图片 9

sql 行转列

  • ##### 平日在做多少总结的时候会用到行转列,假若要总括学子的实际业绩,数据Curry询问出来的会是那样的,但那并不可能到达想要的成效,所以要在询问的时候做一下甩卖

多行转字符串

这一个比较容易,用||或concat函数能够落成

 SQL
Code 

1
2

  

select concat(id,username) str from app_user
select id||username str from app_user

一、行转列

几天前在进行MES项目标付出,碰到了那样的贰个难题:
急需页面是那般的:

图片 1

供给页面.png

这是叁个展现生产线上配备情形的页面。注意红框框住的几列,这几列是显得设备参数的列,其并非多少表中的列,而是由顾客定义在别的表中的数目行转变而来的。(见下图)。

图片 2

客商定义列的页面

在数量表中,与其城门失火的五个表如下所示:

图片 3

参数定义表.png

终极查询出来的效劳应如下所示:

图片 4

行转列后的功用.png

这里为了制止在传输进度中现身乱码难点,将汉语参数,如“温度”,用克罗地亚共和国(Republika Hrvatska)语参数”STD_PARAM_1″代替。

由此,那正是贰个行转列难点。

竭泽而渔那一个标题亟需分两步举办,要写两句SQL。
率先步先用一个Group By 查出参数表中有些许类需求参数。即为须要增添哪些类

“SELECT equ_param FROM MEA_EQUIP_TRUTH GROUP BY equ_param

查询出的结果为’压力’、’湿度’、’气压等’。

接下去须要运用Decode函数进行行转列,
转载SQL如下所示:

SELECT
       MAX(M.EQU_ID) EQU_ID,
       MAX(M.EQU_NAME) EQU_NAME,
       MAX(M.EQU_STATUS) EQU_STATUS,
       SUM(DECODE(T.EQU_PARAM, '压力', T.STD_VALUE)) STD_PARAM_1,
       SUM(DECODE(T.EQU_PARAM, '压力', T.ACT_VALUE)) ACT_PARAM_1,
       SUM(DECODE(T.EQU_PARAM, '湿度', T.STD_VALUE)) STD_PARAM_2,
       SUM(DECODE(T.EQU_PARAM, '湿度', T.ACT_VALUE)) ACT_PARAM_2,
       SUM(DECODE(T.EQU_PARAM, '气压', T.STD_VALUE)) STD_PARAM_3,
       SUM(DECODE(T.EQU_PARAM, '气压', T.ACT_VALUE)) ACT_PARAM_3,
       SUM(DECODE(T.EQU_PARAM, '温度', T.STD_VALUE)) STD_PARAM_4,
       SUM(DECODE(T.EQU_PARAM, '温度', T.ACT_VALUE)) ACT_PARAM_4 
  FROM MEA_POSITION_EQUIP M, MEA_EQUIP_TRUTH T
 WHERE M.COMPANY_CODE = '00'
   AND M.COMPANY_CODE = T.COMPANY_CODE(+)
   AND M.LINE_CODE = T.LINE_CODE(+)
   AND M.POSITION_CODE = T.POSITION_CODE(+)
   AND M.ITEM_CODE = T.ITEM_CODE(+)
   AND M.PRODUCT_LOT_NO = T.PRODUCT_LOT_NO(+)
   AND M.EQU_ID = T.EQU_ID(+)
 GROUP BY M.COMPANY_CODE,
          M.LINE_CODE,
          M.ITEM_CODE,
          M.POSITION_CODE,
          M.PRODUCT_LOT_NO,
          M.EQU_ID,
          M.EQU_NAME

以此SQL中富含如下一些Oracle数据库的用法:

         
图片 5

字符串转多列

实际就是拆分字符串的难点,能够运用
substr、instr、regexp_substr函数形式

DECODE函数

DECODE 函数是兑现行转列作用的首要函数。其为Orcale中的只有函数

SUM(DECODE(T.EQU_PARAM, ‘压力’, T.STD_VALUE)) STD_PARAM_1,

这生龙活虎行的意味是T表中的参数EQU_PARAM,假设等于’压力’,则赶回T表中相应行STD_VALUE的值。
中间’压力’
等值,能够在首先句SQL中获悉。然后利用循环语句拼成上面的讲话。这段循环能够写在SP中,亦写能够在JAVA中。

  • CREATE TABLE TestTable(

        [Id] [int] IDENTITY(1,1) NOT NULL,
        [UserName] [nvarchar](50) NULL,
        [Subject] [nvarchar](50) NULL,
        [Source] [numeric](18, 0) NULL
    ) ON [PRIMARY]
    go
    INSERT INTO TestTable ([UserName],[Subject],[Source]) 
        SELECT N'张三',N'语文',60  UNION ALL
        SELECT N'李四',N'数学',70  UNION ALL
        SELECT N'王五',N'英语',80  UNION ALL
        SELECT N'王五',N'数学',75  UNION ALL
        SELECT N'王五',N'语文',57  UNION ALL
        SELECT N'李四',N'语文',80  UNION ALL
        SELECT N'张三',N'英语',100
    GO
    

    此间自个儿用了两种艺术来促成行转列第生龙活虎种:静态行转列

  • select UserName 姓名,

    sum(case Subject when '语文' then Source else 0 end) 语文,sum(case Subject when '数学' then Source else 0 end) 数学,
    sum(case Subject when '英语' then Source else 0 end) 英语 from TestTable group by UserName
    

    用povit行转列

  • select * from

    (select UserName,Subject,Source from TestTable) testpivot(sum(Source) for Subject in(语文,数学,英语)
    ) pvt
    

    用存款和储蓄进程行转列

  • alter proc pro_test

    @userImages varchar(200),
    @Subject varchar(20),
    @Subject1 varchar(200),
    @TableName varchar(50)
    as
     declare @sql varchar(max)='select * from (select '+@userImages+' from'+@TableName+') tab
    pivot
    (
    sum('+@Subject+') for Subject('+@Subject1+')
    ) pvt'
    exec (@sql)
    go
    exec pro_test 'UserName,Subject,Source',
    'TestTable',
    'Subject',
    '语文,数学,英语'
    

    它们的坚决守护都以那样的

  • 图片 6

  • 上述二种情势落进行转列,我们得以依靠本人的须求选用分化的主意

字符串转多行

使用union
all函数等措施

(+) 左右连接

有关(+)的用法 贴生机勃勃篇英特网的素材,介绍的很详细。
关键是要记住

(+)
哪个表有加号,那些表便是相称表。如若加号写在右表,左表正是全方位呈现,所以是左连接。
即右加号为左连接,左加号为右连接

Oracle(+)号用法

wm_concat函数

第风姿浪漫让我们来看看那个美妙的函数wm_concat(列名),该函数能够把列值以”,”号分隔起来,并出示成风流倜傥行,接下去上例子,看看这些美妙的函数怎么样行使盘算测验数据

 SQL
Code 

1
2
3
4
5
6

  

create table test(id number,name varchar2(20));
insert into test values(1,’a’);
insert into test values(1,’b’);
insert into test values(1,’c’);
insert into test values(2,’d’);
insert into test values(2,’e’);

二、Oracle 树查询

在工程上临时会动用树形结构,举个例子一流、二级、三级菜单 以及,省、市、县等。树形结构的数据会以如下方式存款和储蓄在Oracle数据库中:

图片 7

树型结构的存款和储蓄.png

及各种节点有三个存储父节点ID的字段 PARENT_ID 。

像这系列型的储存结构,Oracle有特意的重大字张开询问:

贴二个树查询讲的相比较好的博客oracle树形查询 start with connect
by

select * from menu start with id=’130000′ connect by prior id =
parent_id ;

图片 8

6树查询.png

对于树查询 关键是 对 关键字 prior 的知晓。

prior 条件表示子数据要求满意父数据的如何条件
prior放的左右岗位决定了寻觅是自底向上依然自顶向下.
左边手是自上而下(找子节点),右侧是自下而上(找父节点)

地点那条SQL的含义为,从ID = 130000 起初,假使有PARENT_ID
等于130000的方方面面查出来,然后递归。即父节点的ID等于子节点PARENT_ID的持有数据。那样能够查出130000节点下的全部子节点。

效果1 : 行转列 ,默许逗号隔绝

 SQL
Code 

1

  

select wm_concat(name) name from test;

   

图片 9

三、查出表中全数的列:

任何查出表音信的常用SQL

select column_name from user_tab_cols where table_name=’TAB’;

1、查找表的具有索引(富含索引名,类型,构成列):

select t.*,i.index_type from user_ind_columns t,user_indexes i
where t.index_name = i.index_name and t.table_name = i.table_name
and t.table_name = 要查询的表

2、查找表的主键(包罗名称,构成列):

select cu.* from user_cons_columns cu, user_constraints au where
cu.constraint_name = au.constraint_name and au.constraint_type = ‘P’
and au.table_name = 要查询的表

3、查找表的唯后生可畏性限定(饱含名称,构成列):

select column_name from user_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name and au.constraint_type
= ‘U’ and au.table_name = 要查询的表

4、查找表的外键(包涵名称,援用表的表名和呼应的键名,上边是分成多步查询):

select * from user_constraints c where c.constraint_type = ‘R’ and
c.table_name = 要查询的表

查询外键约束的列名:

select * from user_cons_columns cl where cl.constraint_name =
外键名称

查询引用表的键的列名:

select * from user_cons_columns cl where cl.constraint_name =
外键援引表的键名

5、查询表的全体列及其性质

select t.*,c.COMMENTS from user_tab_columns t,user_col_comments c
where t.table_name = c.table_name and t.column_name = c.column_name
and t.table_name = 要查询的表

6、查询全部表
select* from tabs