以下查询从表new_name返回所有names的结果集
SELECT
CASE
WHEN nickname = '' THEN fullname ELSE nickname END AS new_name,
FROM names
我试图通过new_name使用以下查询进行搜索
SELECT
CASE
WHEN nickname = '' THEN fullname ELSE nickname END AS new_name,
FROM names
WHERE ne
当我检查用户名或电子邮件是否已经被占用时,我正在尝试优化这个查询。
更优化是因为我相信可以创建一个查询来实现这一点?
select
(select count(Username) from User where Username = @Username) > 0 as "UsernameExist"
(select count(Email) from User where Email = @Email) > 0 as "EmailExist"
下面的查询将不起作用,因为如果用户名和电子邮件是针对不同用户的,我仍将得到1的计数。
select coun
MySQL/MSSQL有一个整洁的小内联if函数,您可以在查询中使用它来检测空值,如下所示。
SELECT
...
foo.a_field AS "a_field",
SELECT if(foo.bar is null, 0, foo.bar) AS "bar",
foo.a_field AS "a_field",
...
我现在遇到的问题是,这段代码在Oracle数据库上运行是不安全的,因为它似乎不支持这种内联if语法。
在Oracle中有没有类似的东西?
我正在运行一个非常简单的查询,但是对于某些结果,一个字段中的值为null。如果该值为null,如何将该值设置为“字符串”?
有点像
SELECT RegName,
RegEmail,
RegPhone,
RegOrg,
RegCountry,
DateReg,
(Website IS NULL? 'no website' : Website) AS WebSite
FROM RegTakePart
WHERE Reject IS NULL
它将在sql server 2005上运行。
谢谢
我有两个具有不同结果集的查询,但其中一个查询中只有一个条件是附加的。这两个查询都是从同一个表中提取的。第一个查询:
SELECT cm.ctm, count_big(*) AS TOTAL
FROM dbo_cm.cm
WHERE
cm.a= 'abc' AND
cm.b= 1 AND
cm.ps IS NOT NULL AND
我已经分别编写了下面的查询,但是我想在一个查询中,如何做到这一点,有人能帮忙吗..?
SELECT IFNULL(SUM(net_amount),0) as current_date FROM orders WHERE is_cancelled = false and business_date = CURRENT_DATE();
SELECT IFNULL(SUM(net_amount),0) as current_week FROM orders WHERE is_cancelled = false and YEARWEEK(business_date) = YEARWEEK(CURREN
我想使用SQL select语句,它的条件是‘其中A列为空,将B列值更改为等于C列值’。如何将此逻辑合并到SELECT语句(不是UPDATE语句,因为我不能更改服务器上的表,但希望从服务器查询它们)。
SELECT final.*
FROM final
实际的表在下面的图像中,这里,如果更改列为null,我希望将列Old更改为匹配列DirectUse。
我有两个MYSQL查询,我试图合并成一个,这是第一个-
SELECT
`Your_Name` as agentname,
sum(`Sale_Amount`) as todaysales,
COUNT(*) as NoSales
FROM mp_wp_sales_tracking
WHERE `created` BETWEEN CURDATE() AND NOW()
GROUP BY agentname
第二个查询与前一天的查询相同。
SELECT
`Your_Name` as agentname,
sum(`Sale_Amount`) as yesterday
我有一些类似的疑问:
select sysdate from dual;
select id, count(*) as A from TABLE1 group by id;
select id, count(*) as B from TABLE2 group by id;
select id, count(*) as C from TABLE3 group by id;
select id, count(*) as D from TABLE4 group by id;
我如何将这5个查询合并如下:
谢谢。
我希望根据以下规则合并PostgresQL查询中的两列:
select (column_1 or column_2) as column_3 from my_table
是否有办法实现这一目标?虽然很清楚,我希望column_1值作为column_3,但是如果它是null,我希望column_2值作为column_3。
对不起,如果这听起来太天真了,谢谢!
我正在使用5.7.20-enterprise-commercial,并有一个如下所示的MySql查询: SELECT name FROM jobTable where jobId IN (SELECT dataId from logTable
WHERE eValue ='1098hbfce'); 如果上面的查询返回空值,那么我想运行下面的查询: SELECT name FROM jobTable where procId IN (
SELECT dataId from logTable WHERE eValue ='1098hbfce'); 第一个查询和第二
是否存在根据其他列值更改条件的SQL查询?
我想要执行一个执行以下操作的查询
select colA,colB, colC
from TABLED
where colA > 10
and (if colC == A then colB = 'C' else colB = 'B')
如果这是有意义的,如果colC == A,我们会有
select colA, colB, colC
from TABLED
where colA > 10 and colB = 'C'
否则我们就会
select colA,colB,colC
from
我正在尝试执行多个查询。但它只知道我在游标中执行了buffered=True,就执行最后一个命令。我需要得到这4个查询的结果。
updated_query = cursor.execute(
'select count(*) from event where update_count > 0 and date > now() - interval 24 hour')
deleted_query = cursor.execute(
'select count(*) from event where deleted > 0 and date
需要对数据进行多次聚合(太多次),如何在不为每个聚合插入内部select语句的情况下实现这一点。不想做一个联合,因为查询会变得太大,不如按照下面所示的方式做,但不知道在from子句中插入什么,我不想在其中插入内部select子句,如下所示的只是一个示例,我正在使用的真正的内部select语句要大10倍。例如
select (
select count(id)
from ?
where date_1 = 2022
and date_2 = 2021
)
(
select sum(sales)
from ?
where date_1 = 2020
and date_
我在以下查询中获得了过去3、6和10个不同months.From的平均值
select SUM(GrossAmount)/10 as Averageten
FROM Table
WHERE CreatedDate >= DATEADD(MONTH, -10, GETDATE())
select SUM(GrossAmount)/6 as Averagesix
FROM Table
WHERE CreatedDate >= DATEADD(MONTH, -6, GETDATE())
select SUM(GrossAmount)/3 as Averagethree
我有一个数据,它的列A包含以下数据
Column A
--------
1
2
?
2
我使用了这个查询:
proc sql;
select
if A= '?' then A=., count(*) as N_obs
from freq_sex_Partner
group by Number_of_sexual_partners;
quit;
这不管用。请建议我如何更换?任何标准值?
我有一个SQL查询,它返回两列- "Title“和"Count”。当"Title“为NULL或空('')时,我希望将结果合并到一行中。我该怎么做呢?
这就是我到目前为止所知道的:
SELECT [Title] WHEN '' THEN 'blank' ELSE ISNULL([Title],'blank') AS [Title],
COUNT([value]) AS [Count]
FROM ....
WHERE ....
GROUP BY [Title],[Count]
但由于Group
我有张桌子:
NAME MONEY
Jane 100
Chris -100
Jane 50
Ann -10
Jane -25
Ann 17
而我想写一个求和数据的查询,在一列中只应该是正数,而在另一栏中只有负数。输出应该如下所示:
NAME SUM_POSITIVE SUM_NEGATIVE
Jane 150 -25
Chris 0 -100
Ann 17 -10
查询:
select name, sum(money) f
我编写了用于获取数据的独立查询。现在,我想在一个查询中连接所有查询。怎么加入?
1.
select Name, COUNT(name) as Total_Complaint,
count(Solved) as SC, COUNT(Pending)as PC
from helpdesk
WHERE Solved="yes" OR Pending="yes"
group by name;
2.
SELECT name,count(name)as Total_Compalint,count(feedback),
count(feedback)/c
我有以下查询:
select
max(case when measure_name = 'consumption' then measure_value::double end)
consumption,
max(case when measure_name = 'generation' then measure_value::double end)
generation,
max(case when measure_name = 'self_coverage' then measure_value::double end
我有两个这样的疑问:
SELECT c.id FROM component c, base_vee bv
WHERE
c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199)
AND
c.id = bv.component_id
AND
(bv.affidavit IS NULL OR bv.affidavit = '')
SELECT c.id FROM component c, base_vee
如何处理MySQL load outfile函数中带有空格的空值?
以下是查询
SELECT * FROM employees WHERE `date` BETWEEN '2016-12-15' AND '2017-01-04' INTO OUTFILE '/tmp/test.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY "" LINES TERMINATED BY ' ';
列中的空值在csv中显示为Nul
我需要在Server中使用此公式计算Quantity列的值,其中Name = A:
A(Quantity) = (B*B1 + C*C1 + D*D1) / SUM(B1 + C1 + D1) + E
样本数据:
Static Static Dynamic
Name ID Quantity -- Here are more columns, but doesn't matter
A 1 ? -- need to calculate this value
B 2 4
C 3
我需要对MYSQL数据库中的一个表进行查询。在这个查询中,我需要使用不同的where进行多个选择。
我当前的查询是:
$result = mysql_query("
SELECT
last_name, first_name, user_name,
SUM(rthours) as rthours, SUM(othours) as others,
SUM(trthours) as trthours, SUM(tothours) as tot hours
FROM data
WHERE labor_date BETWEE
下面是我表中的表列:
EmpNo = Employee number of the creator of the row
DateReceived = the datetime when the ticket is received by employee
DateProcessed = the date the row is inserted
Workcode = ticket name
TimeStart = the datetime the ticket started
TimeEnd = the datetime the ticket ended
我需要得到每个事务的Elap
我需要一个SQL查询,该查询模拟表单的if--然后- the语句:
if (query1 != null)
return query1
else
return query2
由于合并不能处理结果集,所以我创建了一个执行以下任务的联合查询:
SELECT * FROM obs WHERE cond1 --query1
UNION
SELECT * FROM obs WHERE (NOT EXISTS(query1)) AND cond2
在SQL中:
( SELECT * FROM obs WHERE src = @id AND tstart <= @instant AND
因为我们有两个选项来拦截来自数据库的空值.
ISNull
聚结
下面是为上述两个函数编写查询的方法.
Select IsNull(Columnname, '') As validColumnValue From TableName
Select Coleasce(Columnname, '') As validColumnValue From TableName
查询-在哪种情况下应该优先考虑哪种情况,以及为什么?
我从一张桌子上挑选记录作为-
查询1:
Declare @tId varchar(50);
Select top(1) @tId = TransId
From Table1
Where FName = 'Vincent' And LName = 'Hall'
And CustId = 1
Order By TimeStamp Desc
查询2:
If @tId IS NULL
Select top(1) @tId = TransId
From Table1
Where FName = 'Vincent' And LName = '
如果我有这样的查询
SELECT date_trunc('day', assigndate)e,
count(CASE WHEN a.assigneeid = 65548
AND a.assigneeid IN
(SELECT userid
FROM groupmembers
WHERE groupid = 65553) THEN 1 ELSE NULL END) assigned,
count(CASE WHEN a.assign
我遇到了一个SQL查询问题。我有一张10块地的桌子。我需要创建一个查询,它通过字段ProductionYear(int)在两个变量@startDate(int)和@endDate(int)之间获取日期。这两个变量都是不必要的。我需要构建具有以下条件的SQL查询:
If(@endDate = 0)
Select Id from MyTable where ProductionYear > @startDate
else
Select Id from MyTable where ProductionYear BETWEEN @startDate and @endDate.
如何使用这些条件构
我正在学习MERGE或UPSERT (UPDATE + INSERT),根据wikipedawikipeda的说法,它有以下形式:
MERGE INTO tablename USING table_reference ON (condition)
WHEN MATCHED THEN
UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN
INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]);
,以及:
我已经广泛地搜索了这个网站,但找不到解决方案。
下面是我的查询示例:
SELECT
ActivityID,
Hours = (CASE
WHEN ActivityTypeID <> 2 THEN
FieldName = (Some Aggregate Sub Query),
FieldName2 = (Some other aggregate sub query)
WHEN ActivityTypeID = 2 THE