=================================版权声明=================================
版权声明：原创文章 谢绝转载
请通过右侧公告中的“联系邮箱（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 GO 10 IF OBJECT_ID (N't_Employee', N'U') IS NOT NULL 11 DROP TABLE t_Employee; 12 GO 13 14 CREATE TABLE t_Department 15 (did NVARCHAR(3) PRIMARY KEY, 16 dname NVARCHAR(5), 17 dcity NVARCHAR(10)) 18 GO 19 CREATE TABLE t_TakingWork 20 (eid NVARCHAR(5) PRIMARY KEY, 21 did NVARCHAR(3), 22 hiredate DATETIME, 23 salary INTEGER) 24 GO 25 CREATE TABLE t_Employee 26 (eid NVARCHAR(5) PRIMARY KEY, 27 ename NVARCHAR(20), 28 hiredate DATETIME, 29 gender nCHAR(1), 30 city NVARCHAR(10)) 31 GO 32 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 GO 42 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 GO 52 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 GO 64 65 SELECT * FROM t_Department 66 SELECT * FROM t_TakingWork 67 SELECT * FROM t_Employee 68 GO 69 70 WITH TempRes 71 AS( 72 SELECT t_TakingWork.eid,t_TakingWork.did,DATEPART(YEAR,t_TakingWork.hiredate) AS hideyear 73 FROM t_TakingWork 74 LEFT JOIN t_Department ON t_Department.did = t_TakingWork.did 75 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 T 79 GO
执行结果
附上执行计划
=======================================================================
细节效率什么的放过我吧。
非专业SQL，不求高效，但求能跑。
