求一个sql语句写法

[treeway]

A表：
cardtypeid,date_time
1 2007/02/03
2 2007/03/03
3 2007/04/03
2 2007/04/03
B表：
cardtypeid, cardtypeName
1 甲
2 乙
3 丙

查询结果：

月份 甲 乙 丙 合计
2007/02 1 0 0 1
2007/03 0 1 0 1
2007/04 0 1 1 2
合计 1 2 1 4

[duanzhi1984]

使用动态语句得到上半部
再用UNION联结最后一条汇总的数据

，今天有点晚了， 明天再来吧
我要下班了

[dawugui]

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/02/03')
insert into A(cardtypeid,date_time) values(2,'2007/03/03')
insert into A(cardtypeid,date_time) values(3,'2007/04/03')
insert into A(cardtypeid,date_time) values(2,'2007/04/03')
go

if 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,'丙')
go

declare @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 , convert(varchar(7),a.date_time,120) 月份 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t) as m
set @sql = @sql + ' from (select a.cardtypeid , convert(varchar(7),a.date_time,120) 月份 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t group by 月份 with rollup'
exec(@sql)

drop table A,B

/*
月份 丙 甲 乙
------- ----------- ----------- -----------
2007-02 0 1 0
2007-03 0 0 1
2007-04 1 0 1
合计 1 1 2
*/

[dawugui]

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/02/03')
insert into A(cardtypeid,date_time) values(2,'2007/03/03')
insert into A(cardtypeid,date_time) values(3,'2007/04/03')
insert into A(cardtypeid,date_time) values(2,'2007/04/03')
go

if 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,'丙')
go

declare @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 , convert(varchar(7),a.date_time,120) 月份 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t) as m
set @sql = @sql + ' ,count(*) as 合计 from (select a.cardtypeid , convert(varchar(7),a.date_time,120) 月份 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t group by 月份 with rollup'
exec(@sql)

drop table A,B

/*
月份 丙 甲 乙 合计
------- ----------- ----------- ----------- -----------
2007-02 0 1 0 1
2007-03 0 0 1 1
2007-04 1 0 1 2
合计 1 1 2 4
*/

[treeway]

谢谢你 潇洒老乌龟 ：）

[dawugui]

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/02/03')
insert into A(cardtypeid,date_time) values(2,'2007/03/03')
insert into A(cardtypeid,date_time) values(3,'2007/04/03')
insert into A(cardtypeid,date_time) values(2,'2007/04/03')
go

if 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,'丙')
go

declare @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 , convert(varchar(10),a.date_time,120) 日期 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t) as m
set @sql = @sql + ' ,count(*) as 合计 from (select a.cardtypeid , convert(varchar(10),a.date_time,120) 日期 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t group by 日期 with rollup'
exec(@sql)

drop table A,B

/*
日期 丙 甲 乙 合计
---------- ----------- ----------- ----------- -----------
2007-02-03 0 1 0 1
2007-03-03 0 0 1 1
2007-04-03 1 0 1 2
合计 1 1 2 4

*/