SQL Server 2008 R2——统计各部门某年入职人数

=================================版权声明=================================

版权声明：原创文章 谢绝转载 

请通过右侧公告中的“联系邮箱（wlsandwho@foxmail.com）”联系我

勿用于学术性引用。

勿用于商业出版、商业印刷、商业引用以及其他商业用途。　　　　　　　　　　　　　　　　   

 

本文不定期修正完善。

本文链接：http://www.cnblogs.com/wlsandwho/p/4860243.html

耻辱墙：http://www.cnblogs.com/wlsandwho/p/4206472.html

=======================================================================

在国庆假期的最后一天晚上，终于碰到个好例子。于是抽时间写了下。

本着“有码有真相”的原则，附带了创建表和测试数据。只需完全复制就可运行。

做到打脸打到底，让那些只想要代码不想动脑筋的人没有话说。

偏偏我就是不用你的字段，自己做一个完整例子，让你自己去看、去想。

=======================================================================

话不多说上例子。

企鹅截图模糊版

企鹅局部截图：

USE tempdb
GO

IF OBJECT_ID (N't_Department', N'U') IS NOT NULL
DROP TABLE t_Department;
GO
IF OBJECT_ID (N't_TakingWork', N'U') IS NOT NULL
DROP TABLE t_TakingWork;
GO
IF OBJECT_ID (N't_Employee', N'U') IS NOT NULL
DROP TABLE t_Employee;
GO
 
CREATE TABLE t_Department
(did NVARCHAR(3) PRIMARY KEY,
dname NVARCHAR(5),
dcity NVARCHAR(10))
GO
CREATE TABLE t_TakingWork
(eid NVARCHAR(5) PRIMARY KEY,
did NVARCHAR(3),
hiredate DATETIME,
salary INTEGER)
GO
CREATE TABLE t_Employee
(eid NVARCHAR(5) PRIMARY KEY,
ename NVARCHAR(20),
hiredate DATETIME,
gender nCHAR(1),
city NVARCHAR(10))
GO

INSERT INTO t_Department VALUES('101','部门1','城市1')
INSERT INTO t_Department VALUES('201','部门2','城市2')
INSERT INTO t_Department VALUES('301','部门3','城市3')
INSERT INTO t_Department VALUES('401','部门4','城市4')
INSERT INTO t_Department VALUES('501','部门5','城市5')
INSERT INTO t_Department VALUES('601','部门6','城市6')
INSERT INTO t_Department VALUES('701','部门7','城市7')
INSERT INTO t_Department VALUES('801','部门8','城市8')
GO

INSERT INTO t_TakingWork VALUES('a0001','101','2008-12-05',3300)
INSERT INTO t_TakingWork VALUES('h0007','101','2008-10-14',4840)
INSERT INTO t_TakingWork VALUES('i0008','303','2008-01-05',3850)
INSERT INTO t_TakingWork VALUES('a0011','404','2009-02-25',3960)
INSERT INTO t_TakingWork VALUES('n1010','505','1997-07-07',4950)
INSERT INTO t_TakingWork VALUES('p0004','606','2010-10-24',8800)
INSERT INTO t_TakingWork VALUES('q1009','707','2008-12-05',6600)
INSERT INTO t_TakingWork VALUES('r0002','808','1992-02-02',7700)
GO

INSERT INTO t_Employee VALUES('a0001','aa','1993-05-08','m','城市1')
INSERT INTO t_Employee VALUES('a1111','bb','1993-05-09','f','城市2')
INSERT INTO t_Employee VALUES('h0007','cc','1993-05-10','m','城市8')
INSERT INTO t_Employee VALUES('i0008','dd','1993-05-11','f','城市7')
INSERT INTO t_Employee VALUES('n1010','ee','1993-05-12','f','城市6')
INSERT INTO t_Employee VALUES('p0004','ff','1993-05-13','f','城市5')
INSERT INTO t_Employee VALUES('q1009','gg','1993-05-14','f','城市4')
INSERT INTO t_Employee VALUES('r0002','hh','1993-05-15','f','城市3')
INSERT INTO t_Employee VALUES('t0006','ii','1993-05-16','f','城市2')
INSERT INTO t_Employee VALUES('w0005','jj','1993-05-17','m','城市1')
GO

SELECT * FROM t_Department
SELECT * FROM t_TakingWork
SELECT * FROM t_Employee
GO

WITH TempRes
AS(
SELECT  t_TakingWork.eid,t_TakingWork.did,DATEPART(YEAR,t_TakingWork.hiredate) AS hideyear
FROM    t_TakingWork
        LEFT JOIN t_Department ON t_Department.did = t_TakingWork.did
where t_TakingWork.hiredate < '2011-01-01 00:00:00.000'
        AND t_TakingWork.hiredate > '2007-12-31 23:59:59.000'
)
SELECT * FROM TempRes PIVOT(COUNT(eid)FOR hideyear IN([2008],[2009],[2010])) AS T
GO

执行结果

 附上执行计划

=======================================================================

细节效率什么的放过我吧。

非专业SQL，不求高效，但求能跑。