- 浏览: 270846 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
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 983不同服务器数据库之间的数据操作 --创建链接服务器 ex ... -
企业应用数据迁移
2012-08-21 10:21 1097开源ETL工具 Kettle Kettle是一款国外开源的et ... -
大数据量开发
2011-12-29 01:21 822update mes_jt_pdc tttt set tttt ... -
oracle导入问题,正在跳过表
2011-12-20 10:10 950导入过程中,所有表导入都出现提示, 正在跳过表。。。某某表名 ... -
weblogic -- Failed to bind remote object
2011-08-24 11:43 1109weblogic9.23,安装在IBM aix系统上。 配置j ... -
dblink连接数据库
2011-08-14 15:57 1111一般都会用imp exp 备份和导入数据库 但是有些时候 需 ... -
Statspack
2011-03-22 20:18 1198Statspack是Oracle自带的强大的性能分析工具。他可 ... -
如何写出性能优良的SQL?
2011-03-17 08:28 869我们要做到不但会写SQL,还要做到写出性能优良的SQL,以下为 ... -
数据库对象包括:?
2011-03-13 09:58 2083一、ORACLE数据库的模式对象的管理与维护 本 ... -
数据库的读写分离
2011-03-09 22:25 1012随着一个网站的业务不断扩展,数据不断增加,数据库的压力也会越来 ... -
数据库读写分离研究
2011-03-09 22:21 1089如何提高大型网站的访问速度,根据理解和经验一般常用的方法如SQ ... -
错误1053 oracle10g
2011-03-09 17:20 2404我在64位的server 2003系统里安装了32位的orac ... -
数据库主键设计之思考
2011-03-09 14:49 1338主键的无意义性: 我强调主键不应该具有实际的意义,这可能对 ... -
MySQL中int(M)的含义
2011-03-09 14:15 906作为对 ANSI/ISO SQL92 标准的一个扩展,MySQ ... -
c3p0和jdbcTemplate配置oracle集群RAC记录
2011-03-05 00:51 3732领导给了一串连接串给 ... -
导入数据库时出现ORA-01435: 用户不存在
2011-03-02 16:55 18978经由直接路径导出由EXPORT:V09.02.00创建的导出文 ... -
oracle 集群
2011-02-24 11:51 759最近公司数据库做啦集群,知道是用oracle RAC 做的,具 ... -
数据库水平切分的实现原理解析
2011-02-24 11:40 871第1章 引言 随着互联网应用的广泛普及,海量数据的存储和访 ... -
MySql存储引擎
2011-02-23 15:41 885下述存储引擎是最常用的: · MyISAM ... -
用java怎么实现有每天有1亿条记录的db存储
2011-02-23 11:48 1370每天1亿数据库操作,每秒1157条;使用Oracle数据库,读 ...
相关推荐
ibatis标签提示工具 sql-map-2.dtd
SQL-Server-2008-数据库还原到SQL-Server-2012.docx
x-pack-sql-jdbc-7.4.2.jar jdbc驱动包 x-pack-sql-jdbc-7.4.2.jar jdbc驱动包
sqljdbc4-4.0.jar sqljdbc4-4.0.jar sqljdbc4-4.0.jar com.microsoft.sqlserver sqljdbc4 sqlserver
x-pack-sql-jdbc-6.8.0.jar
flink-sql-connector-oceanbase-cdc 2.5-SNAPSHOT
flink-sql-connector-kafka_2.11-1.12.0
flume-ng-sql-source-1.5.2.jar从数据库中增量读取数据到hdfs中的jar包
flume-ng-sql-source-release-1.5.2.jar 用flume-ng-sql-source 从数据库抽取数据到kafka,支持sql
x-pack-sql-jdbc-7.4.2
SQL之行转列 SQL之行转列 SQL之行转列
x-pack-sql-jdbc-7.8.0.jar 连接es的数据库jdbc 驱动包 x-pack-sql-jdbc-7.8.0.jar 连接es的数据库jdbc 驱动包
flink-sql-connector-mysql-cdc-1.1.1.jar
flink-sql-connector-kafka_2.11-1.11.2.jar
x-pack-sql-jdbc-6.5.4.jar
sql-map-config-2.dtd
liferay-portal-sql-6.1.1-ce-ga2-20120731132656558
flink-sql-connector-hive-2.3.6_2.11-1.11.0.jar
spark-sql_2.11-2.4.0-cdh6.1.1.jar
中兴SQL经典题目----奉献给大家了!希望对大家有帮助