我有这么一个表
ID TIME DATABAE RECORD USERT LATETIME TYPE
1 20071023:100350 国图书目库 1 root 20070405 修改
2 20071009:120947 国图书目库 8 root 20070612 删除
3 20071009:165136 国图书目库 6 root 20070824 原编
4 20071023:100350 国图书目库 1 root1 20070405 修改
5 20071009:120947 国图书目库 8 root1 20070612 删除
6 20071009:165136 国图书目库 6 root1 20070824 原编 我用什么语句把他变成这样的 用户 原编 修改 删除
root 1 1 1
root1 1 1 1
总量 2 2 2就是想统计一下类型的个数
总量的数值为它之前的所有数的和
谢谢!!!!!!!!!!!
ID TIME DATABAE RECORD USERT LATETIME TYPE
1 20071023:100350 国图书目库 1 root 20070405 修改
2 20071009:120947 国图书目库 8 root 20070612 删除
3 20071009:165136 国图书目库 6 root 20070824 原编
4 20071023:100350 国图书目库 1 root1 20070405 修改
5 20071009:120947 国图书目库 8 root1 20070612 删除
6 20071009:165136 国图书目库 6 root1 20070824 原编 我用什么语句把他变成这样的 用户 原编 修改 删除
root 1 1 1
root1 1 1 1
总量 2 2 2就是想统计一下类型的个数
总量的数值为它之前的所有数的和
谢谢!!!!!!!!!!!
sum(case TYPE when '原编' then 1 else 0 end) 原编,
sum(case TYPE when '修改' then 1 else 0 end) 修改,
sum(case TYPE when '删除' then 1 else 0 end) 删除
from tb
group by USERT
insert into tb values(1,'20071023:100350','国图书目库',1,'root' ,'20070405','修改')
insert into tb values(2,'20071009:120947','国图书目库',8,'root' ,'20070612','删除')
insert into tb values(3,'20071009:165136','国图书目库',6,'root' ,'20070824','原编')
insert into tb values(4,'20071023:100350','国图书目库',1,'root1','20070405','修改')
insert into tb values(5,'20071009:120947','国图书目库',8,'root1','20070612','删除')
insert into tb values(6,'20071009:165136','国图书目库',6,'root1','20070824','原编')
go--静态SQL,指TYPE只有这三项
select USERT,
sum(case TYPE when '原编' then 1 else 0 end) 原编,
sum(case TYPE when '修改' then 1 else 0 end) 修改,
sum(case TYPE when '删除' then 1 else 0 end) 删除
from tb
group by USERT
/*
USERT 原编 修改 删除
---------- ----------- ----------- -----------
root 1 1 1
root1 1 1 1(所影响的行数为 2 行)
*/--动态SQL,指TYPE不止这三项
declare @sql varchar(8000)
set @sql = 'select USERT'
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 USERT'
exec(@sql)
/*
USERT 删除 修改 原编
---------- ----------- ----------- -----------
root 1 1 1
root1 1 1 1
*/drop table tb
insert @a select 1,'20071023:100350','国图书目库',1,'root' ,'20070405','修改'
union all select 2,'20071009:120947','国图书目库',8,'root' ,'20070612','删除'
union all select 3,'20071009:165136','国图书目库',6,'root' ,'20070824','原编'
union all select 4,'20071023:100350','国图书目库',1,'root1','20070405','修改'
union all select 5,'20071009:120947','国图书目库',8,'root1','20070612','删除'
union all select 6,'20071009:165136','国图书目库',6,'root1','20070824','原编'
select isnull(用户,'总量') 用户,sum(原编) 原编,sum(修改) 修改,sum(删除) 删除
from(
select
USERT 用户,
原编=sum(case when Type='原编' then 1 end),
修改=sum(case when Type='修改' then 1 end),
删除=sum(case when Type='删除' then 1 end)
from @a
group by USERT
)aa
group by 用户 with rollup
--result
/*用户 原编 修改 删除
---------- ----------- ----------- -----------
root 1 1 1
root1 1 1 1
总量 2 2 2(所影响的行数为 3 行)
*/
insert into tb values(1,'20071023:100350','国图书目库',1,'root' ,'20070405','修改')
insert into tb values(2,'20071009:120947','国图书目库',8,'root' ,'20070612','删除')
insert into tb values(3,'20071009:165136','国图书目库',6,'root' ,'20070824','原编')
insert into tb values(4,'20071023:100350','国图书目库',1,'root1','20070405','修改')
insert into tb values(5,'20071009:120947','国图书目库',8,'root1','20070612','删除')
insert into tb values(6,'20071009:165136','国图书目库',6,'root1','20070824','原编')
go--静态SQL,指TYPE只有这三项
select USERT,
sum(case TYPE when '原编' then 1 else 0 end) 原编,
sum(case TYPE when '修改' then 1 else 0 end) 修改,
sum(case TYPE when '删除' then 1 else 0 end) 删除
from tb
group by USERT
union all
select USERT = '总量',sum(原编) 原编 , sum(修改) 修改,sum(删除) 删除 from
(
select USERT,
sum(case TYPE when '原编' then 1 else 0 end) 原编,
sum(case TYPE when '修改' then 1 else 0 end) 修改,
sum(case TYPE when '删除' then 1 else 0 end) 删除
from tb
group by USERT
) t/*
USERT 原编 修改 删除
---------- ----------- ----------- -----------
root 1 1 1
root1 1 1 1
总量 2 2 2
*/--动态SQL,指TYPE不止这三项
declare @sql varchar(8000)
set @sql = 'select USERT'
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 USERT'
exec(@sql)
/*
USERT 删除 修改 原编
---------- ----------- ----------- -----------
root 1 1 1
root1 1 1 1
*/drop table tb
insert into tb values(1,'20071023:100350','国图书目库',1,'root' ,'20070405','修改')
insert into tb values(2,'20071009:120947','国图书目库',8,'root' ,'20070612','删除')
insert into tb values(3,'20071009:165136','国图书目库',6,'root' ,'20070824','原编')
insert into tb values(4,'20071023:100350','国图书目库',1,'root1','20070405','修改')
insert into tb values(5,'20071009:120947','国图书目库',8,'root1','20070612','删除')
insert into tb values(6,'20071009:165136','国图书目库',6,'root1','20070824','原编')
go--静态SQL,指TYPE只有这三项
select USERT,
sum(case TYPE when '原编' then 1 else 0 end) 原编,
sum(case TYPE when '修改' then 1 else 0 end) 修改,
sum(case TYPE when '删除' then 1 else 0 end) 删除
from tb
group by USERT
union all
select USERT = '总量',sum(原编) 原编 , sum(修改) 修改,sum(删除) 删除 from
(
select USERT,
sum(case TYPE when '原编' then 1 else 0 end) 原编,
sum(case TYPE when '修改' then 1 else 0 end) 修改,
sum(case TYPE when '删除' then 1 else 0 end) 删除
from tb
group by USERT
) t/*
USERT 原编 修改 删除
---------- ----------- ----------- -----------
root 1 1 1
root1 1 1 1
总量 2 2 2
*/--动态SQL,指TYPE不止这三项
declare @sql varchar(8000)
set @sql='select usert=isnull(usert,''合计'')'
select @sql=@sql+',['+Type+']=sum(case Type when '''+Type+''' then 1 else 0 end)'
from tb group by Type
set @sql=@sql+' from tb group by usert with rollup'
exec(@sql)
go
/*
usert 删除 修改 原编
---------- ----------- ----------- -----------
root 1 1 1
root1 1 1 1
合计 2 2 2
*/drop table tb
insert into tb values(1,'20071023:100350','国图书目库',1,'root' ,'20070405','修改')
insert into tb values(2,'20071009:120947','国图书目库',8,'root' ,'20070612','删除')
insert into tb values(3,'20071009:165136','国图书目库',6,'root' ,'20070824','原编')
insert into tb values(4,'20071023:100350','国图书目库',1,'root1','20070405','修改')
insert into tb values(5,'20071009:120947','国图书目库',8,'root1','20070612','删除')
insert into tb values(6,'20071009:165136','国图书目库',6,'root1','20070824','原编')
go--静态SQL,指TYPE只有这三项
select isnull(USERT,'总量') usert,sum(原编) 原编 , sum(修改) 修改,sum(删除) 删除 from
(
select USERT,
sum(case TYPE when '原编' then 1 else 0 end) 原编,
sum(case TYPE when '修改' then 1 else 0 end) 修改,
sum(case TYPE when '删除' then 1 else 0 end) 删除
from tb
group by USERT
) t
group by usert with rollup
/*
USERT 原编 修改 删除
---------- ----------- ----------- -----------
root 1 1 1
root1 1 1 1
总量 2 2 2
*/--动态SQL,指TYPE不止这三项
declare @sql varchar(8000)
set @sql='select usert=isnull(usert,''总量'')'
select @sql=@sql+',['+Type+']=sum(case Type when '''+Type+''' then 1 else 0 end)'
from tb group by Type
set @sql=@sql+' from tb group by usert with rollup'
exec(@sql)
go
/*
usert 删除 修改 原编
---------- ----------- ----------- -----------
root 1 1 1
root1 1 1 1
总量 2 2 2
*/drop table tb
create table tb(ID int,TIME varchar(20),DATABAE varchar(20),RECORD int,USERT varchar(10),LATETIME datetime,TYPE varchar(10))
insert into tb values(1,'20071023:100350','国图书目库',1,'root' ,'20070405','修改')
insert into tb values(2,'20071009:120947','国图书目库',8,'root' ,'20070612','删除')
insert into tb values(3,'20071009:165136','国图书目库',6,'root' ,'20070824','原编')
insert into tb values(4,'20071023:100350','国图书目库',1,'root1','20070405','修改')
insert into tb values(5,'20071009:120947','国图书目库',8,'root1','20070612','删除')
insert into tb values(6,'20071009:165136','国图书目库',6,'root1','20070824','原编')
go--静态SQL,指TYPE只有这三项
select isnull(USERT,'总量') usert,sum(原编) 原编 , sum(修改) 修改,sum(删除) 删除 ,sum(总量) 总量 from
(
select USERT,
sum(case TYPE when '原编' then 1 else 0 end) 原编,
sum(case TYPE when '修改' then 1 else 0 end) 修改,
sum(case TYPE when '删除' then 1 else 0 end) 删除,
count(*) 总量
from tb
group by USERT
) t
group by usert with rollup
/*
usert 原编 修改 删除 总量
---------- ----------- ----------- ----------- -----------
root 1 1 1 3
root1 1 1 1 3
总量 2 2 2 6
*/--动态SQL,指TYPE不止这三项
declare @sql varchar(8000)
set @sql='select usert=isnull(usert,''总量'')'
select @sql=@sql+',['+Type+']=sum(case Type when '''+Type+''' then 1 else 0 end)'
from tb group by Type
set @sql=@sql+',count(*) 总量 from tb group by usert with rollup'
exec(@sql)
go
/*
usert 原编 修改 删除 总量
---------- ----------- ----------- ----------- -----------
root 1 1 1 3
root1 1 1 1 3
总量 2 2 2 6
*/drop table tb
set @sql=''select @sql=@sql+',sum(case when type='''+type+''' then num else 0 end) ['+type+']'
from (select distinct type as type from t_a_test) aexec(' select usert as 用户'
+@sql
+' from (select usert,TYPE,count(*) as num from t_a_test group by usert,TYPE) _h'
+' group by usert union all select ''总量'''
+@sql
+' from (select usert,TYPE,count(*) as num from t_a_test group by usert,TYPE) _h'
)