求高效分组-统计-查询sql语句！！！

[love_study]

有如下表：
create table t_d(
d_id int identity(1,1) primary key,
d_name varchar(50), --名称
)
insert into t_d(d_name) values('A')
insert into t_d(d_name) values('B')
insert into t_d(d_name) values('C')

create table t_e(
e_id int identity(1,1) primary key,
d_id int references t_d(d_id),
add_time datetime, --添加日期
lost_time datetime --过期日期
)
insert into t_e (d_id,add_time,lost_time) values(1,2008-10-4,2008-10-5)
insert into t_e (d_id,add_time,lost_time) values(1,2008-10-6,2008-10-7)
insert into t_e (d_id,add_time,lost_time) values(1,2008-10-6,2008-10-8)
insert into t_e (d_id,add_time,lost_time) values(2,2008-10-6,2008-10-6)
insert into t_e (d_id,add_time,lost_time) values(2,2008-10-6,2008-10-9)

希望对 t_d 表进行分组统计查询，得到如下结果形式： 当天日期为 2008-10-6，若当天日期 超过 2008-10-6 则视为过期
d_id d_name 发行数量 过期数量
1 A 3 1
2 B 2 0
3 C 0 0

[love_study]

数据量较大 ，要高效的

[tim_spac]

select
     a.d_id
    ,d_name
    ,count(1) as [发行数量]
    ,sum(case when lost_time < @dt then 1 else 0 end) as [过期数量]
from t_e a
join t_d b on a.d_id=b.d_id
group by a.d_id, d_name

[zheninchangjiang]

[SQL]
select a.*,b.counta,b.countl
from t_d a join
(select d_id,count(*) counta,sum(case when datediff(day,lost_time,getdate())<0 then 1 else 0 end) countl from t_e group by d_id) b
on a.d_id=b.d_id
[SQL]
前阵子看到把日期当int处理的，值得一用

[wzy_love_sly]

select a.d_id,a.d_name,
count(1) as 发行数量,
sum(case when datediff(dd,b.lost_time,getdate())>0 then 1 else 0 end) as 过期数量
from t_d a left join t_e b on a.d_id=b.d_id
group by a.d_id,a.d_name

d_id d_name 发行数量 过期数量
1 A 3 1
2 B 2 0
3 C 1 0

[liangCK]

create table t_d( 
d_id int identity(1,1) primary key, 
d_name varchar(50), --名称 
) 
insert into t_d(d_name) values('A') 
insert into t_d(d_name) values('B') 
insert into t_d(d_name) values('C') 

create table t_e( 
e_id int identity(1,1) primary key, 
d_id int references t_d(d_id), 
add_time datetime, --添加日期 
lost_time datetime --过期日期 
) 
insert into t_e (d_id,add_time,lost_time) values(1,'2008-10-4','2008-10-5') 
insert into t_e (d_id,add_time,lost_time) values(1,'2008-10-6','2008-10-7') 
insert into t_e (d_id,add_time,lost_time) values(1,'2008-10-6','2008-10-8') 
insert into t_e (d_id,add_time,lost_time) values(2,'2008-10-6','2008-10-6') 
insert into t_e (d_id,add_time,lost_time) values(2,'2008-10-6','2008-10-9') 

SELECT td.d_id,td.d_name ,
       COUNT(*) AS 发行数量,
       SUM(CASE WHEN te.lost_time>GETDATE() THEN 1 ELSE 0 END) AS 过期数量 
FROM t_d AS td
 LEFT OUTER JOIN t_e AS te
ON td.d_id=te.d_id 
GROUP BY td.d_id,td.d_name 

drop table t_e,t_d

/*
d_id        d_name                                             发行数量        过期数量
----------- -------------------------------------------------- ----------- -----------
1           A                                                  3           2
2           B                                                  2           1
3           C                                                  1           0

(3 行受影响)

*/

[love_study]

都是错的啊，C 的发行应该是 0 啊，因为C 没有在 t_e 表有数据，所以C 发行应该是 0

[liangCK]

这样呢?

create table t_d( 
d_id int identity(1,1) primary key, 
d_name varchar(50), --名称 
) 
insert into t_d(d_name) values('A') 
insert into t_d(d_name) values('B') 
insert into t_d(d_name) values('C') 

create table t_e( 
e_id int identity(1,1) primary key, 
d_id int references t_d(d_id), 
add_time datetime, --添加日期 
lost_time datetime --过期日期 
) 
insert into t_e (d_id,add_time,lost_time) values(1,'2008-10-4','2008-10-5') 
insert into t_e (d_id,add_time,lost_time) values(1,'2008-10-6','2008-10-7') 
insert into t_e (d_id,add_time,lost_time) values(1,'2008-10-6','2008-10-8') 
insert into t_e (d_id,add_time,lost_time) values(2,'2008-10-6','2008-10-6') 
insert into t_e (d_id,add_time,lost_time) values(2,'2008-10-6','2008-10-9') 

SELECT td.d_id,td.d_name ,
       COUNT(te.e_id) AS 发行数量,
       SUM(CASE WHEN te.lost_time<GETDATE() THEN 1 ELSE 0 END) AS 过期数量 
FROM t_d AS td
 LEFT OUTER JOIN t_e AS te
ON td.d_id=te.d_id 
GROUP BY td.d_id,td.d_name 

drop table t_e,t_d


/*
d_id        d_name                                             发行数量        过期数量
----------- -------------------------------------------------- ----------- -----------
1           A                                                  3           1
2           B                                                  2           1
3           C                                                  0           0
*/

[tim_spac]

select
     a.d_id
    ,d_name
    ,isnull([发行数量],0)
    ,isnull([过期数量],0)
from t_d b 
left join (
   select 
     d_id
    ,count(1) as [发行数量]
    ,sum(case when lost_time < @dt then 1 else 0 end) as [过期数量]
   from t_e 
   group by d_id) as a on a.d_id=b.d_id

[liangCK]

create table t_d( 
d_id int identity(1,1) primary key, 
d_name varchar(50), --名称 
) 
insert into t_d(d_name) values('A') 
insert into t_d(d_name) values('B') 
insert into t_d(d_name) values('C') 

create table t_e( 
e_id int identity(1,1) primary key, 
d_id int references t_d(d_id), 
add_time datetime, --添加日期 
lost_time datetime --过期日期 
) 
insert into t_e (d_id,add_time,lost_time) values(1,'2008-10-4','2008-10-5') 
insert into t_e (d_id,add_time,lost_time) values(1,'2008-10-6','2008-10-7') 
insert into t_e (d_id,add_time,lost_time) values(1,'2008-10-6','2008-10-8') 
insert into t_e (d_id,add_time,lost_time) values(2,'2008-10-6','2008-10-6') 
insert into t_e (d_id,add_time,lost_time) values(2,'2008-10-6','2008-10-9') 

SELECT td.d_id,td.d_name ,
       COUNT(te.e_id) AS 发行数量,
       SUM(CASE WHEN DATEDIFF(DAY,te.lost_time,GETDATE())>0 THEN 1 ELSE 0 END) AS 过期数量 
FROM t_d AS td
 LEFT OUTER JOIN t_e AS te
ON td.d_id=te.d_id 
GROUP BY td.d_id,td.d_name 

drop table t_e,t_d


/*
d_id        d_name                                             发行数量        过期数量
----------- -------------------------------------------------- ----------- -----------
1           A                                                  3           1
2           B                                                  2           0
3           C                                                  0           0
*/

[cxmcxm]

declare @curdate datetime
set @curdate='2008-10-6'
select a.d_id,a.d_name,isnull(b.allcount,0) as 发行数量,isnull(b.lostcount,0) as 过期数量
from t_d a left join 
	 (select d_id,count(*) allcount,sum(case when lost_time<@curdate then 1 else 0 end) lostcount
	  from t_e group by d_id) b
     on a.d_id=b.d_id