SQL查询:选择第1列、行数(criteria1)和行数(criteria2)
本文关键字:criteria1 criteria2 行数 查询 选择 1列 SQL | 更新日期: 2023-09-27 18:30:04
我需要一个小sql查询的帮助。考虑下表:
TicketNo | Rules | Audit Result
---------------------------------
PR123 | some random strings | Audited
PR123 | some random strings | Not Audited
PR123 | some random strings | Audited
PR123 | some random strings | Audited
PR123 | some random strings | Not Audited
PR123 | some random strings | Not Audited
PR123 | some random strings | Audited
PR999 | some random strings | Audited
PR999 | some random strings | Audited
PR999 | some random strings | Audited
PR999 | some random strings | Audited
PR999 | some random strings | Audited
PR111 | some random strings | Not Audited
PR111 | some random strings | Not Audited
PR111 | some random strings | Not Audited
PR111 | some random strings | Not Audited
我需要以下结果:
Ticket | Count of Audited Rules | Count of NOT-Audited Rules
------------------------------------------------------------
PR123 | 4 | 3
PR999 | 5 | 0
PR111 | 0 | 4
我试过了:
Select T1.TicketNo,T1.Audited_Count,T2.NonAudited_Count
FROM
(
Select [TicketNo], COUNT([TicketNo]) AS Audited_Count
from [Resolved Results$] where [Audit Result] = 'Audited'
group by TicketNo
) T1
inner JOIN
(
Select [TicketNo], COUNT([TicketNo]) AS NonAudited_Count
from [Resolved Results$] where [Audit Result] <> 'Audited'
group by TicketNo
) T2
on T1.[TicketNo] = T2.[TicketNo]
但以上命令不适用于那些具有oNLY已审核规则或oNLY未审核规则的票证,而仅适用于同时具有这两种规则的票证。
有人能帮我解决这个问题吗?
请注意:只有内部联接才能使用OleDB,因此请避免使用内部联接以外的任何联接。非常感谢
编辑:Gordon的第一个答案应该很理想,但不幸的是,Excel的OLEDB驱动程序不支持它。然而,Gordon使用iff(..)的建议奏效了。
我使用的最后一个查询是:
select [TicketNo],
sum(iif([Audit Result] = "AUDITED", 1, 0)) as Audited,
sum(iif([Audit Result] = "NOT AUDITED", 1, 0)) as NotAudited
from [Resolved Results$]
group by [TicketNo];
谢谢你,戈登!
您想要条件求和,可以通过在sum()
:中放入case
语句来实现
select ticket,
sum(case when AuditResult = 'Audited' then 1 else 0 end) as Audited,
sum(case when AuditResult = 'Not Audited' then 1 else 0 end) as NotAudited
from t
group by ticket;
这是假设AuditResult是一个单独的列。如果它是SomeStrings
上的最后一条注释,那么查询看起来像:
select ticket,
sum(case when SomeStrings like '%Audited' and SomeStrings not like '%Not Audited' then 1
else 0
end) as Audited,
sum(case when SomeStrings like '%Not Audited' then 1
else 0
end) as NotAudited
from t
group by ticket;