create table test(zd1 varchar(10),zd2 varchar(10),zd3 datetime,zd4 varchar(10)) insert test select '001','aa','2004-03-02','test' union all select '002','aa',' 2008-03-02','dsds' select zd2,小于当前日期=sum(case when zd3<getdate() then 1 else 0 end), 大于当前日期=sum(case when zd3>getdate() then 1 else 0 end) from test group by zd2
zd2 小于当前日期 大于当前日期 ---------- ----------- ----------- aa 1 1
select aa, 小=sum(case when datediff(day,zd3,getdate)>0 then 1 end), 大=sum(case when datediff(day,zd3,getdate)<0 then 1 end) from [Table] group by aa
declare @t table(zd1 varchar(10), zd2 varchar(10),zd3 varchar(10),zd4 varchar(10)) insert @t select '001', 'aa', '2004-03-02', 'test' union all select '002', 'aa', '2008-03-02', 'dsds'select zd2, 小于当前日期 = sum(case when datediff(dd,zd3,getdate()) > 0 then 1 else 0 end), 大于当前日期 = sum(case when datediff(dd,zd3,getdate()) < 0 then 1 else 0 end) from @t group by zd2/*结果 zd2 小于当前日期 大于当前日期 --------------------------------------------- aa 1 1 */
select zd2 , sum(case when zd3<getdate() then 1 else 0 end ) 小于当前日期, sum(case when zd3>getdate() then 1 else 0 end ) 大于当前日期 from 表 group by zd2
大于或小于当前日期的数据不一定是1 可能有多个 zd1 zd2 zd3 zd4 001 aa 2004-03-02 test 002 aa 2008-03-02 dsds 003 aa 2008-03-01 dsds
一样的create table test(zd1 varchar(10),zd2 varchar(10),zd3 datetime,zd4 varchar(10)) insert test select '001','aa','2004-03-02','test' union all select '002','aa',' 2008-03-02','dsds' union all select '003','aa',' 2008-03-01','dsds' select zd2,小于当前日期=sum(case when zd3<getdate() then 1 else 0 end), 大于当前日期=sum(case when zd3>getdate() then 1 else 0 end) from test group by zd2drop table testzd2 小于当前日期 大于当前日期 ---------- ----------- ----------- aa 1 2
kongxiangli(笑看红尘) ( ) 信誉:100 Blog 加为好友 2007-06-29 09:30:15 得分: 0
create table test(zd1 varchar(10),zd2 varchar(10),zd3 datetime,zd4 varchar(10))
insert test select '001','aa','2004-03-02','test'
union all select '002','aa',' 2008-03-02','dsds'
select zd2,小于当前日期=sum(case when zd3<getdate() then 1 else 0 end),
大于当前日期=sum(case when zd3>getdate() then 1 else 0 end)
from test
group by zd2
---------- ----------- -----------
aa 1 1
小=sum(case when datediff(day,zd3,getdate)>0 then 1 end),
大=sum(case when datediff(day,zd3,getdate)<0 then 1 end)
from [Table] group by aa
insert @t
select '001', 'aa', '2004-03-02', 'test' union all
select '002', 'aa', '2008-03-02', 'dsds'select zd2,
小于当前日期 = sum(case when datediff(dd,zd3,getdate()) > 0 then 1 else 0 end),
大于当前日期 = sum(case when datediff(dd,zd3,getdate()) < 0 then 1 else 0 end)
from @t group by zd2/*结果
zd2 小于当前日期 大于当前日期
---------------------------------------------
aa 1 1
*/
sum(case when zd3<getdate() then 1 else 0 end ) 小于当前日期,
sum(case when zd3>getdate() then 1 else 0 end ) 大于当前日期
from 表 group by zd2
zd1 zd2 zd3 zd4
001 aa 2004-03-02 test
002 aa 2008-03-02 dsds
003 aa 2008-03-01 dsds
insert test select '001','aa','2004-03-02','test'
union all select '002','aa',' 2008-03-02','dsds'
union all select '003','aa',' 2008-03-01','dsds'
select zd2,小于当前日期=sum(case when zd3<getdate() then 1 else 0 end),
大于当前日期=sum(case when zd3>getdate() then 1 else 0 end)
from test
group by zd2drop table testzd2 小于当前日期 大于当前日期
---------- ----------- -----------
aa 1 2
大于或小于当前日期的数据不一定是1 可能有多个-----------
一樣可以,上面的語句都可以用