日期:2014-05-18  浏览次数:20669 次

求一条跨表统计语句,谢谢
T1
地区 个人编号 姓名 
1001 1 A
1001 2 B
1002 3 C
1002 4 D
1003 5 E
..
T2
个人编号 建档日期
1 2011-01-03
2 2011-01-04
3 2011-01-05
4 2011-01-06
5 2011-01-07
。。。

结果
查询条件 2011-01-01至2011-01-05
地区 总人数 查询时间范围内建档数
1001 2 2
1002 2 1
1003 1 0
...

------解决方案--------------------
SQL code

--> 测试数据:[T1]
if object_id('[T1]') is not null drop table [T1]
create table [T1]([地区] int,[个人编号] int,[姓名] varchar(1))
insert [T1]
select 1001,1,'A' union all
select 1001,2,'B' union all
select 1002,3,'C' union all
select 1002,4,'D' union all
select 1003,5,'E'
--> 测试数据:[T2]
if object_id('[T2]') is not null drop table [T2]
create table [T2]([个人编号] int,[建档日期] datetime)
insert [T2]
select 1,'2011-01-03' union all
select 2,'2011-01-04' union all
select 3,'2011-01-05' union all
select 4,'2011-01-06' union all
select 5,'2011-01-07'

with t
as(
select a.地区,a.个人编号,b.建档日期 from [T1] a 
full join [T2] b on a.个人编号=b.个人编号
)

select 地区,COUNT(1) as 总人数,
(select COUNT(1) from t b where a.地区=b.地区 
and b.建档日期 between '2011-01-01' and '2011-01-05') as 时间范围内建档人数
from t a
group by 地区

/*
地区    总人数    时间范围内建档人数
1001    2    2
1002    2    1
1003    1    0
*/

------解决方案--------------------
SQL code

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
    DROP TABLE tba
END
GO
create table tba([地区] int,[个人编号] int,[姓名] varchar(1))
insert tba
select 1001,1,'A' union all
select 1001,2,'B' union all
select 1002,3,'C' union all
select 1002,4,'D' union all
select 1003,5,'E'
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tbb')
BEGIN
    DROP TABLE tbb
END
GO
create table tbb([个人编号] int,[建档日期] datetime)
insert tbb
select 1,'2011-01-03' union all
select 2,'2011-01-04' union all
select 3,'2011-01-05' union all
select 4,'2011-01-06' union all
select 5,'2011-01-07'


SELECT A.[地区],COUNT(A.[个人编号]) AS 总人数,CASE WHEN 查询时间范围内建档数 IS NULL THEN 0
                                                   ELSE 查询时间范围内建档数 END AS 查询时间范围内建档数
FROM tba AS A INNER JOIN tbb AS B ON A.个人编号 = B.个人编号
LEFT OUTER JOIN ((SELECT [地区],COUNT([建档日期]) AS 查询时间范围内建档数 FROM tbb,tba WHERE tba.个人编号 = tbb.个人编号 AND [建档日期] BETWEEN '2011-01-01' AND '2011-01-05' GROUP BY [地区])) AS C ON A.地区 = C.地区
GROUP BY A.[地区],查询时间范围内建档数
ORDER BY A.[地区]

地区    总人数    查询时间范围内建档数
1001    2    2
1002    2    1
1003    1    0