你的位置:首页 > 数据库

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


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

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

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

勿用于学术性引用。

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

 

本文不定期修正完善。

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

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

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

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

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

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

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

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

话不多说上例子。

企鹅截图模糊版

 1 USE tempdb 2 GO 3  4 IF OBJECT_ID (N't_Department', N'U') IS NOT NULL 5 DROP TABLE t_Department; 6 GO 7 IF OBJECT_ID (N't_TakingWork', N'U') IS NOT NULL 8 DROP TABLE t_TakingWork; 9 GO10 IF OBJECT_ID (N't_Employee', N'U') IS NOT NULL11 DROP TABLE t_Employee;12 GO13 14 CREATE TABLE t_Department15 (did NVARCHAR(3) PRIMARY KEY,16 dname NVARCHAR(5),17 dcity NVARCHAR(10))18 GO19 CREATE TABLE t_TakingWork20 (eid NVARCHAR(5) PRIMARY KEY,21 did NVARCHAR(3),22 hiredate DATETIME,23 salary INTEGER)24 GO25 CREATE TABLE t_Employee26 (eid NVARCHAR(5) PRIMARY KEY,27 ename NVARCHAR(20),28 hiredate DATETIME,29 gender nCHAR(1),30 city NVARCHAR(10))31 GO32 33 INSERT INTO t_Department VALUES('101','部门1','城市1')34 INSERT INTO t_Department VALUES('201','部门2','城市2')35 INSERT INTO t_Department VALUES('301','部门3','城市3')36 INSERT INTO t_Department VALUES('401','部门4','城市4')37 INSERT INTO t_Department VALUES('501','部门5','城市5')38 INSERT INTO t_Department VALUES('601','部门6','城市6')39 INSERT INTO t_Department VALUES('701','部门7','城市7')40 INSERT INTO t_Department VALUES('801','部门8','城市8')41 GO42 43 INSERT INTO t_TakingWork VALUES('a0001','101','2008-12-05',3300)44 INSERT INTO t_TakingWork VALUES('h0007','101','2008-10-14',4840)45 INSERT INTO t_TakingWork VALUES('i0008','303','2008-01-05',3850)46 INSERT INTO t_TakingWork VALUES('a0011','404','2009-02-25',3960)47 INSERT INTO t_TakingWork VALUES('n1010','505','1997-07-07',4950)48 INSERT INTO t_TakingWork VALUES('p0004','606','2010-10-24',8800)49 INSERT INTO t_TakingWork VALUES('q1009','707','2008-12-05',6600)50 INSERT INTO t_TakingWork VALUES('r0002','808','1992-02-02',7700)51 GO52 53 INSERT INTO t_Employee VALUES('a0001','aa','1993-05-08','m','城市1')54 INSERT INTO t_Employee VALUES('a1111','bb','1993-05-09','f','城市2')55 INSERT INTO t_Employee VALUES('h0007','cc','1993-05-10','m','城市8')56 INSERT INTO t_Employee VALUES('i0008','dd','1993-05-11','f','城市7')57 INSERT INTO t_Employee VALUES('n1010','ee','1993-05-12','f','城市6')58 INSERT INTO t_Employee VALUES('p0004','ff','1993-05-13','f','城市5')59 INSERT INTO t_Employee VALUES('q1009','gg','1993-05-14','f','城市4')60 INSERT INTO t_Employee VALUES('r0002','hh','1993-05-15','f','城市3')61 INSERT INTO t_Employee VALUES('t0006','ii','1993-05-16','f','城市2')62 INSERT INTO t_Employee VALUES('w0005','jj','1993-05-17','m','城市1')63 GO64 65 SELECT * FROM t_Department66 SELECT * FROM t_TakingWork67 SELECT * FROM t_Employee68 GO69 70 WITH TempRes71 AS(72 SELECT t_TakingWork.eid,t_TakingWork.did,DATEPART(YEAR,t_TakingWork.hiredate) AS hideyear73 FROM  t_TakingWork74     LEFT JOIN t_Department ON t_Department.did = t_TakingWork.did75 where t_TakingWork.hiredate < '2011-01-01 00:00:00.000'76     AND t_TakingWork.hiredate > '2007-12-31 23:59:59.000'77 )78 SELECT * FROM TempRes PIVOT(COUNT(eid)FOR hideyear IN([2008],[2009],[2010])) AS T79 GO

执行结果

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

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

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