两道例子
来源:互联网 发布:天刀贴吧最美捏脸数据 编辑:程序博客网 时间:2024/05/12 19:12
SQL Server2005引入了很多迎合开发者口味的新特性,虽然改动不大,却大大了减少了开发者的工作量,这种替用户考虑的开发思路,值得称赞。
在SQLServer2000中,要实现行列转换,需要综合利用聚合函数和动态SQL,实现起来需要一定的技巧,所以在CSDN的SQL讨论区里可以看到大量询问行列转换如何实现的问题。到了2005中,使用新引进的关键字PIVOT/UNPIVOT,可以轻松实现行列转换的需求。
好像Oracle11g也准备引入PIVOT/UNPIVOT特性,对于Oracle开发来说,It''s a good news。
本文通过两个简单的例子展示PIVOT/UNPIVOT的用法。详细的语法请参考联机帮助。
PIVOT
创建测试表,插入测试数据
create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,''a'',1,1000)
insert into test values(1,''a'',2,2000)
insert into test values(1,''a'',3,4000)
insert into test values(1,''a'',4,5000)
insert into test values(2,''b'',1,3000)
insert into test values(2,''b'',2,3500)
insert into test values(2,''b'',3,4200)
insert into test values(2,''b'',4,5500)
select * from test
id name quarter profile
----------- -------------------- ----------- -----------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500
(8 row(s) affected)
利用PIVOT将个季度的利润转成横向显示:
select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt
id name 一季度 二季度 三季度 四季度
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
(2 row(s) affected)
UNPIVOT
建立测试表,插入测试数据
drop table test
create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test values(1,''a'',1000,2000,4000,5000)
insert into test values(2,''b'',3000,3500,4200,5500)
select * from test
id name Q1 Q2 Q3 Q4
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
(2 row(s) affected)
利用UNPIVOT,将同一行中四个季度的列数据转换成四行数据:
select id,name,quarter,profile
from
test
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
id name quarter profile
----------- -------------------- ---------- -----------
1 a Q1 1000
1 a Q2 2000
1 a Q3 4000
1 a Q4 5000
2 b Q1 3000
2 b Q2 3500
2 b Q3 4200
2 b Q4 5500
(8 row(s) affected)
------------------------------
第一是.关于加班时间的问题
公司规定一周内最多只能加班和12个小时,多余的要去掉,去掉的规则是:某天加班超过1H的才能去,而且是依次大致平均的去,(最小单位是1小时)
举例:
日期 工号 加班小时
周一 A001 6
周二 A001 2
周三 A001 1
周四 A001 5
周五 A001 7
----------------
共计加班:21H,超出9H
从周一开始依次删减,每天减1H(如果当天的加班时间>1H),一轮后如果还不够,再继续下一轮,直到刚好为12H为止(只对超过12H的而言)
最后的结果应该是:
日期 工号 加班小时
周一 A001 3 --减了3次
周二 A001 1 --减了1次
周三 A001 1 --减了0次
周四 A001 2 --减了3次
周五 A001 5 --减了2次
-----------------------
以上只列具体某一个人的,公司有好几千人,
用SQL语句实现,考虑到效率问题规定:
1.不能用游标
2.不能用循环
其它方法都可以
DECLARE @T TABLE (日期 VARCHAR(4),工号 VARCHAR(4),加班小时 INT)
INSERT INTO @T
SELECT '周一','A001',6 UNION ALL
SELECT '周二','A001',2 UNION ALL
SELECT '周三','A001',1 UNION ALL
SELECT '周四','A001',5 UNION ALL
SELECT '周五','A001',7
--SQL查询如下:
;WITH LiangLoveLan AS
(
SELECT *
FROM (
SELECT 日期,工号,加班小时 FROM @T
UNION ALL
SELECT '总数',工号,SUM(加班小时) FROM @T GROUP BY 工号
) AS T
PIVOT(MAX(加班小时) FOR 日期 IN([周一],[周二],[周三],[周四],[周五],[总数])) AS pvt
),
LiangLoveLan2 AS
(
SELECT *,number = 1
FROM LiangLoveLan
UNION ALL
SELECT
工号,
周一=CASE WHEN 周一>1 AND number = 1
THEN 周一-1
ELSE 周一 END,
周二=CASE WHEN 周二>1 AND number = 2
THEN 周二-1
ELSE 周二 END,
周三=CASE WHEN 周三>1 AND number = 3
THEN 周三-1
ELSE 周三 END,
周四=CASE WHEN 周四>1 AND number = 4
THEN 周四-1
ELSE 周四 END,
周五=CASE WHEN 周五>1 AND number = 5
THEN 周五-1
ELSE 周五 END,
总数=CASE WHEN number=1 AND 周一>1 THEN 总数-1
WHEN number=2 AND 周二>1 THEN 总数-1
WHEN number=3 AND 周三>1 THEN 总数-1
WHEN number=4 AND 周四>1 THEN 总数-1
WHEN number=5 AND 周五>1 THEN 总数-1
ELSE 总数
END,
number=CASE WHEN number=1 THEN 2
WHEN number=2 THEN 3
WHEN number=3 THEN 4
WHEN number=4 THEN 5
WHEN number=5 THEN 1
END
FROM LiangLoveLan2
WHERE 总数>12
)
SELECT
CAST(日期 AS VARCHAR(20)) AS 日期,
工号,
加班小时
FROM (
SELECT 工号,周一,周二,周三,周四,周五
FROM LiangLoveLan2
WHERE 总数=12
) AS LiangLoveLan3
UNPIVOT(加班小时 FOR 日期 IN([周一],[周二],[周三],[周四],[周五])) AS unpvt
ORDER BY 工号;
- 两道例子
- JS保留两位小数例子
- alv 两种格式原始的例子
- 关于PV操作(含两例子)
- ListView之两种实现 简单例子
- jquery文件上传例子(两种方式)
- sapui5 oData例子代码两则
- 一个功能齐全的DataGrid分页例子(两种方法)
- 一个功能齐全的DataGrid分页例子(两种方法)
- 功能齐全的DataGrid分页例子(两种方法)
- 两主机互ping的一些例子及其解释
- 有关GUI建立两图标之间连线的例子
- 使用GUI建立两图标之间连线的例子
- 两种利用内存流存取图片的经典例子
- Oracle 外连接两种写法的一个例子
- 两种实现Toast 的例子(图片&文字)
- js 的两种面向对象编程【两个例子】
- 两种实现Toast 的例子(图片&文字)转载
- 接上
- 计算机网络
- 好人缘
- Drawing with Alpha:使用alpha通道来绘图
- 改变td背景
- 两道例子
- 地球真的很危险,我们却没有火星可回
- 一张能分出你是左脑还是右脑的经典图片
- 使用低权限 Oracle 数据库账户得到 OS 访问权限
- 一位程序员的忠告
- 实用的pdf在线阅读和处理工具
- 努力
- 今天
- From China Daily 13/6