新手求教！一提关于sql的题目

[asd61789]

分组之后怎么查询不同的数量呢

[asd61789]

求各位大神解答！

[二月十六]

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([id] int,[address] nvarchar(22),[level] nvarchar(23))
Insert #A
select 2,N'北京',N'I' union all
select 3,N'上海',N'II' union all
select 5,N'广东',N'III' union all
select 6,N'北京',N'I' union all
select 7,N'上海',N'III'
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select [address] '
select @sql=@sql+' , sum(case [level] when '''+ [level] +''' then 1 else 0 end) [水质'+  [level]  +'总数]'
from #A
set @sql=@sql+' from #A group by [address]'
EXEC(@sql) 

[二月十六]

2#有点问题，用这个

--测试数据
if not object_id(N'Tempdb..#A') is null
    drop table #A
Go
Create table #A([id] int,[address] nvarchar(22),[level] nvarchar(23))
Insert #A
select 2,N'北京',N'I' union all
select 3,N'上海',N'II' union all
select 5,N'广东',N'III' union all
select 6,N'北京',N'I' union all
select 7,N'上海',N'III'
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select [address] '
select @sql=@sql+' , sum(case [level] when '''+ [level] +''' then 1 else 0 end) [水质'+  [level]  +'总数]'
from (SELECT DISTINCT [level] FROM #A)a
set @sql=@sql+' from #A group by [address]'
EXEC(@sql) 

[zhouyuehai1978]

--测试数据
if not object_id(N'Tempdb..#A') is null
    drop table #A
Go
Create table #A([id] int,[address] nvarchar(22),[level] nvarchar(23))
Insert #A
select 2,N'北京',N'I' union all
select 3,N'上海',N'II' union all
select 5,N'广东',N'III' union all
select 6,N'北京',N'I' union all
select 7,N'上海',N'III'
Go
--测试数据结束
SELECT ADDRESS,
       ISNULL(I, 0)    AS I,
       ISNULL(II, 0)   AS II,
       ISNULL(III, 0)  AS III
FROM   (
           SELECT ADDRESS,
                  LEVEL,
                  1   AS c
           FROM   #A  AS a
       )               AS a1 PIVOT(SUM(c) FOR LEVEL IN (I, II, III)) AS p

[asd61789]

引用 3楼二月十六 的回复:

2#有点问题，用这个

--测试数据
if not object_id(N'Tempdb..#A') is null
    drop table #A
Go
Create table #A([id] int,[address] nvarchar(22),[level] nvarchar(23))
Insert #A
select 2,N'北京',N'I' union all
select 3,N'上海',N'II' union all
select 5,N'广东',N'III' union all
select 6,N'北京',N'I' union all
select 7,N'上海',N'III'
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='select [address] '
select @sql=@sql+' , sum(case [level] when '''+ [level] +''' then 1 else 0 end) [水质'+  [level]  +'总数]'
from (SELECT DISTINCT [level] FROM #A)a
set @sql=@sql+' from #A group by [address]'
EXEC(@sql) 

谢谢大佬

[asd61789]

引用 4楼zhouyuehai1978 的回复:

--测试数据
if not object_id(N'Tempdb..#A') is null
    drop table #A
Go
Create table #A([id] int,[address] nvarchar(22),[level] nvarchar(23))
Insert #A
select 2,N'北京',N'I' union all
select 3,N'上海',N'II' union all
select 5,N'广东',N'III' union all
select 6,N'北京',N'I' union all
select 7,N'上海',N'III'
Go
--测试数据结束
SELECT ADDRESS,
       ISNULL(I, 0)    AS I,
       ISNULL(II, 0)   AS II,
       ISNULL(III, 0)  AS III
FROM   (
           SELECT ADDRESS,
                  LEVEL,
                  1   AS c
           FROM   #A  AS a
       )               AS a1 PIVOT(SUM(c) FOR LEVEL IN (I, II, III)) AS p

可以了，谢谢^ ^