declare @sql varchar(8000) set @sql='' select @sql=@sql+',sum(case 哈哈 when '+cast(哈哈 as varchar)+' then 1 else 0 end) as [哈'+cast(哈哈 as varchar)+'数量]' from tablename group by 哈哈exec('select 编号'+@sql+' from tablename group by 编号')
select 编号, [哈10数]=count(case when 哈哈=10 then 1 else 0 end), [哈11数]=count(case when 哈哈=11 then 1 else 0 end) from [Table] group by 编号
其实最好说明 哈哈 字段的类型,如果是字符型,改成declare @sql varchar(8000) set @sql='' select @sql=@sql+',sum(case 哈哈 when '''+哈哈+''' then 1 else 0 end) as [哈'+哈哈+'数量]' from tablename group by 哈哈exec('select 编号'+@sql+' from tablename group by 编号')ps:这个问题的回复里可以看到无数个哈 哈哈
----创建测试数据 if object_id('tbTest') is not null drop table tbTest GO create table tbTest(编号 int,哈哈 int) insert tbTest select 1000,10 union all select 1000,11 union all select 1000,11 union all select 1001,10 union all select 1001,11 union all select 1001,10 union all select 1002,10 union all select 1002,11 union all select 1003,10----查询 declare @sql varchar(8000) set @sql = 'select 编号' select @sql = @sql + ',哈' + rtrim(哈哈) + '=sum(case 哈哈 when ' + rtrim(哈哈) + ' then 1 else 0 end)' from tbTest group by 哈哈 order by 哈哈 EXEC(@sql + ' from tbTest group by 编号')----清除测试环境 drop table tbTest /*结果 编号 哈10 哈11 ----------- ----------- ----------- 1000 1 2 1001 2 1 1002 1 1 1003 1 0 */
declare @sql varchar(8000) set @sql='' select @sql=@sql+',sum(case when type=1 and 哈哈='''+哈哈+''' then 1 else 0 end) as [1哈'+哈哈+'数量]' from 表1 group by 哈哈select @sql=@sql+',sum(case when type=2 and 哈哈='''+哈哈+''' then 1 else 0 end) as [2哈'+哈哈+'数量]' from 表2 group by 哈哈exec('select 编号'+@sql+' from (select *,1 as type from 表1 union all select *,2 as type from 表2) as t group by 编号')
--如果哈哈的類型是固定的Select IsNull(A.编号, B.编号) As 编号, IsNull(A.[1哈10数量], 0) As [1哈10数量], IsNull(A.[1哈11数量], 0) As [1哈11数量], IsNull(B.[2哈10数量], 0) As [2哈10数量], IsNull(B.[2哈11数量], 0) As [2哈11数量] From ( Select 编号, SUM(Case 哈哈 When 10 Then 1 Else 0 End) As [1哈10数量], SUM(Case 哈哈 When 11 Then 1 Else 0 End) As [1哈11数量] From 表1 Group By 编号 ) A Full Join ( Select 编号, SUM(Case 哈哈 When 10 Then 1 Else 0 End) As [2哈10数量], SUM(Case 哈哈 When 11 Then 1 Else 0 End) As [2哈11数量] From 表2 Group By 编号 ) B On A.编号 = B.编号
set @sql=''
select @sql=@sql+',sum(case 哈哈 when '+cast(哈哈 as varchar)+' then 1 else 0 end) as [哈'+cast(哈哈 as varchar)+'数量]'
from tablename
group by 哈哈exec('select 编号'+@sql+' from tablename group by 编号')
编号 哈哈
1000 10
1000 11
1000 11
1001 10
1001 11
1001 10
1002 10
1002 11
1003 10如何查出如下数据:
编号 哈10数量 哈11数量
1000 1 2
1001 2 1
1002 1 1
1003 1 0
[哈10数]=count(case when 哈哈=10 then 1 else 0 end),
[哈11数]=count(case when 哈哈=11 then 1 else 0 end)
from [Table]
group by 编号
set @sql=''
select @sql=@sql+',sum(case 哈哈 when '''+哈哈+''' then 1 else 0 end) as [哈'+哈哈+'数量]'
from tablename
group by 哈哈exec('select 编号'+@sql+' from tablename group by 编号')ps:这个问题的回复里可以看到无数个哈
哈哈
if object_id('tbTest') is not null
drop table tbTest
GO
create table tbTest(编号 int,哈哈 int)
insert tbTest
select 1000,10 union all
select 1000,11 union all
select 1000,11 union all
select 1001,10 union all
select 1001,11 union all
select 1001,10 union all
select 1002,10 union all
select 1002,11 union all
select 1003,10----查询
declare @sql varchar(8000)
set @sql = 'select 编号'
select @sql = @sql + ',哈' + rtrim(哈哈) + '=sum(case 哈哈 when ' + rtrim(哈哈) + ' then 1 else 0 end)'
from tbTest group by 哈哈 order by 哈哈
EXEC(@sql + ' from tbTest group by 编号')----清除测试环境
drop table tbTest
/*结果
编号 哈10 哈11
----------- ----------- -----------
1000 1 2
1001 2 1
1002 1 1
1003 1 0
*/
SQL中的数据如下:(表1)
编号 哈哈
1000 10
1000 11
1000 11
1001 10
1001 11
1001 10
1002 10
1002 11
1003 10
(表2):
编号 哈哈
1000 10
1000 11
1001 10
1001 11
1002 10
1002 11
1003 10
如何查出如下数据:
编号 1哈10数量 1哈11数量 1哈10数量 1哈11数量
1000 1 2 1 1
1001 2 1 1 1
1002 1 1 1 1
1003 1 0 1 0
set @sql=''
select @sql=@sql+',sum(case when type=1 and 哈哈='''+哈哈+''' then 1 else 0 end) as [1哈'+哈哈+'数量]'
from 表1
group by 哈哈select @sql=@sql+',sum(case when type=2 and 哈哈='''+哈哈+''' then 1 else 0 end) as [2哈'+哈哈+'数量]'
from 表2
group by 哈哈exec('select 编号'+@sql+' from (select *,1 as type from 表1 union all select *,2 as type from 表2) as t group by 编号')
IsNull(A.编号, B.编号) As 编号,
IsNull(A.[1哈10数量], 0) As [1哈10数量],
IsNull(A.[1哈11数量], 0) As [1哈11数量],
IsNull(B.[2哈10数量], 0) As [2哈10数量],
IsNull(B.[2哈11数量], 0) As [2哈11数量]
From
(
Select
编号,
SUM(Case 哈哈 When 10 Then 1 Else 0 End) As [1哈10数量],
SUM(Case 哈哈 When 11 Then 1 Else 0 End) As [1哈11数量]
From
表1
Group By
编号
) A
Full Join
(
Select
编号,
SUM(Case 哈哈 When 10 Then 1 Else 0 End) As [2哈10数量],
SUM(Case 哈哈 When 11 Then 1 Else 0 End) As [2哈11数量]
From
表2
Group By
编号
) B
On A.编号 = B.编号