=================================版权声明=================================
版权声明：原创文章 谢绝转载
请通过右侧公告中的“联系邮箱（[email protected]）”联系我
勿用于学术性引用。
勿用于商业出版、商业印刷、商业引用以及其他商业用途。
本文不定期修正完善。
本文链接：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，不求高效，但求能跑。
- 2楼清海扬波
- 不求高效，但求能跑。
- Re: 王林森
- @清海扬波，毕竟不是DBA
- 1楼xling
- 用分析函数, 没有你这些乱七八糟的东西.