select systemid,sum(case when functionid=1 then 1 else 0 end) as Nr of functionid1,sum(case when functionid=2 then 1 else 0 end) as Nr of functionid2 from 表名 不知道你的第三列表有什么用是标识数据的数不?
select systemid,sum(case when functionid=1 then 1 else 0 end) as Nr of functionid1,sum(case when functionid=2 then 1 else 0 end) as Nr of functionid2 from 表名 group by systemid 刚才着急吃饭写错了 不好意思
Nr of functionid1,顯示的列名這裡要處理下,否則有錯誤提示的。Select systemid, SUM(Case functionid When 1 Then 1 Else 0 End) As [Nr of functionid1], SUM(Case functionid When 2 Then 1 Else 0 End) As [Nr of functionid2] From TableName Group By systemid Order By systemid
对 呵呵 不过你用order by 没有什么用吧
Create Table TEST (SystemID Int, functionid Int, id Int) Insert TEST Select 1, 1, 1 Union All Select 2, 1, 2 Union All Select 3, 1, 3 Union All Select 2, 2, 4 Union All Select 3, 2, 5 Union All Select 3, 2, 6 Union All Select 1, 1, 7 GO Select SystemID, SUM(Case functionid When 1 Then 1 Else 0 End) As [Nr of functionid1], SUM(Case functionid When 2 Then 1 Else 0 End) As [Nr of functionid2] From TEST Group By SystemID Order By SystemID GO Drop Table TEST --Result /* SystemID Nr of functionid1 Nr of functionid2 1 2 0 2 1 1 3 1 2 */
group by systemid
刚才着急吃饭写错了 不好意思
systemid,
SUM(Case functionid When 1 Then 1 Else 0 End) As [Nr of functionid1],
SUM(Case functionid When 2 Then 1 Else 0 End) As [Nr of functionid2]
From TableName
Group By systemid
Order By systemid
(SystemID Int,
functionid Int,
id Int)
Insert TEST Select 1, 1, 1
Union All Select 2, 1, 2
Union All Select 3, 1, 3
Union All Select 2, 2, 4
Union All Select 3, 2, 5
Union All Select 3, 2, 6
Union All Select 1, 1, 7
GO
Select
SystemID,
SUM(Case functionid When 1 Then 1 Else 0 End) As [Nr of functionid1],
SUM(Case functionid When 2 Then 1 Else 0 End) As [Nr of functionid2]
From TEST
Group By SystemID
Order By SystemID
GO
Drop Table TEST
--Result
/*
SystemID Nr of functionid1 Nr of functionid2
1 2 0
2 1 1
3 1 2
*/
对 呵呵 不过你用order by 没有什么用吧
------------------------
:)