`
floatingleaf
  • 浏览: 2412 次
  • 性别: Icon_minigender_1
  • 来自: 北京
最近访客 更多访客>>
文章分类
社区版块
存档分类
最新评论

Oracle列转行_看自SQL经典模式-列转行

SQLOracleCC++C# 
阅读更多
需求：
找出一些课程， 这些课程是由1位以上 男老师教，并且他们的级别大于3，并且他们年龄在40以下的。

课程表： 每门课程由5位老师教，要求包含老师的信息，以及一些课程的信息
create table course (id int, name varchar(100), teacher1 int,teacher2 int,teacher3 int, teacher4 int, teacher5 int);

insert into course values (1,concat('Course_',round(sys.dbms_random.value(1,300))),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)));
insert into course values (2,concat('Course_',round(sys.dbms_random.value(1,300))),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)));
insert into course values (3,concat('Course_',round(sys.dbms_random.value(1,300))),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)));
insert into course values (4,concat('Course_',round(sys.dbms_random.value(1,300))),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)));
insert into course values (5,concat('Course_',round(sys.dbms_random.value(1,300))),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)));
insert into course values (6,concat('Course_',round(sys.dbms_random.value(1,300))),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)));
insert into course values (7,concat('Course_',round(sys.dbms_random.value(1,300))),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)));
insert into course values (8,concat('Course_',round(sys.dbms_random.value(1,300))),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)));
insert into course values (9,concat('Course_',round(sys.dbms_random.value(1,300))),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)));
insert into course values (10,concat('Course_',round(sys.dbms_random.value(1,300))),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)));
insert into course values (11,concat('Course_',round(sys.dbms_random.value(1,300))),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)));
insert into course values (12,concat('Course_',round(sys.dbms_random.value(1,300))),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)),round(sys.dbms_random.value(1,14)));

-- DELETE TABLE course;
--DROP TABLE course;
COMMIT;
SELECT * FROM course a ORDER BY A.NAME;

老师表： 记录了每个老师的年龄，级别，性别
这里需要说明: LEVEL 在Oracle 中是keyword，所以需要替换，这里我替换成了Scale
create table teacher(id int, age int,scale int, gender int);
insert into teacher values (1,round(SYS.DBMS_RANDOM.value(30,50)),round(SYS.DBMS_RANDOM.value(1,10)),round(SYS.DBMS_RANDOM.value(0,1)));
insert into teacher values (2, round(SYS.DBMS_RANDOM.value(30,50)),round(SYS.DBMS_RANDOM.value(1,10)), round(SYS.DBMS_RANDOM.value(0,1)));
insert into teacher values (3, round(SYS.DBMS_RANDOM.value(30,50)),  round(SYS.DBMS_RANDOM.value(1,10)), round(SYS.DBMS_RANDOM.value(0,1)));
insert into teacher values (4, round(SYS.DBMS_RANDOM.value(30,50)),  round(SYS.DBMS_RANDOM.value(1,10)), round(SYS.DBMS_RANDOM.value(0,1)));
insert into teacher values (5, round(SYS.DBMS_RANDOM.value(30,50)),  round(SYS.DBMS_RANDOM.value(1,10)), round(SYS.DBMS_RANDOM.value(0,1)));
insert into teacher values (6, round(SYS.DBMS_RANDOM.value(30,50)),  round(SYS.DBMS_RANDOM.value(1,10)), round(SYS.DBMS_RANDOM.value(0,1)));
insert into teacher values (7, round(SYS.DBMS_RANDOM.value(30,50)),  round(SYS.DBMS_RANDOM.value(1,10)), round(SYS.DBMS_RANDOM.value(0,1)));
insert into teacher values (8, round(SYS.DBMS_RANDOM.value(30,50)),  round(SYS.DBMS_RANDOM.value(1,10)), round(SYS.DBMS_RANDOM.value(0,1)));
insert into teacher values (9, round(SYS.DBMS_RANDOM.value(30,50)),  round(SYS.DBMS_RANDOM.value(1,10)), round(SYS.DBMS_RANDOM.value(0,1)));
insert into teacher values (10, round(SYS.DBMS_RANDOM.value(30,50)),  round(SYS.DBMS_RANDOM.value(1,10)), round(SYS.DBMS_RANDOM.value(0,1)));
insert into teacher values (11, round(SYS.DBMS_RANDOM.value(30,50)),  round(SYS.DBMS_RANDOM.value(1,10)), round(SYS.DBMS_RANDOM.value(0,1)));
insert into teacher values (12, round(SYS.DBMS_RANDOM.value(30,50)),  round(SYS.DBMS_RANDOM.value(1,10)), round(SYS.DBMS_RANDOM.value(0,1)));
insert into teacher values (13, round(SYS.DBMS_RANDOM.value(30,50)),  round(SYS.DBMS_RANDOM.value(1,10)), round(SYS.DBMS_RANDOM.value(0,1)));
insert into teacher values (14, round(SYS.DBMS_RANDOM.value(30,50)),  round(SYS.DBMS_RANDOM.value(1,10)), round(SYS.DBMS_RANDOM.value(0,1)));

COMMIT;
--------
SELECT * FROM TEACHER B ORDER BY B.ID;

列转行需要一个工具表pivot，里面只有一列，存多少,取决于你有多少个列需要转成行， 我们这个例子是5(5位老师嘛)
CREATE TABLE pivot(id int);
INSERT INTO PIVOT VALUES(1);
INSERT INTO PIVOT VALUES(2);
INSERT INTO PIVOT VALUES(3);
INSERT INTO PIVOT VALUES(4);
INSERT INTO PIVOT VALUES(5);
COMMIT;
我勒个去！看到了表，才看见什么叫做 一条记录复制5行。拿课程名为:Course_114的为例
 
步骤一：得到一个临时表
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 COURSE C,PIVOT P
WHERE C.NAME = 'Course_114'

我比较笨,看到图(数据)了之后才发现,真的看到了是行转列耶!
步骤二： 在临时表的基础上，再进行过滤(男老师教gender=0，并且他们的级别大于2，并且他们年龄在41)，得到合适的结果集
SELECT TEMP.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 COURSE C,PIVOT P
) TEMP
      WHERE TEMP.TEACHERID IN(
            SELECT A.ID
            FROM TEACHER A
            WHERE A.SCALE>2 AND A.AGE = 41 AND A.GENDER = 0)


步骤三： 分组统计，课程是由1位以上符合要求老师教的
SELECT TEMP.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 COURSE C,PIVOT P
) TEMP
      WHERE TEMP.TEACHERID IN(
            SELECT A.ID
            FROM TEACHER A
            WHERE A.SCALE>2 AND A.AGE < 40 AND A.GENDER = 0)
GROUP BY TEMP.NAME
HAVING COUNT(*) > 1;
    
1
顶
3
踩
分享到：
SQLServer和Oracle常用函数对比
评论
2 楼 liuyes 2011-03-19  
有这么复杂么？偶都用decode转
1 楼 floatingleaf 2011-03-18  
怎么截的图 都没了呢
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    ORACLE 列转行 DECODE函数用法

    总之，Oracle的DECODE函数是处理列值转换的强大工具，它可以在列转行的过程中帮助你构建灵活且易于理解的查询结果。结合PIVOT和其他函数，你可以实现复杂的报表逻辑，让数据分析变得更加简单直观。在实际应用中，...

    oracle的列转行问题

    Oracle 列转行问题解决方案 Oracle 数据库中，列转行问题是一个非常传统的话题。在这个问题中，我们需要将行数据转换成不同的列表示，或者将不同的列数据写到同一列的不同行上。这种问题在实际应用中非常常见，如将...

    Oracle的列转行问题

    总结来说，Oracle中的列转行可以通过多种方法实现，包括传统的`DECODE`或`CASE`结合子查询，以及从11g版本开始提供的`PIVOT`功能。选择哪种方法取决于具体的需求和数据规模，以及对性能的考虑。在处理大数据量时，应...

    DB2 SQL 实现行转列，列转行

    DB2 SQL 通过函数(CONCAT/POSSTR/LOCATE)实现行转列，列转行 可以按照标点把多列转换为一行，多行转换为一列

    sql行转列，与列转行

    sql 行转列 与列转行，oracle ，msssql等，详细数据库操作方法，各种例子，欢迎大家学习。、~

    oracle中实现列转行实例

    oracle中实现列转行实例,有表的创建，数据的插入，查询的sql

    sql经典 oracle的查询结果的行列互换

    ### Oracle SQL 中的行列互换技术详解 #### 一、引言 在处理数据库查询时，我们经常会遇到需要对查询结果进行格式调整的情况，其中一种常见的需求就是将数据的行列进行互换。例如，原始数据可能按列的形式存储了...

    oracle wm_concat 列转行 逗号分隔

    然而，由于WM_CONCAT的局限性，Oracle建议使用其他替代方法，如使用LISTAGG函数（自Oracle 11g R2起提供）或者自定义聚合函数来实现类似的功能。LISTAGG函数提供了更好的控制，比如可以指定分隔符，并且是标准的SQL...

    oracle行转列

    - T-SQL_经典行专列、列转行，分页及存储过程.doc：T-SQL是SQL Server的扩展，但其中的行转列和分页概念与Oracle相似。在Oracle中，可以使用ROW_NUMBER()函数配合PARTITION BY和ORDER BY来实现分页查询。 - oracle_...

    列转行小工具

    在Oracle Developer中，"列转行小工具"是一种实用功能，它主要用于处理数据库表中的数据，将多列数据转换为单列数据，通常涉及到的是数据的行列转换操作。这种操作在处理复杂的数据分析、报表生成或者接口对接时非常...

    Oracle行转列之pivot

    在Oracle数据库中，行转列（也称为数据透视）和列转行（unpivot）是SQL查询中用于数据转换的高级功能。从Oracle 11g版本开始，引入了PIVOT和UNPIVOT关键字，以支持显式的查询转换，即从行数据转换为列数据，或从列...

    Oracle行列转换_总结

    列转行是指将表中的多列数据转换为单列多行的形式。这一操作在某些分析场景下非常有用，例如，当你需要展示每个记录的不同属性时。 **1. UNION ALL 方法** - **适用版本**: Oracle 8i 及以上 - **操作原理**: ...

    sql动态行转列 存储过程

    `PIVOT`是Oracle数据库特有的行转列函数，而`CASE`语句则在大多数SQL方言中可用。 3. **执行动态SQL**：使用`EXEC`或`sp_executesql`（在SQL Server中）来执行动态构建的SQL语句。 4. **处理结果**：存储过程可以...

    Oracle SQLServer行列转换

    ### Oracle SQLServer 行列转换知识点详解 #### 一、SQLServer 行转列方法 在SQLServer中，实现行转列的操作有两种主要方法：一种是利用`CASE...WHEN`语句配合`GROUP BY`聚合函数的方式；另一种是通过`PIVOT`操作...

    Oracle行转列

    Oracle行转列 Oracle行转列是指在Oracle数据库中将行数据转换为列数据或将列数据转换为行数据的操作。这种操作在实际应用中非常有用，例如，在数据报表、数据分析和数据挖掘等领域都需要使用行转列操作。 1. 列...

    Oracle中SQL语句行列之间相互转换

    ### Oracle中SQL语句行列之间的相互转换 在Oracle数据库中，有时我们需要将表格中的行转换为列或将列转换为行，这种操作被称为行列转换。这种转换对于数据分析、报表制作等场景非常有用。本文将详细介绍Oracle中...

    Oracle逗号分隔列转行实现方法

    该方法只需要sql语句就可以实现列转行。 　下面给出该方法的示例： select a,b,c from(with test as (select ‘aaa’ a,’bbb’ b,’1,2,3′ c from dual)select a,b,substr(t.ca,instr(t.ca, ‘,’, 1, c.lv) + 1,...

Magicbox
Global site tag (gtag.js) - Google Analytics