我在执行MSSQL查询不同的方式，但第二个查询没有给结果作为第一个。
Query 1:
select dbresultsid, TestCase, BuildID, Analyzed,
Verdict,
(select count(Verdict) from results where BuildID = 'Beta1'
and Verdict = 'PASS') AS PASS,
(select count(Verdict) from results where BuildID = 'Beta1'
and Verdict = 'FAIL') AS FAIL,
(select count(Verdict) from results where BuildID = 'Beta1'
and Verdict = 'INCONC') AS INCONC,
(select count(Verdict) from results where BuildID = 'Beta1'
and Verdict = 'TIMEOUT') AS TIMEOUT
from results
where BuildID = 'Beta1'
group by TestCase,dbresultsid
order by Analyzed
Query 2:
select dbresultsid, TestCase, BuildID, Analyzed,
Verdict,
(case when Verdict='PASS' then count(Verdict) else 0 end) as PASS,
(case when Verdict='FAIL' then count(Verdict) else 0 end) as FAIL,
(case when Verdict='INCONC' then count(Verdict) else 0 end) as INCONC,
(case when Verdict='TIMEOUT' then count(Verdict) else 0 end) as TIMEOUT
from results
where
BuildID = 'Beta1'
group by TestCase,dbresultsid
order by Analyzed
Results :
for Query 1:
if the total number of PASS = 20,
then PASS column will display 20 everywhere.
Results :
for Query 2:
here whereever there is PASS, it displays 1 and the total 20 rows where pass is displayed there is 1,
我想查询2的结果是相同的查询1
任何想法吗？
谢谢，
第一个查询做了选择带有WHERE只返回具有匹配的判决，然后计算这些行的行。 第二查询计算每一次都行。
您可以试试：
select
dbresultsid, TestCase, BuildID, Analyzed, Verdict,
Sum(case when Verdict='PASS' then 1 else 0 end) as PASS,
Sum(case when Verdict='FAIL' then 1 else 0 end) as FAIL,
Sum(case when Verdict='INCONC' then 1 else 0 end) as INCONC,
Sum(case when Verdict='TIMEOUT' then 1 else 0 end) as TIMEOUT
from
results
where
BuildID = 'Beta1'
group by
TestCase, dbresultsid
order by
Analyzed
测试数据：
CREATE TABLE #Test (BuildID Integer, Verdict char(7))
INSERT INTO #TEST (Buildid, Verdict)
VALUES (1, 'PASS')
INSERT INTO #TEST (Buildid, Verdict)
VALUES (1, 'PASS')
INSERT INTO #TEST (BuildID, Verdict)
VALUES (2, 'FAIL')
INSERT INTO #TEST (BuildID, Verdict)
VALUES (3, 'INCONC')
INSERT INTO #TEST (BuildID, Verdict)
VALUES(4, 'TIMEOUT')
查询：
select buildid,
sum(case verdict when 'PASS' then 1 else 0 end) as Pass,
sum(case verdict when 'FAIL' then 1 else 0 end) as Fail,
sum(case verdict when 'INCONC' then 1 else 0 end) as Inconc,
sum(case verdict when 'TIMEOUT' then 1 else 0 end) as TimeOut
FROM #temp
group by buildid
输出：
Item buildid PASS Fail Inconc TimeOut
1 1 2 0 0 0
2 2 0 1 0 0
3 3 0 0 1 0
4 4 0 0 0 1
你为什么不只是使用第一个？在我看来是一个很好的方式来获得，如果参考和GROUP BY键的明确界定你的结果。 我只将改变“其中”中的子选择的关键每次不重复，使得直接引用主表代替。
(select count .. from results where BuildID = r1.BuildID and Verdict ..)
from results r1
首先，因为你在评论提到dbresultsid是一个关键的柱子中，条款包括它的组中无效，你会得到一个输出行于原始表的每一行（即你的where子句相匹配）。
其次，由于在第一次查询的子查询不相关 ，它们的输出不依赖于从外部查询的行。 因此，他们将只执行一次，而同样产生价值将每 outout行中重复。
所以，如果第一次查询的输出真的是你想要的东西（那里是每原“Beta1的”行一个行的结果表，并在输出的每一行都有相同的值，在过去4列），那么你有什么是漂亮接近最好的，你能做到的。 刚取出group by子句 - 你不需要它。
Select
dbresultsid, TestCase, BuildID, Analyzed, Verdict,
z.PASS, z.FAIL, z.INCONC, z.TIMEOUT
From results r Cross Join
(Select
Sum(case when Verdict='PASS' then 1 else 0 end) PASS,
Sum(case when Verdict='FAIL' then 1 else 0 end) FAIL,
Sum(case when Verdict='INCONC' then 1 else 0 end) INCONC,
Sum(case when Verdict='TIMEOUT' then 1 else 0 end) TIMEOUT
From results Where BuildID = 'Beta1') Z
Where BuildID = 'Beta1'
Order By Analyzed
