SQL查询增强 [英] SQL query enhancement

问题描述

我编写了一个查询，该查询的计数具有不同的状态.
查询就像

I wrote a query that gets the counts of which has different statuses.
The query goes like

SELECT COUNT(rec.rmaNum) as Created
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated
WHERE rec.rmaStatus = 0 
UNION 
SELECT COUNT(rec.rmaNum) as Received
FROM RMARecords rec
LEFT JOIN RMAUsers ure on ure.id = rec.userCreated
WHERE (rec.rmaStatus = 1 OR rec.rmaStatus = 2)
UNION
SELECT COUNT(rec.rmaNum) as Closed
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 3
UNION
SELECT COUNT(rec.rmaNum) as Voided
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 4

该查询的输出是;

已创建
-------
0
1
3
6

我认为由于联合，结果集就这样了.我想要的输出是不同的

已创建已收到的封闭空洞
--------/----------/-------/--------
--- 0 ---/---- 1 -----/-3 ---/--- 6 ----

如何获得像上层结果一样的结果集?在此先感谢您.

The output of this query is;

Created
-------
0
1
3
6

I think because of the union the result set comes like this. What I want as output is different

Created Received Closed Voided
--------/----------/-------/--------
---0---/----1-----/--3---/---6----

How can I get as a result set like the upper result? Thanks in advance.

推荐答案

选中此项，

http://weblogs.asp.net/salimfayad/archive/2008/01/30/rows-to-columns.aspx [ ^ ]

它应该有帮助



*如果解决了，请标记为答案

Check this,

http://weblogs.asp.net/salimfayad/archive/2008/01/30/rows-to-columns.aspx[^]

It should help



*Mark as answer if this solves

尝试这一奇迹-FOOL.

try this one wonder-FOOL.

SELECT (SUM(CASE WHEN rec.rmaNum = 0 THEN 1 ELSE 0 END)) as 'Created',
(SUM(CASE WHEN rec.rmaNum IN ('1','2') THEN 1 ELSE 0 END)) as 'Received',
(SUM(CASE WHEN rec.rmaNum = 3 THEN 1 ELSE 0 END)) as 'Closed',
(SUM(CASE WHEN rec.rmaNum = 4 THEN 1 ELSE 0 END)) as 'Voided',
FROM RMARecords AS rec
LEFT OUTER JOIN RMAUsers AS usr ON rec.userCreated = usr.id
GROUP BY rec.rmaNum

如果您有问题，请发表评论，如果对您有帮助，请标记为答案.

最好的问候，
@iamsupergrasya

If you have questions just comment, and if this is helpful mark as answer.

Best Regards,
@iamsupergrasya

这篇关于SQL查询增强的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！