一条查询语句查多个count应该怎么查

[墨染眸]

ID STATE
2 1
2 2
2 1

3 2
3 1
3 1
我想查出来这种效果
ID STATE=1 STATE=2
2 2 1
3 2 1


请问这样的HQL或者SQL语句应该怎么写，

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[STATE] int)
Insert #T
select 2,1 union all
select 2,2 union all
select 2,1 union all
select 3,2 union all
select 3,1 union all
select 3,1
Go
--测试数据结束
;WITH cte AS (
Select ID,STATE,COUNT(STATE) AS num from #T GROUP BY ID,STATE
)
SELECT  a.ID ,
        a.num AS [STATE=1] ,
        b.num AS [STATE=2]
FROM    cte a
        JOIN cte b ON b.ID = a.ID
                      AND a.STATE = 1
                      AND b.STATE = 2

[墨染眸]

引用 1 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[STATE] int)
Insert #T
select 2,1 union all
select 2,2 union all
select 2,1 union all
select 3,2 union all
select 3,1 union all
select 3,1
Go
--测试数据结束
;WITH cte AS (
Select ID,STATE,COUNT(STATE) AS num from #T GROUP BY ID,STATE
)
SELECT  a.ID ,
        a.num AS [STATE=1] ,
        b.num AS [STATE=2]
FROM    cte a
        JOIN cte b ON b.ID = a.ID
                      AND a.STATE = 1
                      AND b.STATE = 2

小白表示有点看不懂

[二月十六]

或者这样也行

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[STATE] int)
Insert #T
select 2,1 union all
select 2,2 union all
select 2,1 union all
select 3,2 union all
select 3,1 union all
select 3,1
Go
--测试数据结束
;WITH cte AS (
Select ID,STATE,COUNT(STATE) AS num from #T GROUP BY ID,STATE
)
SELECT  ID ,
        MAX(CASE WHEN STATE = 1 THEN num
                 ELSE 0
            END) AS [STATE=1] ,
        MAX(CASE WHEN STATE = 2 THEN num
                 ELSE 0
            END) AS [STATE=1]
FROM    cte
GROUP BY ID

[二月十六]

引用 2 楼 u013243610 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[STATE] int)
Insert #T
select 2,1 union all
select 2,2 union all
select 2,1 union all
select 3,2 union all
select 3,1 union all
select 3,1
Go
--测试数据结束
;WITH cte AS (
Select ID,STATE,COUNT(STATE) AS num from #T GROUP BY ID,STATE
)
SELECT  a.ID ,
        a.num AS [STATE=1] ,
        b.num AS [STATE=2]
FROM    cte a
        JOIN cte b ON b.ID = a.ID
                      AND a.STATE = 1
                      AND b.STATE = 2

小白表示有点看不懂[/quote] 上边那个with as 是先统计出来每个id每个state有多少个。然后下边是按照规则显示出来

[墨染眸]

引用 4 楼 sinat_28984567 的回复:

[quote=引用 2 楼 u013243610 的回复:] [quote=引用 1 楼 sinat_28984567 的回复:]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[STATE] int)
Insert #T
select 2,1 union all
select 2,2 union all
select 2,1 union all
select 3,2 union all
select 3,1 union all
select 3,1
Go
--测试数据结束
;WITH cte AS (
Select ID,STATE,COUNT(STATE) AS num from #T GROUP BY ID,STATE
)
SELECT  a.ID ,
        a.num AS [STATE=1] ,
        b.num AS [STATE=2]
FROM    cte a
        JOIN cte b ON b.ID = a.ID
                      AND a.STATE = 1
                      AND b.STATE = 2

小白表示有点看不懂[/quote] 上边那个with as 是先统计出来每个id每个state有多少个。然后下边是按照规则显示出来[/quote] 换成一句普通的SQL语句应该怎么写，假设已经有了测试数据，怎么用一句sql查出来

[二月十六]

有测试数据直接用这句，把#T换成对应的表名

;WITH cte AS (
Select ID,STATE,COUNT(STATE) AS num from #T GROUP BY ID,STATE
)
SELECT  a.ID ,
        a.num AS [STATE=1] ,
        b.num AS [STATE=2]
FROM    cte a
        JOIN cte b ON b.ID = a.ID
                      AND a.STATE = 1
                      AND b.STATE = 2