- 浏览: 566329 次
- 性别:
- 来自: 成都
文章分类
最新评论
-
a1641693970:
还不错,学习了
BeanUtils使用总结(二)LazyDynaBean -
zjfshowtime:
很好的办法,学习了。
ORA-28001: the password has expired -
ya654277yo:
哦,多谢分享
Apache整合Tomcat后get方式提交中文乱码问题解决 -
foolkeeper:
nice !!
jvm内存参数设定 -
tracy821:
谢谢了,找了好久才找到
关于Spring 声明式事务处理时,throws exception不回滚的问题
1、 强大的group by
1 select stdname,
2 isnull(sum( case stdsubject when ' 化学 ' then Result end), 0 ) [化学],
3 isnull(sum( case stdsubject when ' 数学 ' then Result end), 0 ) [数学],
4 isnull(sum( case stdsubject when ' 物理 ' then Result end), 0 ) [物理],
5 isnull(sum( case stdsubject when ' 语文 ' then Result end), 0 ) [语文]
6 from #student
7 group by stdname
在这里,group by与sum + case结合,可以将表1中的记录(行)变成表2的字段(列)。Sum里面如果没有case,那么出来的值,只能是全部科目的总和,用了case以后,就是某科的成绩;然后这里用了好几个sum,每个科目一个sum,于是表1中本来某人某科占一条记录的“行”就变成了表2里某人一条记录,每科做一个字段了。
这种心思巧妙和对语法的熟练运用让人击节赞叹。
2、 利用select from (select from)的模式生成SQL语句
1 declare @sql varchar( 4000 )
2 set @sql = ' select stdname '
3 select @sql = @sql + ' ,isnull(sum(case stdsubject when ''' + stdsubject + ''' then Result end),0) [ ' + stdsubject + ' ] '
4 from (select distinct stdsubject from #student) as a
5 select @sql = @sql + ' from #student group by stdname '
6 print @sql
7 exec(@sql)
为了自动写上所有的科目,这里先将科目信息提炼出来:
4 from (select distinct stdsubject from #student) as a
利用之拼接生成SQL语句。当然现实中,如果#student表很大,这种做法并不妥,应该都有一个专门的科目类别表的。
3、 在临时库中提炼出字段名。临时表是真实存在的表,保存在[tempdb]中,可以利用object_id('tempdb.dbo.表名')的方式获得字段信息。
============================================
附录:
http://www.cnblogs.com/zhanglei644213943/archive/2009/12/27/1633356.html
纵览各大社区、论坛,各大 ORM框架火得不行了,如NHibernate、LINQ to SQL、ADO.NET Entity framework等,还有最近市场上出版的一本叫《领域驱动设计与模式实战》,里面也凸显了不少NHibernate在领域驱动设计中的作用与地位,也算是第一本与NHibernate相关的书籍吧!不过就NHibernate而言还是没有官方文档介绍得详细呵呵,园子里Kiler 已经把他翻译成中文版的了,收益一大片仅仅是CET-4的人。不管你是用NHibernate也好,还是用LINQ to SQL也好,用profiler一跟踪,执行的都是SQL语句,所以所SQL是根。特别是对于那些以数据为中心的应用系统,在数据库中实现复杂的存储过程,复杂的报表查询,还是直接SQL来得痛快。当然 对于那些在基于.NET的中间层应用中,它们实现面向对象的业务模型和商业逻辑的应用,NHibernate是最有用的。不管怎样,NHibernate一定可以帮助你消除或者包装那些针对特定厂商的SQL代码,并且帮你把结果集从表格式的表示形式转换到一系列的对象去(官方文档)。
有点跑题了,不再啰嗦----直接晾出压轴题。
压轴题第一问
1.把表一转换为表二
表一:
表二:
数据库代码如下:
代码
1 DROP table #student
2 CREATE TABLE #student (stdname nvarchar( 10 ),stdsubject nvarchar( 10 ),result int )
3 INSERT INTO #student VALUES ( ' 张三 ' , ' 语文 ' , 80 )
4 INSERT INTO #student values ( ' 张三 ' , ' 数学 ' , 90 )
5 INSERT INTO #student VALUES ( ' 张三 ' , ' 物理 ' , 85 )
6 INSERT INTO #student VALUES ( ' 李四 ' , ' 语文 ' , 85 )
7 INSERT INTO #student values ( ' 李四 ' , ' 数学 ' , 92 )
8 INSERT INTO #student VALUES ( ' 李四 ' , ' 物理 ' , 82 )
9 INSERT INTO #student VALUES ( ' 李四 ' , ' 化学 ' , 82 )
10 INSERT INTO #student VALUES ( ' 李四 ' , ' 化学 ' , 82 )
11 SELECT * FROM #student
可能很多老手们,一看到这题目就有了答案。当然,贴出答案来不是我的目的,我要带着SQL新手们重构到答案。用MVP李建忠老师最爱说的话就是------我不建议一上来就套用模式,而应该从重构到模式。
首先大家会想到分两组
1 select stdname,····,from #student group by stdname
然后······中间该写什么呢?
代码
1 case stdsubject when ' 化学 ' then Result end
2 case stdsubject when ' 语文 ' then Result end
3 case stdsubject when ' ··· ' then Result end
4 case stdsubject when ' ··· ' then Result end
5 case stdsubject when ' ··· ' then Result end
表二里面得0是哪里来的呢?
代码
1 isnull(sum( case stdsubject when ' 化学 ' then Result end), 0 )
2 isnull(sum( case stdsubject when ' 语文 ' then Result end), 0 )
3 isnull(sum( case stdsubject when ' ··· ' then Result end), 0 )
4 isnull(sum( case stdsubject when ' ··· ' then Result end), 0 )
5 isnull(sum( case stdsubject when ' ··· ' then Result end), 0 )
所以得出:
代码
1 select stdname,
2 isnull(sum( case stdsubject when ' 化学 ' then Result end), 0 ) [化学],
3 isnull(sum( case stdsubject when ' 数学 ' then Result end), 0 ) [数学],
4 isnull(sum( case stdsubject when ' 物理 ' then Result end), 0 ) [物理],
5 isnull(sum( case stdsubject when ' 语文 ' then Result end), 0 ) [语文]
6 from #student
7 group by stdname
然后得出答案:
代码
1 declare @sql varchar( 4000 )
2 set @sql = ' select stdname '
3 select @sql = @sql + ' ,isnull(sum(case stdsubject when ''' + stdsubject + ''' then Result end),0) [ ' + stdsubject + ' ] '
4 from (select distinct stdsubject from #student) as a
5 select @sql = @sql + ' from #student group by stdname '
6 print @sql
7 exec(@sql)
8
压轴题第二问:把表二转化为表一
表一:
表二:
数据库代码如下:
代码
1 DROP table #student2
2 CREATE TABLE #student2 (stdname nvarchar( 10 ),化学 int ,数学 int ,物理 int ,语文 int )
3 INSERT INTO #student2 VALUES ( ' 李四 ' , 164 , 92 , 82 , 85 )
4 INSERT INTO #student2 VALUES ( ' 张三 ' , 0 , 90 , 85 , 80 )
5 SELECT * FROM #student2
看到这题,直接想到:
代码
1 SELECT ' 李四 ' as stdname,stdname = ' 化学 ' , 化学 as result from #student2 where stdname = ' 李四 '
2 union all
3 SELECT ' 李四 ' as stdname,stdname = ' 数学 ' , 数学 as result from #student2 where stdname = ' 李四 '
4 union all
5 SELECT ' 李四 ' as stdname,stdname = ' 物理 ' , 物理 as result from #student2 where stdname = ' 李四 '
6 union all
7 SELECT ' 李四 ' as stdname,stdname = ' 语文 ' , 语文 as result from #student2 where stdname = ' 李四 '
8 union all
9 SELECT ' 张三 ' as stdname,stdname = ' 化学 ' , 化学 as result from #student2 where stdname = ' 张三 '
10 union all
11 SELECT ' 张三 ' as stdname,stdname = ' 数学 ' , 数学 as result from #student2 where stdname = ' 张三 '
12 union all
13 SELECT ' 张三 ' as stdname,stdname = ' 物理 ' , 物理 as result from #student2 where stdname = ' 张三 '
14 union all
15 SELECT ' 张三 ' as stdname,stdname = ' 语文 ' , 语文 as result from #student2 where stdname = ' 张三 '
重构到:
代码
1 declare @sql2 varchar( 4000 )
2 set @sql2 = ''
3 SELECT @sql2 = @sql2 +
4 ' SELECT ''' + stdname + ''' as stdname,stdname= '' 化学 '' , 化学 as result from #student2 where stdname= ''' + stdname + '''
5 union all
6 SELECT ''' +stdname+ ''' as stdname,stdname = '' 数学 '' , 数学 as result from #student2 where stdname = ''' +stdname+ '''
7 union all
8 SELECT ''' +stdname+ ''' as stdname,stdname = '' 物理 '' , 物理 as result from #student2 where stdname = ''' +stdname+ '''
9 union all
10 SELECT ''' +stdname+ ''' as stdname,stdname = '' 语文 '' , 语文 as result from #student2 where stdname = ''' +stdname+ ''' union all '
11 from (SELECT stdname FROM #student2) as a
12 SELECT @sql2 = LEFT(@sql2,LEN(@sql2) - 10 )
13 PRINT(@sql2)
14 exec(@sql2)
如果要求不能出现 化学 数学 物理 语文 这样的关键字,那么可以这样写:
代码
1 select [name] into #tmpCloumns
2 from tempdb.dbo.syscolumns
3 where id = object_id( ' tempdb.dbo.#student2 ' )
4 and [name] <> ' stdname '
5 select * from #tmpCloumns
6
7 declare @strSql nvarchar( 800 )
8 select @strSql = ''
9 select @strSql = @strSql + ' union all ' + char ( 10 ) + char ( 13 ) +
10 ' select [stdname], ''' + [name] + ''' as [科目],[ ' + [name] + ' ] ' + char ( 10 ) + char ( 13 ) +
11 ' from [#student2] ' + char ( 10 ) + char ( 13 )
12 from #tmpCloumns
13
14 select @strSql = substring(@strSql, 11 ,len(@strSql)) + ' order by stdname,[科目] '
15 -- print @strSql
16 exec(@strsql)
这种题目,在各种笔试中出现的概率还是非常大的,大家不用死记。以前有的朋友看着复杂的报表查询,几百行SQL,望而生畏,然后说:"这是哪个SQL超人写的啊!"其实,谁一上来不可能写出那么长的SQL,也是慢慢重构--调试--重构-······
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/leftfist/archive/2009/12/29/5097307.aspx
发表评论
-
Oracle数据库的锁类型(转)
2014-05-08 14:05 1085Oracle数据库的锁类型 o ... -
AWR报告手动生成
2014-04-12 12:23 952sqlplus / as sysdba SQL> ... -
PowerDesigner脚本生成
2013-11-23 17:04 8161 PowerDesigner中批量根据对象的name生 ... -
EXP-00011:oracle11g 空表不能exp导出的问题
2013-05-23 10:51 1356环境:Oracle11gR2+linux RH 5.5 最 ... -
新建Oracle数据库2种方法
2013-03-14 15:53 1078新建Oracle数据库2种方法:1.通过运行Oracle ... -
(转)查看执行计划
2012-09-29 11:00 1421Oracle10g获取sql语句的执 ... -
(转)ORACLE 数据库(查询--主键外键约束)
2012-05-15 15:50 2066-- 查询外键约束(查某表的所有父表) sel ... -
(转)Redhat Linux安装Oracle 11g R2数据库
2012-04-04 22:17 1693Oracle目前最新的版本是11g R2版本,网上不少安装介绍 ... -
ORA-28001: the password has expired
2012-02-23 13:47 9428大早上正式库提示: Oracle提示错误消息ORA-28 ... -
linux自动备份数据库
2012-02-13 18:08 986#!/bin/sh export PATH=$PATH:$H ... -
Oracle中使用sys_connect_by_path函数实现行转列
2012-01-09 20:16 1329select category ,MAX(s ... -
Oracle强杀进程,解决表锁死等问题
2011-10-17 17:49 11491、找到sid,serial#; SELECT /*+ ... -
Oracle常用技巧(转)
2011-10-11 14:39 9871.删除表空间 DROP TABLESPACE ... -
查询oracle表的信息(表,字段,约束,索引)
2011-08-09 12:22 10871、查询出所有的用户表 select * fro ... -
Oracle index
2011-08-09 12:19 1100索引: 1、一般索引: create inde ... -
(转)oracle执行计划
2011-07-20 19:23 1056一.相关的概念 ... -
ORA-01658 表空間大小不足
2011-02-22 12:24 1295解決方法: 1.查詢各個表空間的利用率 sele ... -
(转)ORACLE数据导入导出
2011-02-19 15:45 871Oracle数据导入导出imp/exp就相当于oracle数据 ... -
Oracle 10g 安装之网络适配器要求 Microsoft Loopback Adapter (环回适配器)
2011-02-19 15:36 4627安装10g的时候,遇到了 ... -
【ORA-12560: TNS: 协议适配器错误】解决方案
2011-02-19 14:42 1325ORA-12560: TNS: 协议适配器错误的问题的原因有三 ...
相关推荐
Sql语句实现表的行列转换,行转列,列转行
1.创建数据库表及插入数据 2.子查询统计不同性质的学生总数 3.一行数据转换成两列数据 union all 4.表行列数据转换(表转置) 1.创建数据库表及插
oracle行列转换示例,查询转换,有创表过程,数据,清晰明了
使用交叉表查询,显示源于表中某个字段的汇总值,并将它们分组,其中一组列在数据表的左侧,另一组列在数据表的上部。行和列的交叉处可以对数据进行多种汇总计算,如:求和、平均值、记数、最大值、最小值等。使用...
--2、普通行列转换 --3、字符串相关操作 --3.1提取字符串中不同类型字符 --提取数字 --提取英文 --提取中文 --3.2 从字符串中取固定数量的字段 --3.3 关于字符串拆分 --3.3.3.游标循环法: --SQL2000/2005字符串拆分...
Oracle数据库交叉查询经典 交叉表、行列转换和交叉查询经典
最近突然玩起了sql语句,想着想着便给自己出了一道题目:“行列转换”。起初瞎折腾了不少时间也上网参考了一些博文,不过大多数是采用oracle数据库当中的一些便捷函数进行处理,比如”pivot”。那么,在Mysql环境下...
Landsat行列号与行政区、经纬度对照表,含图片,有文字叙述,简单易懂
主要介绍了sql server通过pivot对数据进行行列转换的方法,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下
Linq动态查询、行转列;可用于多功能的动态组合查询实现,后头返回组装的表头和数据集;实现行转列,动态linq组装
MYSQL子查询、相关子查询、inner join、left|right join、cross join 、行列转换等高级查询练习题
4.3 行列转换 第4章 表空间、数据库对象 1.同义词 2.序列 3.视图 4.索引 4.1 索引介绍 4.2 索引种类 4.3 索引优缺点 4.4 索引失效 5.表空间 6.表分区 6.1表空间及分区表的概念 6.2表分区的具体作用 ...
Oracle中使用语句将行数据转换称不同的列表示,或者将不同的列数据写到同一列的不同行上的行列转换问题是一个非常传统的话题。 网络上流传了很多将行数据转换称列数据的方法和应用实例,一般通过decode或者case函数...
查询后的结果可以行列转换。保存参数后打开窗口即可得到查询结果,方便有规律的查询。 7)编辑功能。新增数据、对查询结果可进行修改、删除。 8)打印功能。一次的查询结果可以打印多个水晶报表。 9)设置功能。编辑...
字段3[,字段4] 行列转置,分号前为保留在行的字段,分号后为转置成列的字段 GROUP 字段[,字段2][;[字段名=]统计表达式/[:]格式化表达式][;表达式2] 分组统计,例:--group id;AvgPrice=avg(child.price) SORT 排序...
--17.2 SQL 2000 动态 --17.3 有关sql 2005的动静态写法参见我的文章《普通行列转换(version 2.0)》或《普通行列转换(version 3.0)》。--18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name...
数据的行列转换 基于变量列表的查询 基于范围的聚合 一般规则,最后使用 查询与列表中多个项目相符的记录 最佳匹配查询 优化器指令 12 明察秋毫:监控性能 数据库速度缓慢 服务器负载因素 何谓“性能优良” 从业务...
【公农双历查询】【高级定位】【选区背景着色】【修改文件建立时间】【工作表环境设置】【按颜色合计】【根据工资计算钞票】【隔行插入行】【折分工作簿(工作表)】【工作表折分】【合并工作簿】【文本与数值互换】...
4. **数据分离**:通过行列转换、填充序列、排序等操作,能将一个汇总的工资表拆分成各个独立的员工工资条,便于打印或单独发送给每位员工。 5. **更新同步**:如果使用了类似Power Query的功能,当原始工资数据...
03046显示、隐藏行列标题 04001通过Range属性引用某个单元格 04002通过Range属性引用连续的单元格区域 04003通过Range属性引用不连续的单元格区域 04004通过字符串的方式引用某个单元格 04005通过字符串的方式引用...