- 浏览: 276063 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
java老大爷:
技术扶持 技术交流 汇聚程序员精英 群里有马士兵2017 ...
大数据数据 -
yeruowei:
遇到同样的问题,特意登录点赞
导入数据库时出现ORA-01435: 用户不存在 -
1017974387:
特意登陆来给你赞。。。。。。
导入数据库时出现ORA-01435: 用户不存在 -
sgy1103:
您好:看了苯苯熊家庭记帐注册码破解很羡慕。我在用,不过换电脑后 ...
笨笨家庭记账本—破解 -
shellbye:
居然真是这个原因。。。哈哈
maven设置中ERROR: JAVA_HOME is set to an invalid directory
SQL经典模式--列转行
一般需要将列转成行来使用,一定是原有的Schema设计没有考虑周全。但是没有办法,为了保护现有的投资,不得不在糟糕的设计上周旋,用最小的代价去实现新需求。
毕竟认识都是由浅入深,为不健全的Schema设计付出代价,就像交税一样,无可避免。
举例:
课程表: 每门课程由5位老师教,要求包含老师的信息,以及一些课程的信息
create table cource (id int, name varchar(100), teacher1 int,teacher2 int,teacher3 int, teacher4 int, teacher5 int);
insert into cource values (1,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (2,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (3,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (4,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (5,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (6,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (7,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (8,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (9,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (10,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (11,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (12,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
老师表: 记录了每个老师的年龄,级别,性别
create table teacher(id int, age int, level int, gender int);
insert into teacher values (1, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (2, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (3, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (4, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (5, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (6, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (7, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (8, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (9, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (10, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (11, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (12, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (13, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (14, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
需求:
找出一些课程, 这些课程是由2位以上 男老师教,并且他们的级别大于3,并且他们年龄在40以下的。
一般过程性的方法:
先找出teacher表里面所有的teacherId (男老师教,并且他们的级别大于3,并且他们年龄在40),得到一个set
然后,把cource表加载到内存对象里面,然后开始循环,并用计数器去统计每个teacherId属性,看是否存在于set里面,如果存在就计数器+1, 计数器>3就跳出这条记录。
毫无疑问,以上的步骤还是比较的麻烦,估计一堆代码才理的清调理。
于是列转行的模式,就应运而生了。之所以称之为模式,是因为这样的问题场景实在是太常见了,就像在java里面要解决整个系统只用一个对象的问题而总结出了单例模式一样。
列转行需要一个工具表pivot,里面只有一列,存了1,2,3... , 你有多少个列需要转成行,就要多少个数。 我们这个例子是5
create table pivot (id int);
insert into pivot values (1),(2),(3),(4),(5);
步骤一: 放大结果集,一条记录复制5条, 然后对与每条记录,根据pivot.id只取一个teacherId值,得到一个临时表
select
c.id,
c.name,
case
when p.id=1 then c.teacher1
when p.id=2 then c.teacher2
when p.id=3 then c.teacher3
when p.id=4 then c.teacher4
when p.id=5 then c.teacher5
else 0
end
as teacherId
from cource c, pivot p
步骤二: 在临时表的基础上,再进行过滤(男老师教,并且他们的级别大于2,并且他们年龄在40),得到合适的结果集
select tmp.name from (
select
c.id,
c.name,
case
when p.id=1 then c.teacher1
when p.id=2 then c.teacher2
when p.id=3 then c.teacher3
when p.id=4 then c.teacher4
when p.id=5 then c.teacher5
else 0
end
as teacherId
from cource c, pivot p
) tmp where tmp.teacherId in (select id from teacher where age<40 and gender=1 and level>3)
步骤三: 分组统计,课程是由3位以上符合要求老师教的
select tmp.name from (
select
c.id,
c.name,
case
when p.id=1 then c.teacher1
when p.id=2 then c.teacher2
when p.id=3 then c.teacher3
when p.id=4 then c.teacher4
when p.id=5 then c.teacher5
else 0
end
as teacherId
from cource c, pivot p
) tmp where tmp.teacherId in (select id from teacher where age<40 and gender=1 and level>3)
group by tmp.name having count(*)>2
一般需要将列转成行来使用,一定是原有的Schema设计没有考虑周全。但是没有办法,为了保护现有的投资,不得不在糟糕的设计上周旋,用最小的代价去实现新需求。
毕竟认识都是由浅入深,为不健全的Schema设计付出代价,就像交税一样,无可避免。
举例:
课程表: 每门课程由5位老师教,要求包含老师的信息,以及一些课程的信息
create table cource (id int, name varchar(100), teacher1 int,teacher2 int,teacher3 int, teacher4 int, teacher5 int);
insert into cource values (1,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (2,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (3,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (4,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (5,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (6,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (7,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (8,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (9,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (10,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (11,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
insert into cource values (12,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));
老师表: 记录了每个老师的年龄,级别,性别
create table teacher(id int, age int, level int, gender int);
insert into teacher values (1, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (2, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (3, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (4, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (5, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (6, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (7, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (8, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (9, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (10, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (11, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (12, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (13, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
insert into teacher values (14, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);
需求:
找出一些课程, 这些课程是由2位以上 男老师教,并且他们的级别大于3,并且他们年龄在40以下的。
一般过程性的方法:
先找出teacher表里面所有的teacherId (男老师教,并且他们的级别大于3,并且他们年龄在40),得到一个set
然后,把cource表加载到内存对象里面,然后开始循环,并用计数器去统计每个teacherId属性,看是否存在于set里面,如果存在就计数器+1, 计数器>3就跳出这条记录。
毫无疑问,以上的步骤还是比较的麻烦,估计一堆代码才理的清调理。
于是列转行的模式,就应运而生了。之所以称之为模式,是因为这样的问题场景实在是太常见了,就像在java里面要解决整个系统只用一个对象的问题而总结出了单例模式一样。
列转行需要一个工具表pivot,里面只有一列,存了1,2,3... , 你有多少个列需要转成行,就要多少个数。 我们这个例子是5
create table pivot (id int);
insert into pivot values (1),(2),(3),(4),(5);
步骤一: 放大结果集,一条记录复制5条, 然后对与每条记录,根据pivot.id只取一个teacherId值,得到一个临时表
select
c.id,
c.name,
case
when p.id=1 then c.teacher1
when p.id=2 then c.teacher2
when p.id=3 then c.teacher3
when p.id=4 then c.teacher4
when p.id=5 then c.teacher5
else 0
end
as teacherId
from cource c, pivot p
步骤二: 在临时表的基础上,再进行过滤(男老师教,并且他们的级别大于2,并且他们年龄在40),得到合适的结果集
select tmp.name from (
select
c.id,
c.name,
case
when p.id=1 then c.teacher1
when p.id=2 then c.teacher2
when p.id=3 then c.teacher3
when p.id=4 then c.teacher4
when p.id=5 then c.teacher5
else 0
end
as teacherId
from cource c, pivot p
) tmp where tmp.teacherId in (select id from teacher where age<40 and gender=1 and level>3)
步骤三: 分组统计,课程是由3位以上符合要求老师教的
select tmp.name from (
select
c.id,
c.name,
case
when p.id=1 then c.teacher1
when p.id=2 then c.teacher2
when p.id=3 then c.teacher3
when p.id=4 then c.teacher4
when p.id=5 then c.teacher5
else 0
end
as teacherId
from cource c, pivot p
) tmp where tmp.teacherId in (select id from teacher where age<40 and gender=1 and level>3)
group by tmp.name having count(*)>2
发表评论
-
数据库导表
2013-04-26 19:54 1028不同服务器数据库之间的数据操作 --创建链接服务器 ex ... -
企业应用数据迁移
2012-08-21 10:21 1125开源ETL工具 Kettle Kettle是一款国外开源的et ... -
大数据量开发
2011-12-29 01:21 850update mes_jt_pdc tttt set tttt ... -
oracle导入问题,正在跳过表
2011-12-20 10:10 979导入过程中,所有表导入都出现提示, 正在跳过表。。。某某表名 ... -
weblogic -- Failed to bind remote object
2011-08-24 11:43 1141weblogic9.23,安装在IBM aix系统上。 配置j ... -
dblink连接数据库
2011-08-14 15:57 1138一般都会用imp exp 备份和导入数据库 但是有些时候 需 ... -
Statspack
2011-03-22 20:18 1228Statspack是Oracle自带的强大的性能分析工具。他可 ... -
如何写出性能优良的SQL?
2011-03-17 08:28 925我们要做到不但会写SQL,还要做到写出性能优良的SQL,以下为 ... -
数据库对象包括:?
2011-03-13 09:58 2113一、ORACLE数据库的模式对象的管理与维护 本 ... -
数据库的读写分离
2011-03-09 22:25 1051随着一个网站的业务不断扩展,数据不断增加,数据库的压力也会越来 ... -
数据库读写分离研究
2011-03-09 22:21 1128如何提高大型网站的访问速度,根据理解和经验一般常用的方法如SQ ... -
错误1053 oracle10g
2011-03-09 17:20 2434我在64位的server 2003系统里安装了32位的orac ... -
数据库主键设计之思考
2011-03-09 14:49 1372主键的无意义性: 我强调主键不应该具有实际的意义,这可能对 ... -
MySQL中int(M)的含义
2011-03-09 14:15 937作为对 ANSI/ISO SQL92 标准的一个扩展,MySQ ... -
c3p0和jdbcTemplate配置oracle集群RAC记录
2011-03-05 00:51 3768领导给了一串连接串给 ... -
导入数据库时出现ORA-01435: 用户不存在
2011-03-02 16:55 19120经由直接路径导出由EXPORT:V09.02.00创建的导出文 ... -
oracle 集群
2011-02-24 11:51 797最近公司数据库做啦集群,知道是用oracle RAC 做的,具 ... -
数据库水平切分的实现原理解析
2011-02-24 11:40 967第1章 引言 随着互联网应用的广泛普及,海量数据的存储和访 ... -
MySql存储引擎
2011-02-23 15:41 918下述存储引擎是最常用的: · MyISAM ... -
用java怎么实现有每天有1亿条记录的db存储
2011-02-23 11:48 1407每天1亿数据库操作,每秒1157条;使用Oracle数据库,读 ...
相关推荐
精典的SQL语句,行转列,列转行的语句 本文共分六个部分,分别讨论精典的SQL语句,行转列,列转行的语句,行列转换、取得数据表的所有列名、更改用户密码、判断表的哪些字段不允许为空、找到含有相同字段的表六个...
在本篇文章中,我们将深入探讨SQL的Unpivot函数,以及如何使用它来实现列转行的操作。 首先,让我们回顾一下上一篇文章中提到的Pivot函数。Pivot函数是用于将数据的行转换为列,通常用于数据透视或汇总,使得特定的...
- 列转行:使用UNPIVOT或其他数据库函数将列数据转换为行数据。 - 将结果集反向转置为一列:将多行数据合并为单个字段。 - 抑制结果集中的重复值:使用DISTINCT关键字。 - 利用“行转列”进行计算:在转换后的...
有时会遇到没有遵守第一范式设计模式的业务表。即一列中存储了多个属性值。如下表 pk value 1 ET,AT 2 AT,BT 3 AT,DT 4 DT,CT,AT 一般有这两种常见需求(测试数据见文末) 1.得到所有的不重复的值,...
Oracle数据库提供了多种方法来实现这种转换,本篇将详细介绍如何在Oracle中进行行列转换,包括列转行、行转列以及各种复杂场景下的转换。 1. 列转行 列转行主要是将数据库表中的多列数据转换为多行数据。在Oracle...
#### 六、列转行组件——Normalizer Transformation **简要说明** Normalizer Transformation 用于将多列转换为多行,适用于需要将宽格式数据转换为长格式数据的场景。 **组件配置** 1. **组件选择**: 选择 ...
- 更新操作通过`RowUpdating`事件完成,从编辑模式下的行获取新的字段值,并构建相应的更新SQL语句执行。 #### 3. GridView正反双向排序 - **功能简介**:实现GridView的升序与降序双向排序。 - **实现方法**: ...
- **方法一:** 使用CSS样式更改行背景色。 - **方法二:** 通过JavaScript或jQuery动态改变背景色。 **示例代码:** ```html .highlight { background-color: #f0f0f0; } function OnRowMouseOver(sender, ...
为了增强用户体验,可以通过CSS或JavaScript实现鼠标悬停时更改行背景色的效果。CSS方法较为简单,只需设置`:hover`伪类即可。而JavaScript方法提供了更多的定制选项,例如动画效果或渐变过渡。 #### 7. GridView...