- 浏览: 1332604 次
- 性别:
- 来自: 成都
文章分类
- 全部博客 (471)
- 原创文章 (4)
- Database (84)
- J2SE (63)
- Web (26)
- Javascript (30)
- Lucene (11)
- os (13)
- 算法 (8)
- Webservice (1)
- Open projects (18)
- Hibernate (18)
- Spring (15)
- Css (2)
- J2ee (2)
- 综合技术 (18)
- 安全管理 (13)
- PatternsInJava (27)
- NIO (5)
- Ibatis (2)
- 书籍收藏 (1)
- quartz (7)
- 并发编程 (15)
- oracle问题 (2)
- ios (60)
- coco2d-iphone (3)
- C++ (6)
- Zookeeper (2)
- golang (4)
- animation (2)
- android (1)
最新评论
-
dandingge123:
【引用】限制UITextField输入长度的方法 -
qja:
...
对List顺序,逆序,随机排列实例代码 -
安静听歌:
现在在搞这个,,,,,哎~头都大了,,,又freemarker ...
通用大型网站页面静态化解决方案(一) -
springdata-jpa:
java quartz定时任务demo教程源代码下载,地址:h ...
Quartz 配置参考 -
马清天:
[b][/b][list][*]引用[u][/u][/list ...
通用大型网站页面静态化解决方案(一)
我们首先先通过一个老生常谈的例子,学生成绩表(下面简化了些)来形象了解下行转列
(
[ UserName ] NVARCHAR ( 20 ), -- 学生姓名
[ Subject ] NVARCHAR ( 30 ), -- 科目
[ Score ] FLOAT , -- 成绩
)
INSERT INTO [ StudentScores ] SELECT ' Nick ' , ' 语文 ' , 80
INSERT INTO [ StudentScores ] SELECT ' Nick ' , ' 数学 ' , 90
INSERT INTO [ StudentScores ] SELECT ' Nick ' , ' 英语 ' , 70
INSERT INTO [ StudentScores ] SELECT ' Nick ' , ' 生物 ' , 85
INSERT INTO [ StudentScores ] SELECT ' Kent ' , ' 语文 ' , 80
INSERT INTO [ StudentScores ] SELECT ' Kent ' , ' 数学 ' , 90
INSERT INTO [ StudentScores ] SELECT ' Kent ' , ' 英语 ' , 70
INSERT INTO [ StudentScores ] SELECT ' Kent ' , ' 生物 ' , 85
如果我想知道每位学生的每科成绩,而且每个学生的全部成绩排成一行,这样方便我查看、统计,导出数据
UserName,
MAX ( CASE Subject WHEN ' 语文 ' THEN Score ELSE 0 END ) AS ' 语文 ' ,
MAX ( CASE Subject WHEN ' 数学 ' THEN Score ELSE 0 END ) AS ' 数学 ' ,
MAX ( CASE Subject WHEN ' 英语 ' THEN Score ELSE 0 END ) AS ' 英语 ' ,
MAX ( CASE Subject WHEN ' 生物 ' THEN Score ELSE 0 END ) AS ' 生物 '
FROM dbo. [ StudentScores ]
GROUP BY UserName
查询结果如图所示,这样我们就能很清楚的了解每位学生所有的成绩了
接下来我们来看看第二个小列子。有一个游戏玩家充值表(仅仅为了说明,举的一个小例子),
代码
(
[ ID ] INT IDENTITY ( 1 , 1 ),
[ UserName ] NVARCHAR ( 20 ), -- 游戏玩家
[ CreateTime ] DATETIME , -- 充值时间
[ PayType ] NVARCHAR ( 20 ), -- 充值类型
[ Money ] DECIMAL , -- 充值金额
[ IsSuccess ] BIT , -- 是否成功 1表示成功, 0表示失败
CONSTRAINT [ PK_Inpours_ID ] PRIMARY KEY (ID)
)
INSERT INTO Inpours SELECT ' 张三 ' , ' 2010-05-01 ' , ' 支付宝 ' , 50 , 1
INSERT INTO Inpours SELECT ' 张三 ' , ' 2010-06-14 ' , ' 支付宝 ' , 50 , 1
INSERT INTO Inpours SELECT ' 张三 ' , ' 2010-06-14 ' , ' 手机短信 ' , 100 , 1
INSERT INTO Inpours SELECT ' 李四 ' , ' 2010-06-14 ' , ' 手机短信 ' , 100 , 1
INSERT INTO Inpours SELECT ' 李四 ' , ' 2010-07-14 ' , ' 支付宝 ' , 100 , 1
INSERT INTO Inpours SELECT ' 王五 ' , ' 2010-07-14 ' , ' 工商银行卡 ' , 100 , 1
INSERT INTO Inpours SELECT ' 赵六 ' , ' 2010-07-14 ' , ' 建设银行卡 ' , 100 , 1
CASE PayType WHEN ' 支付宝 ' THEN SUM ( Money ) ELSE 0 END AS ' 支付宝 ' ,
CASE PayType WHEN ' 手机短信 ' THEN SUM ( Money ) ELSE 0 END AS ' 手机短信 ' ,
CASE PayType WHEN ' 工商银行卡 ' THEN SUM ( Money ) ELSE 0 END AS ' 工商银行卡 ' ,
CASE PayType WHEN ' 建设银行卡 ' THEN SUM ( Money ) ELSE 0 END AS ' 建设银行卡 '
FROM Inpours
GROUP BY CreateTime, PayType
如图所示,我们这样只是得到了这样的输出结果,还需进一步处理,才能得到想要的结果
SELECT
CreateTime,
ISNULL ( SUM ( [ 支付宝 ] ), 0 ) AS [ 支付宝 ] ,
ISNULL ( SUM ( [ 手机短信 ] ), 0 ) AS [ 手机短信 ] ,
ISNULL ( SUM ( [ 工商银行卡 ] ), 0 ) AS [ 工商银行卡 ] ,
ISNULL ( SUM ( [ 建设银行卡 ] ), 0 ) AS [ 建设银行卡 ]
FROM
(
SELECT CONVERT ( VARCHAR ( 10 ), CreateTime, 120 ) AS CreateTime,
CASE PayType WHEN ' 支付宝 ' THEN SUM ( Money ) ELSE 0 END AS ' 支付宝 ' ,
CASE PayType WHEN ' 手机短信 ' THEN SUM ( Money ) ELSE 0 END AS ' 手机短信 ' ,
CASE PayType WHEN ' 工商银行卡 ' THEN SUM ( Money ) ELSE 0 END AS ' 工商银行卡 ' ,
CASE PayType WHEN ' 建设银行卡 ' THEN SUM ( Money ) ELSE 0 END AS ' 建设银行卡 '
FROM Inpours
GROUP BY CreateTime, PayType
) T
GROUP BY CreateTime
其 实行转列,关键是要理清逻辑,而且对分组(Group by)概念比较清晰。上面两个列子基本上就是行转列的类型了。但是有个问题来了,上面是我为了说明弄的一个简单列子。实际中,可能支付方式特别多,而且逻 辑也复杂很多,可能涉及汇率、手续费等等(曾经做个这样一个),如果支付方式特别多,我们的CASE WHEN 会弄出一大堆,确实比较恼火,而且新增一种支付方式,我们还得修改脚本如果把上面的脚本用动态SQL改写一下,我们就能轻松解决这个问题
DECLARE @tmpSql VARCHAR ( 8000 );
SET @cmdText = ' SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime, ' + CHAR ( 10 );
SELECT @cmdText = @cmdText + ' CASE PayType WHEN ''' + PayType + ''' THEN SUM(Money) ELSE 0 END AS ''' + PayType
+ ''' , ' + CHAR ( 10 ) FROM ( SELECT DISTINCT PayType FROM Inpours ) T
SET @cmdText = LEFT ( @cmdText , LEN ( @cmdText ) - 2 ) -- 注意这里,如果没有加CHAR(10) 则用LEFT(@cmdText, LEN(@cmdText) -1)
SET @cmdText = @cmdText + ' FROM Inpours GROUP BY CreateTime, PayType ' ;
SET @tmpSql = ' SELECT CreateTime, ' + CHAR ( 10 );
SELECT @tmpSql = @tmpSql + ' ISNULL(SUM( ' + PayType + ' ), 0) AS ''' + PayType + ''' , ' + CHAR ( 10 )
FROM ( SELECT DISTINCT PayType FROM Inpours ) T
SET @tmpSql = LEFT ( @tmpSql , LEN ( @tmpSql ) - 2 ) + ' FROM ( ' + CHAR ( 10 );
SET @cmdText = @tmpSql + @cmdText + ' ) T GROUP BY CreateTime ' ;
PRINT @cmdText
EXECUTE ( @cmdText );
下面是通过PIVOT来进行行转列的用法,大家可以对比一下,确实要简单、更具可读性(呵呵,习惯的前提下)
CreateTime, [ 支付宝 ] , [ 手机短信 ] ,
[ 工商银行卡 ] , [ 建设银行卡 ]
FROM
(
SELECT CONVERT ( VARCHAR ( 10 ), CreateTime, 120 ) AS CreateTime,PayType, Money
FROM Inpours
) P
PIVOT (
SUM ( Money )
FOR PayType IN
( [ 支付宝 ] , [ 手机短信 ] , [ 工商银行卡 ] , [ 建设银行卡 ] )
) AS T
ORDER BY CreateTime
有时可能会出现这样的错误:
消息 325,级别 15,状态 1,第 9 行
'PIVOT' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值,以启用此功能。有关存储过程 sp_dbcmptlevel 的信息,请参见帮助。
这个是因为:对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时,必须将数据库的兼容级别设置为 90 或更高。有关如何设置数据库兼容级别的信息,请参阅 sp_dbcmptlevel (Transact-SQL)。 例如,只需在执行上面脚本前加上 EXEC sp_dbcmptlevel Test, 90; 就OK了, Test 是所在数据库的名称。
下面我们来看看列转行,主要是通过UNION ALL ,MAX来实现。假如有下面这么一个表
(
ProgrectName NVARCHAR ( 20 ), -- 工程名称
OverseaSupply INT , -- 海外供应商供给数量
NativeSupply INT , -- 国内供应商供给数量
SouthSupply INT , -- 南方供应商供给数量
NorthSupply INT -- 北方供应商供给数量
)
INSERT INTO ProgrectDetail
SELECT ' A ' , 100 , 200 , 50 , 50
UNION ALL
SELECT ' B ' , 200 , 300 , 150 , 150
UNION ALL
SELECT ' C ' , 159 , 400 , 20 , 320
UNION ALL
SELECT ' D ' , 250 , 30 , 15 , 15
我们可以通过下面的脚本来实现,查询结果如下图所示
发表评论
-
mysql主从热备配置(含innodb)终极版
2012-12-25 13:10 2648转自 http://blogread.cn/it/articl ... -
sqlite3中的数据类型
2012-12-10 21:37 1333(转)http://www.cnblogs.com/kfqco ... -
Mac下MySql卸载方法
2012-09-10 23:57 1054Mac下MySql卸载方法 mac下mysql的D ... -
mac os x mysql数据库utf8配置
2012-09-10 23:29 2282进入mysql console: 输入 mysql& ... -
ON DUPLICATE KEY UPDATE
2012-08-07 01:47 1091(转自)http://blog.csdn.net/kesaih ... -
MySQL单列索引和组合索引的区别介绍
2012-08-07 01:31 1106(转自)http://blog.cs ... -
centos下MySQL主从同步配置
2012-08-03 13:14 1558(转自)http://apps.hi.baidu.com ... -
sql语句中left join、inner join中的on与where的区别
2012-06-13 13:24 1263table a(id, type): id t ... -
分组取前N记录
2012-05-31 16:24 1125(转)http://blog.csdn.net/ac ... -
如何一条sql语句取出分组数据中值最大的
2012-05-30 23:59 2363--按某一字段分组取最大(小)值所在行的数据(2007-10- ... -
存储过程与函数的区别
2011-09-28 19:35 1158... -
RMAN备份方案
2011-07-21 10:29 2060RMAN备份方案 RMAN也可以进行联机备份,而且备份与恢 ... -
oracle
2011-04-06 17:46 1049学习笔记 -
PLSQL Developer + Oracle客户端简易安装方法
2011-03-17 00:10 14381Oracle 10g绿色客户端 pl ... -
SELECT INTO FROM与INSERT INTO SELECT
2011-01-26 15:52 34101.INSERT INTO SELECT语句 ... -
浅谈unique列上插入重复值的MySQL解决方案
2011-01-25 11:33 1781本文的unique列上插入重复值解决方案,主要基于MySQL平 ... -
:Oracle 中对Merge语句的增强 和去重复新增
2011-01-25 11:11 2321在Oracle 10g之前,merge语句支持匹配更新和不匹配 ... -
单行函数(仅对单个表达式或一行进行处理)
2011-01-23 18:49 1272例如:select sum(qty) from sales ... -
oracle中的事务控制命令
2011-01-23 18:45 2104事务提交有显示提交:使用commit命令来提交所有未提交的更新 ... -
oracle全文检索
2011-01-23 18:18 61551.全文检索和普通检索的区别 不使用Oracle text功 ...
相关推荐
虽然SQL注入是一种老生常谈的安全漏洞,但本案例展示了一种相对特殊的攻击方法——通过bit by bit的方式来逐步渗透数据库。这种攻击方式需要细致的规划和执行,但一旦成功,可以获得丰富的数据库信息甚至对服务器的...
行列转换是一个老生常谈的问题,这几天逛知乎有遇到了这个问题。一个前端说,拿到的数据是单列的需要做转换才能够绑定,折腾了好久才搞定,还说这个应该后端直接出数据,不应该让前端折腾。 这个嘛,行列转换在后端...
文档(documents)是集合中的基本单元,相当于表格中的行,文档内字段可以自由组合,无需固定模式。 3. **无表结构限制**:与SQL数据库不同,MongoDB不需预定义表结构,这意味着在使用集合前不需要初始化,对于快速...
在Android开发中,事件传递和处理机制是相当关键的一个部分,尤其对于用户界面的交互有着决定性的影响。本文主要探讨了Android系统如何处理触摸事件,通过拟人化的比喻来解释事件传递的流程,并通过实际代码示例进行...
去年到现在就一直有人希望我出一篇关于waf绕过的文章,我觉得这种老生常谈的话题也没什么可写的。很多人一遇到waf就发懵,不知如何是好,能搜到的各种姿势也是然并卵。但是积累姿势的过程也是迭代的,那么就有了此文...
C#导出数据到EXCEL表格是个老生常谈的问题了,写这篇文章主要是给和我一样的新手朋友提供两种导出EXCEL的方法并探讨一下导出的效率问题,本文中的代码直接就可用,其中部分代码参考其他的代码并做了修改,抛砖引玉,...
在SQLServer中使用存储过程实现分页的已经有很多方法了。之前在面试中遇到过这一问题,问如何高效实现数据库分页。刚好上周在业务中也遇到了这个需求,所以在这里简单记录和分享一下。这里以SQLServer的示例数据库...
转换为字符串是JavaScript中常见的操作,无论是数字、布尔值还是字符串本身,都有`toString()`方法用于将其转换为字符串形式。例如,数字10调用`toString()`方法后会变成字符串"10",布尔值`true`会变成字符串"true...
正如标题所言,《电子技术的老生常谈——接地》一文中提到的那样,尽管接地的基本概念在每一次培训和交流中都会被提及,但往往缺乏一个通用而全面的方法论指导。本文旨在深入探讨接地的各种类型、目的以及具体的实施...
数据库分页是老生常谈的问题了。如果使用ORM框架,再使用LINQ的话,一个Skip和Take可以搞定。但是有时由于限制,需要使用存储过程来实现。在SQLServer中使用存储过程实现分页的已经有很多方法了。之前在面试中遇到过...
在编程中,数据结构的使用对于程序的性能和开发效率具有重要影响。DS扩展通过引入一系列接口和类来丰富PHP的数据结构类型,使得开发者能够更方便地处理数据。 首先,DS扩展定义了几个基本接口,用来规范数据结构的...
ProgressBar和ProgressDialog是Android开发中常见的两种进度条控件,用于展示任务执行的进度或等待状态。下面我们将深入探讨这两种控件的用法。 首先,ProgressBar是一个可以显示具体进度的组件,它可以是圆形或...
老生常谈外链 站长要做到对症下药.pps
在进行JavaScript编程的过程中,面向对象思想一直是一个核心的概念。面向对象编程(OOP)是通过创建对象来模拟现实世界的一种编程范式。在JavaScript中,对象可以通过不同的方法创建和定义。面向对象有三大基本特性...
CSS中的`float`属性是一个非常基础且重要的布局工具,它被广泛用于创建多列布局、对齐元素以及处理内容环绕等场景。本文将深入探讨`float`的用法及其在实际设计中的应用。 首先,`float`属性允许元素在当前行内浮动...
在JavaScript中,布尔值(Boolean)`true`和`false`是逻辑运算的基本元素,它们在条件语句、三元运算符以及逻辑运算符中扮演重要角色。然而,JavaScript的数据类型转换规则有时会让初学者感到困惑,特别是当涉及到非...
本文将详细讲解ListView的`onItemClick`方法中的各个参数及其应用,以及如何在点击事件中获取当前选中行的数据,以便调用相应的服务接口。 `OnItemClickListener`是一个接口,包含一个方法`onItemClick(AdapterView...