SQL QUERY使用Group by .. [英] SQL QUERY Using Group by..

问题描述

1）我的表：



查询：

1) My Table:

Query:

select mcdesp,mcopsts from machine

表：

Table:

mcdesp	mcopsts
A	GOOD
A	GOOD
B	GOOD
C	URWP
C	GOOD
A	URWT
A	URWT

2）我的查询及其输出：

2) My Queries and their outputs:

select mcdesp,count(mcopsts)as TotalMachine from machine group by mcdesp

mcdesp	TotalMahine
A	4
B	1
C	2

select mcdesp, count(mcopsts) as GOOD from machine where mcopsts='GOOD' group by mcdesp

mcdesp	GOOD
A	2
B	1
C	1

select mcdesp,count(mcopsts) as URWT from machine where mcopsts='URWT' group by mcdesp

mcdesp	URWT
A	2
B	
C	

select mcdesp,count(mcopsts) as URWP from machine where mcopsts='URWP' group by mcdesp

mcdesp	URWP
A	
B	
C	1

但我需要一个结果

BUT I NEED A RESULT LIKE

mcdesp	TotalMachine	GOOD	URWP	URWT
A	4	2	0	2
B	1	1	0	0
C	2	1	1	0

请帮助我..

Please some one help me..

推荐答案

另一个解决方案是使用 Pivot [ ^ ]：

Another solution is to use Pivot[^]:

SELECT mcdesp, [GOOD], [URWP], [URWT]
FROM (
    SELECT mcdesp,mcopsts
    FROM machine) AS DT
PIVOT (COUNT(mcopsts) FOR mcdesp IN([GOOD], [URWP], [URWT])) AS PT

如果您想使用动态支点，请参阅以下内容：

http://social.msdn.microsoft.com/Forums/en-US/transactsql / thread / 87582c2b-2f4a-4be3-a1a7-3bc8235d4c7f / [ ^ ]

http://sqlwithmanoj.wordpress.com/2011/01/25/dynamic-pivot/ [ ^ ]



总得：

If you would like to use dynamic pivots, see these:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/87582c2b-2f4a-4be3-a1a7-3bc8235d4c7f/[^]
http://sqlwithmanoj.wordpress.com/2011/01/25/dynamic-pivot/[^]

To get total:

SELECT mcdesp, [GOOD], [URWP], [URWT], [GOOD] + [URWP] + [URWT] AS Total
FROM(
    SELECT mcdesp, [GOOD], [URWP], [URWT]
    FROM (
        SELECT mcdesp,mcopsts
        FROM machine) AS DT
    PIVOT (COUNT(mcopsts) FOR mcdesp IN([GOOD], [URWP], [URWT])) AS PT) AS T

尝试：

Try:

SELECT mcdesp,
   COUNT(mcdesp) AS TotalMachine, 
   SUM(CASE WHEN mcopsts='GOOD' THEN 1 ELSE 0 END) AS GOOD,
   SUM(CASE WHEN mcopsts='URWP' THEN 1 ELSE 0 END) AS URWP,
   SUM(CASE WHEN mcopsts='URWT' THEN 1 ELSE 0 END) AS URWT
FROM myTable GROUP BY mcdesp

这篇关于SQL QUERY使用Group by ..的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！