急！求一sql语句。关于统计

[l331326473]

有两个表：
表一：
入职时间
2012-07-08
2012-06-09
表二：
离职时间
2012-05-06
2012-06-04
统计结果显示为：
日期 离职人数 入职人数
2012-05 1 0
2012-06 1 1
2012-07 0 1
该怎么写sql语句？当然表中还有其他字段，做这个查询用不到，就不再写。

[l331326473]

SELECT DISTINCT LEFT(ISNULL(YL_YGXX_RZ.RZRQ, YL_YGXX_LZ.LZRQ), 7) AS NF,
(SELECT COUNT(*) AS Expr1
FROM dbo.YL_YGXX_RZ AS YL_YGXX_RZ_1
WHERE (CONVERT(VARCHAR(7), RZRQ, 120) = CONVERT(VARCHAR(7), YL_YGXX_RZ.RZRQ, 120))) AS RZNum,
(SELECT COUNT(*) AS Expr1
FROM dbo.YL_YGXX_LZ AS YL_YGXX_LZ_1
WHERE (CONVERT(VARCHAR(7), LZRQ, 120) = CONVERT(VARCHAR(7), YL_YGXX_LZ.LZRQ, 120))) AS LZNum
FROM dbo.YL_YGXX_RZ AS YL_YGXX_RZ FULL OUTER JOIN
dbo.YL_YGXX_LZ AS YL_YGXX_LZ ON CONVERT(VARCHAR(7), YL_YGXX_RZ.RZRQ, 120) = CONVERT(VARCHAR(7), YL_YGXX_LZ.LZRQ, 120)

[天-笑]

;with t1 as 
(
	select '2012-07-08' as t union all
	select '2012-06-09' as t 
),t2 as 
(
	select '2012-05-06' as t union all
	select '2012-06-04' as t 
),t3 as 
(
	select t,1 as s from t1 union all
	select t,2 from t2
)
select t as 日期, 
	sum(case when s = 2 then 1 else 0 end) as [离职人数],
	sum(case when s = 1 then 1 else 0 end) as [入职人数]
from t3 
group by t

/*
日期         离职人数        入职人数
---------- ----------- -----------
2012-05-06 1           0
2012-06-04 1           0
2012-06-09 0           1
2012-07-08 0           1

(4 行受影响)



*/

[昵称被占用了]

SELECT CONVERT(VARCHAR(7),时间,120) AS 日期
,SUM(CASE WHEN TYPE = 2 THEN 1 ELSE 0 END) AS 离职人数
,SUM(CASE WHEN TYPE = 1 THEN 1 ELSE 0 END) AS 入职人数
FROM 
SELECT 入职时间 AS 时间,1 AS TYPE FROM 表一 
UNION ALL
SELECT 离职时间 AS 时间,2 AS TYPE FROM 表二
) AS A
GROUP BY CONVERT(VARCHAR(7),时间,120)

[天-笑]

;with t1 as 
(
	select '2012-07-08' as t union all
	select '2012-06-09' as t 
),t2 as 
(
	select '2012-05-06' as t union all
	select '2012-06-04' as t 
),t3 as 
(
	select t,1 as s from t1 union all
	select t,2 from t2
)
select convert(varchar(7),t,120) as 日期, 
	sum(case when s = 2 then 1 else 0 end) as [离职人数],
	sum(case when s = 1 then 1 else 0 end) as [入职人数]
from t3 
group by convert(varchar(7),t,120)

/*
日期      离职人数        入职人数
------- ----------- -----------
2012-05 1           0
2012-06 1           1
2012-07 0           1

(3 行受影响)


*/

[开着拖拉机泡妞]

----------------------------------------------------------------
-- Author  :TravyLee(努力工作中！！！)
-- Date    :2012-08-08 08:55:41
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
--	Apr  2 2010 15:48:46 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([InDate] datetime)
insert [A]
select '2012-07-08' union all
select '2012-06-09'
go
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([OutDate] datetime)
insert [B]
select '2012-05-06' union all
select '2012-06-04'
go

;with 
t as(
select *,'IN' as States from A
union all
select *,'Out' from B
)
select 
	convert(varchar(7),Indate,120) as 时间,
	SUM(case when States='IN' then 1 else 0 end) as 入职,
	SUM(case when States='Out' then 1 else 0 end) as 离职
from t
group by 
convert(varchar(7),Indate,120)

/*
时间	入职	离职
---------------------------------
2012-05	0	1
2012-06	1	1
2012-07	1	0
*/

[以学习为目的]

--> 测试数据：
declare @TA table ([INDATE] datetime)
declare @TB table ([OUTDATE] datetime)
insert @TA
select '2012-07-08' union all
select '2012-06-09'
insert @TB
select '2012-05-06' union all
select '2012-06-04'

;with cte as
(select INDATE,'IN' as States from @TA
union all
select OUTDATE,'OUT' as States from @TB
)
select 
    convert(varchar(7),INDATE,120) as 时间,
    SUM(case when States='IN' then 1 else 0 end) as 入职人数,
    SUM(case when States='OUT' then 1 else 0 end) as 离职人数
from cte
group by 
convert(varchar(7),INDATE,120)

/*
时间    入职    离职
---------------------------------
2012-05    0    1
2012-06    1    1
2012-07    1    0
*/