if object_id('pubs..A') is not null drop table A go create table A(cardtypeid int,date_time datetime) insert into A(cardtypeid,date_time) values(1,'2007-04-08 09:21:56.000') insert into A(cardtypeid,date_time) values(2,'2007-04-08 10:21:56.000') insert into A(cardtypeid,date_time) values(3,'2007-04-09 10:21:56.000') insert into A(cardtypeid,date_time) values(2,'2007-04-09 11:21:56.000') goif object_id('pubs..B') is not null drop table B go create table B(cardtypeid int, cardtypeName varchar(10)) insert into B(cardtypeid, cardtypeName) values(1,'甲') insert into B(cardtypeid, cardtypeName) values(2,'乙') insert into B(cardtypeid, cardtypeName) values(3,'丙') godeclare @sql varchar(8000) set @sql = 'select isnull(时间段,''合计'') 时间段' select @sql = @sql + ' , sum(case cardtypeName when ''' + cardtypeName + ''' then 1 else 0 end) [' + cardtypeName + ']' from (select distinct cardtypeName from (select a.cardtypeid , right(convert(varchar(13),a.date_time,120),2) 时间段 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t) as m set @sql = @sql + ' ,count(*) as 合计 from (select a.cardtypeid , right(convert(varchar(13),a.date_time,120),2) 时间段 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t group by 时间段 with rollup' exec(@sql) drop table A,B/* 时间段 丙 甲 乙 合计 ------ ----------- ----------- ----------- ----------- 09 0 1 0 1 10 1 0 1 2 11 0 0 1 1 合计 1 1 2 4 */
if object_id('pubs..A') is not null drop table A go create table A(cardtypeid int,date_time datetime) insert into A(cardtypeid,date_time) values(1,'2007-04-08 09:21:56.000') insert into A(cardtypeid,date_time) values(2,'2007-04-08 10:21:56.000') insert into A(cardtypeid,date_time) values(3,'2007-04-09 10:21:56.000') insert into A(cardtypeid,date_time) values(2,'2007-04-09 11:21:56.000') goif object_id('pubs..B') is not null drop table B go create table B(cardtypeid int, cardtypeName varchar(10)) insert into B(cardtypeid, cardtypeName) values(1,'甲') insert into B(cardtypeid, cardtypeName) values(2,'乙') insert into B(cardtypeid, cardtypeName) values(3,'丙') godeclare @sql varchar(8000) set @sql = 'select isnull(时间段,''合计'') 时间段' select @sql = @sql + ' , sum(case cardtypeName when ''' + cardtypeName + ''' then 1 else 0 end) [' + cardtypeName + ']' from (select distinct cardtypeName from (select a.cardtypeid , right(convert(varchar(13),a.date_time,120),2)+':00-'+right(convert(varchar(13),a.date_time,120),2)+':59' 时间段 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t) as m set @sql = @sql + ' ,count(*) as 合计 from (select a.cardtypeid , right(convert(varchar(13),a.date_time,120),2)+'':00-''+right(convert(varchar(13),a.date_time,120),2)+'':59'' 时间段 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t group by 时间段 with rollup' exec(@sql) drop table A,B /* 时间段 丙 甲 乙 合计 --------------- ----------- ----------- ----------- ----------- 09:00-09:59 0 1 0 1 10:00-10:59 1 0 1 2 11:00-11:59 0 0 1 1 合计 1 1 2 4 */
drop table A
go
create table A(cardtypeid int,date_time datetime)
insert into A(cardtypeid,date_time) values(1,'2007-04-08 09:21:56.000')
insert into A(cardtypeid,date_time) values(2,'2007-04-08 10:21:56.000')
insert into A(cardtypeid,date_time) values(3,'2007-04-09 10:21:56.000')
insert into A(cardtypeid,date_time) values(2,'2007-04-09 11:21:56.000')
goif object_id('pubs..B') is not null
drop table B
go
create table B(cardtypeid int, cardtypeName varchar(10))
insert into B(cardtypeid, cardtypeName) values(1,'甲')
insert into B(cardtypeid, cardtypeName) values(2,'乙')
insert into B(cardtypeid, cardtypeName) values(3,'丙')
godeclare @sql varchar(8000)
set @sql = 'select isnull(时间段,''合计'') 时间段'
select @sql = @sql + ' , sum(case cardtypeName when ''' + cardtypeName + ''' then 1 else 0 end) [' + cardtypeName + ']'
from (select distinct cardtypeName from (select a.cardtypeid , right(convert(varchar(13),a.date_time,120),2) 时间段 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t) as m
set @sql = @sql + ' ,count(*) as 合计 from (select a.cardtypeid , right(convert(varchar(13),a.date_time,120),2) 时间段 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t group by 时间段 with rollup'
exec(@sql) drop table A,B/*
时间段 丙 甲 乙 合计
------ ----------- ----------- ----------- -----------
09 0 1 0 1
10 1 0 1 2
11 0 0 1 1
合计 1 1 2 4
*/
drop table A
go
create table A(cardtypeid int,date_time datetime)
insert into A(cardtypeid,date_time) values(1,'2007-04-08 09:21:56.000')
insert into A(cardtypeid,date_time) values(2,'2007-04-08 10:21:56.000')
insert into A(cardtypeid,date_time) values(3,'2007-04-09 10:21:56.000')
insert into A(cardtypeid,date_time) values(2,'2007-04-09 11:21:56.000')
goif object_id('pubs..B') is not null
drop table B
go
create table B(cardtypeid int, cardtypeName varchar(10))
insert into B(cardtypeid, cardtypeName) values(1,'甲')
insert into B(cardtypeid, cardtypeName) values(2,'乙')
insert into B(cardtypeid, cardtypeName) values(3,'丙')
godeclare @sql varchar(8000)
set @sql = 'select isnull(时间段,''合计'') 时间段'
select @sql = @sql + ' , sum(case cardtypeName when ''' + cardtypeName + ''' then 1 else 0 end) [' + cardtypeName + ']'
from (select distinct cardtypeName from (select a.cardtypeid , right(convert(varchar(13),a.date_time,120),2)+':00-'+right(convert(varchar(13),a.date_time,120),2)+':59' 时间段 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t) as m
set @sql = @sql + ' ,count(*) as 合计 from (select a.cardtypeid , right(convert(varchar(13),a.date_time,120),2)+'':00-''+right(convert(varchar(13),a.date_time,120),2)+'':59'' 时间段 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t group by 时间段 with rollup'
exec(@sql) drop table A,B
/*
时间段 丙 甲 乙 合计
--------------- ----------- ----------- ----------- -----------
09:00-09:59 0 1 0 1
10:00-10:59 1 0 1 2
11:00-11:59 0 0 1 1
合计 1 1 2 4
*/