有两个表：
表一：
入职时间
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语句？当然表中还有其他字段，做这个查询用不到，就不再写。
------解决方案--------------------
- SQL code
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 ALLSELECT 离职时间 AS 时间,2 AS TYPE FROM 表二) AS AGROUP BY CONVERT(VARCHAR(7),时间,120)
------解决方案--------------------
- SQL code
;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 02012-06 1 12012-07 0 1(3 行受影响)*/
------解决方案--------------------
- SQL code
------------------------------ 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 allselect '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 allselect '2012-06-04'go;with t as(select *,'IN' as States from Aunion allselect *,'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 tgroup by convert(varchar(7),Indate,120)/*时间 入职 离职---------------------------------2012-05 0 12012-06 1 12012-07 1 0*/
------解决方案--------------------
- SQL code
--> 测试数据：declare @TA table ([INDATE] datetime)declare @TB table ([OUTDATE] datetime)insert @TAselect '2012-07-08' union allselect '2012-06-09'insert @TBselect '2012-05-06' union allselect '2012-06-04';with cte as(select INDATE,'IN' as States from @TAunion allselect 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 ctegroup by convert(varchar(7),INDATE,120)/*时间 入职 离职---------------------------------2012-05 0 12012-06 1 12012-07 1 0*/