sql 两表统计查询
有两张表 表A 字段有 id palyid senddate 表B 字段有 id sex senddate
如表A 有如下两条数据
id palyid senddate
1 20 2010-10-22
2 30 2010-10-20
表B有下面四条数据
id sex senddate
1 0 2010-10-20
2 0 2010-10-20
3 1 2010-10-22
4 0 2010-10-19
怎么查询通过senddate来判断相等的时间下有这样的数据 不相等就将各自的详细信息以一张新表获得结果 如
id senddate team person
1 2010-10-22 1 1
2 2010-10-20 1 2
3 2010-10-19 0 1
求大神帮忙 sql2005
[解决办法]
其实不知道你最终想要如何的，你看看是不是这样？
SELECT ( ROW_NUMBER() OVER ( ORDER BY r.send_date DESC ) ) rownumber ,
send_date ,
( SELECT COUNT(*)
FROM T_xmim_provide_log r1
WHERE r1.send_date = r.send_date
) AS person ,
( SELECT COUNT(*)
FROM T_xmim_Group_Provide_log r2
WHERE r2.send_date = r.send_date
) AS team
FROM T_xmim_provide_log r
GROUP BY send_date
UNION ALL
SELECT ( ROW_NUMBER() OVER ( ORDER BY r.send_date DESC ) ) rownumber ,
send_date ,
( SELECT COUNT(*)
FROM T_xmim_provide_log r1
WHERE r1.send_date <> r.send_date
) AS person ,
( SELECT COUNT(*)
FROM T_xmim_Group_Provide_log r2
WHERE r2.send_date <> r.send_date
) AS team
FROM T_xmim_provide_log r
GROUP BY send_date
create table #A(id int,palyid int,senddate datetime)
insert into #A select 1 ,20,'2010-10-22'
union all select 2,30,'2010-10-20'
create table #B(id int,sex int,senddate datetime)
insert into #B select 1,0,'2010-10-20'
union all select 2,0,'2010-10-20'
union all select 3,1,'2010-10-22'
union all select 4,0,'2010-10-19'
;with ceb as
(
select a.senddate,
count(*) as person
from #B a group by a.senddate
)
select (ROW_NUMBER() over(order by senddate)) id ,b.senddate,b.person,isnull((select (case when a.palyid is not null then 1 else 0 end) as team from #A a where a.senddate=b.senddate ),0) as team
from ceb b
