关于一sql写法:
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/03 1 0 0 1
2007/03/03 0 1 0 1
2007/04/03 0 1 1 2
合计 1 2 1 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/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
*/
提问的问题是:date_time(日期)和cardtypeName(甲/乙/丙)作为 'where ' 条件,请问该如何写呢? date_time不为空, 不选择cardtypeName可以实现查询,也就是上面的查询结果.
如:
选择一定时间 从2007/04/01到2007/04/05来查询,然后选择cardtypeName为甲得到如下结果:
日期 甲 合计
---------- ----------- ----------- ----------- -----------
2007-04-03 0 0
合计 0 0
[解决办法]
占个位慢慢看。
[解决办法]
--直接加上条件即可。
--用if else 判断一下即可
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
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 @dt1 datetime,@dt2 datetime, @name varchar(100)
set @dt1= '2007/04/01 '
set @dt2= '2007/04/05 '
--set @name= '丙 '
declare @sql varchar(8000)
set @sql = 'select isnull(日期, ' '合计 ' ') 日期 '
if @name is not null
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
and a.date_time> =@dt1 and a.date_time <=@dt2
and b.cardtypeName=@name) t) as m
else
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
and a.date_time> =@dt1 and a.date_time <=@dt2) t) as m
if @name is not null
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
and a.date_time> = ' ' ' + convert(varchar(10),@dt1,120) + ' ' ' and a.date_time <= ' ' ' + convert(varchar(10),@dt2,120) + ' ' '
and b.cardtypeName= ' ' ' + @name + ' ' ') t group by 日期 with rollup '
else
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
and a.date_time> = ' ' ' + convert(varchar(10),@dt1,120) + ' ' ' and a.date_time <= ' ' ' + convert(varchar(10),@dt2,120) + ' ' '
) t group by 日期 with rollup '
exec(@sql)
drop table A,B
/*
日期 丙 合计
---------- ----------- ----------- ----------- -----------
2007-04-03 1 1
合计 1 1
*/
