SQL数量统计问题,请教

bigmingming 2011-12-13 10:16:50
TYPE 状态
40A 好
40B 坏
40B 好
40C 好
40A 好



现在要显示成
40A 40B 40C . 。。。。。。
好 2 1 1
坏 0 1 0
ToTal 2 2 1



注意:TYPE中类型很多,只列了3种,如何横向列出type
...全文
197 点赞 收藏 15
写回复
15 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
-晴天 2011-12-13
[Quote=引用 12 楼 bigmingming 的回复:]

谢谢大家,再问一下,如果坏的个数都是0,能否加一行 坏 个数0行的
[/Quote]

上面各位写的行转列语句,如果坏的个数是0,它也会出来的,应该用不着另外加一行.
回复
bigmingming 2011-12-13
谢谢大家,再问一下,如果坏的个数都是0,能否加一行 坏 个数0行的
回复
大家都看不到吗?
回复
[Quote=引用 2 楼 beirut 的回复:]

SQL code
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([TYPE] varchar(10),[状态] varchar(10))
insert [tb]
select '40A','好' union all
select '40B','坏' union all
se……
[/Quote]
不是在吗?
在怎么回事啊?
回复
dawugui 2011-12-13
create table [tb]([TYPE] varchar(10),[状态] varchar(10))
insert [tb]
select '40A','好' union all
select '40B','坏' union all
select '40B','好' union all
select '40C','好' union all
select '40A','好'


declare @sql varchar(8000)
set @sql = 'select isnull(状态,''total'') 状态'
select @sql = @sql + ' , sum(case TYPE when ''' + TYPE + ''' then 1 else 0 end) [' + TYPE + ']'
from (select distinct TYPE from tb) as a
set @sql = @sql + ' from tb group by 状态 with rollup'
exec(@sql)

drop table tb

/*
状态 40A 40B 40C
---------- ----------- ----------- -----------
好 2 1 1
坏 0 1 0
total 2 2 1
*/
回复
--小F-- 2011-12-13
select
isnull(type,'total')
sum(case type when '40A' then 1 else 0 end) as '40A',
sum(case type when '40B' then 1 else 0 end) as '40B',
sum(case type when '40C' then 1 else 0 end) as '40C'
from
tb
group by
type
with rollup
回复
小_爱 2011-12-13
可恶啊啊啊啊啊啊啊啊

我的2楼不见了




--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([TYPE] varchar(10),[状态] varchar(10))
insert [tb]
select '40A','好' union all
select '40B','坏' union all
select '40B','好' union all
select '40C','好' union all
select '40A','好'


declare @s varchar(max)
set @s=''
select @s=@s+','+quotename([TYPE])+'=sum(case when [TYPE]='+quotename([TYPE],'''')+' then 1 else 0 end)'
from [tb] group by[TYPE]
set @s = 'select isnull([状态],''ToTal'')'+@s+' from [tb] group by [状态] with ROLLUP'
exec(@s)
/*
40A 40B 40C
---------- ----------- ----------- -----------
好 2 1 1
坏 0 1 0
ToTal 2 2 1

*/
回复
小_爱 2011-12-13
我的回复怎么不见了
回复
小_爱 2011-12-13
[Quote=引用 1 楼 roy_88 的回复:]

SQL code
use Tempdb
go
--> -->

if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([TYPE] nvarchar(3),[状态] nvarchar(10))
Insert #T
select N'40A',N'好' union all
select N'4……
[/Quote]
大版早。。。。。。。。。。。。。。。。。。。。。。。。。。。。
回复
中国风 2011-12-13
生成的語句格式如下:
select 
[状态]=isnull([状态],'ToTal'),
[40A]=sum(case when [TYPE]='40A' then 1 else 0 end),
[40B]=sum(case when [TYPE]='40B' then 1 else 0 end),
[40C]=sum(case when [TYPE]='40C' then 1 else 0 end)
from #T group by [状态] with rollup;
回复
小_爱 2011-12-13
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([TYPE] varchar(10),[状态] varchar(10))
insert [tb]
select '40A','好' union all
select '40B','坏' union all
select '40B','好' union all
select '40C','好' union all
select '40A','好'


declare @s varchar(max)
set @s=''
select @s=@s+','+quotename([TYPE])+'=sum(case when [TYPE]='+quotename([TYPE],'''')+' then 1 else 0 end)'
from [tb] group by[TYPE]
set @s = 'select isnull([状态],''ToTal'')'+@s+' from [tb] group by [状态] with ROLLUP'
exec(@s)
/*
40A 40B 40C
---------- ----------- ----------- -----------
好 2 1 1
坏 0 1 0
ToTal 2 2 1

*/
回复
中国风 2011-12-13
use Tempdb
go
--> -->

if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([TYPE] nvarchar(3),[状态] nvarchar(10))
Insert #T
select N'40A',N'好' union all
select N'40B',N'坏' union all
select N'40B',N'好' union all
select N'40C',N'好' union all
select N'40A',N'好'
Go
DECLARE @s NVARCHAR(4000)
SET @s=N'select [状态]=isnull([状态],''ToTal'')'
Select @s=@s+','+QUOTENAME([TYPE])+'=sum(case when [TYPE]='+QUOTENAME([TYPE],'''')+' then 1 else 0 end)' from #T GROUP BY [TYPE]

EXEC(@s+N' from #T group by [状态] with rollup;')

/*
状态 40A 40B 40C
好 2 1 1
坏 0 1 0
ToTal 2 2 1
*/
回复
唐诗三百首 2011-12-13

create table [tb]([TYPE] varchar(10),[状态] varchar(10))

insert [tb]
select '40A','好' union all
select '40B','好' union all
select '40B','好' union all
select '40C','好' union all
select '40A','好'


declare @s varchar(max)=''

select @s=@s+','+quotename([TYPE])+'=sum(case when [TYPE]='+quotename([TYPE],'''')+' then 1 else 0 end)'
from [tb] group by[TYPE]

set @s = 'select isnull([状态],''ToTal'')'+@s+' from (select * from [tb] union all select ''x'',''坏'' union all select ''x'',''好'') t group by [状态] with ROLLUP'

exec(@s)

40A 40B 40C
---------- ----------- ----------- -----------
好 2 2 1
坏 0 0 0
ToTal 2 2 1

(3 row(s) affected)
回复
中国风 2011-12-13
use Tempdb
go
--> -->

if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([TYPE] nvarchar(3),[状态] nvarchar(10))
Insert #T
select N'40A',N'好' union all
select N'40B',N'好' union all
select N'40C',N'好' union all
select N'40A',N'好'
Go
DECLARE @s NVARCHAR(4000)
SET @s=N'select [状态]=isnull(a.[状态],''ToTal'')'
Select @s=@s+','+QUOTENAME([TYPE])+'=sum(case when [TYPE]='+QUOTENAME([TYPE],'''')+' then 1 else 0 end)' from #T GROUP BY [TYPE]

EXEC(@s+N' from (SELECT cast(N''好'' as nvarchar(50)) AS [状态] UNION ALL SELECT N''坏'') as a left join #T as b on a.[状态]=b.[状态] group by a.[状态] with rollup;')


/*
状态 40A 40B 40C
好 2 1 1
坏 0 0 0
ToTal 2 1 1
*/
回复
bigmingming 2011-12-13
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([TYPE] varchar(10),[状态] varchar(10))
insert [tb]
select '40A','好' union all
select '40B','好' union all
select '40B','好' union all
select '40C','好' union all
select '40A','好'


declare @s varchar(max)
set @s=''
select @s=@s+','+quotename([TYPE])+'=sum(case when [TYPE]='+quotename([TYPE],'''')+' then 1 else 0 end)'
from [tb] group by[TYPE]
set @s = 'select isnull([状态],''ToTal'')'+@s+' from [tb] group by [状态] with ROLLUP'
exec(@s)

--------------------------------------
好 2 2 1
ToTal 2 2 1



没有坏 0

希望
--------------------------------------
好 2 2 1
坏 0 0 0
ToTal 2 2 1
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2011-12-13 10:16
社区公告
暂无公告