的SQL Server交叉联接查询
问题描述:
我的下一个SQL Server表:的SQL Server交叉联接查询
Machine | Result
-----------------
M2 | 0
M1 | 1
M3 | 0
M1 | 1
M2 | 1
M1 | 0
M2 | 0
M1 | 1
M3 | 0
M1 | 1
M3 | 0
我需要得到像这样的报告:
Machine | Count 0's | Count 1's
--------------------------------
M1 | 1 | 4
M2 | 2 | 1
M3 | 3 | 0
我都试过,没有sucess下一个查询:
SELECT A, B
FROM
(SELECT COUNT(*) as A
FROM MY_TABLE
WHERE Result = 0)
GROUP BY Machine) a
CROSS JOIN
(SELECT COUNT(*) as B
FROM MY_TABLE
WHERE Result = 1)
GROUP BY Machine) b
你能帮助我吗?提前致谢!
答
您可以有条件聚集做到这一点:
select machine,
sum(case when result = 0 then 1 else 0 end) as num_0,
sum(case when result = 1 then 1 else 0 end) as num_1
from my_table
group by machine;
谢谢,完美的作品! – user3658439 2014-11-22 23:16:36