生产各种数据格式的sql语句

最新推荐文章于 2024-02-27 17:29:40 发布

小新新_88

最新推荐文章于 2024-02-27 17:29:40 发布

阅读量501

点赞数

分类专栏： Mysql/PLSQL

本文链接：https://blog.csdn.net/qingfeng45697/article/details/52397610

版权

Mysql/PLSQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1.行转列：

样例：

重复数据：

ROW_NUMBER() OVER函数的基本用法

简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号。

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

实例：

初始化数据

create table employee (empid int ,deptid int ,salary decimal(10,2))insert into employee values(1,10,5500.00)insert into employee values(2,10,4500.00)insert into employee values(3,20,1900.00)insert into employee values(4,20,4800.00)insert into employee values(5,40,6500.00)insert into employee values(6,40,14500.00)insert into employee values(7,40,44500.00)insert into employee values(8,50,6500.00)insert into employee values(9,50,7500.00)

数据显示为

需求：根据部门分组，显示每个部门的工资等级

预期结果：

5

SQL脚本：

SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee

  User表结构如下：

ID  PassWord CreatedDate
002  aaa  2016/06/04
002  bbb  2016/06/03
002  bbb  2016/06/02
002  ccc   2016/06/01

如果用户002的当前密码是aaa，那么2016/06/04的密码与当前密码相同跳过，2016/06/03的密码bbb与当前密码不同，所以选中，2016/06/02的密码与当前密码不同但是与已选密码bbb相同跳过，2016/06/01的密码和当前密码与已选密码bbb都不同，所以选中。

最后该用户最近3次使用的密码是aaa，bbb，ccc

with t as (
select '002' as id,'aaa' as password,'2016/06/04' as createdate from dual
union all
select '002','bbb','2016/06/03' from dual
union all
select '002','bbb','2016/06/02' from dual
union all
select '002','ccc','2016/06/01' from dual
),
m as (
  select id, password, createdate, 
         row_number() over(partition by password order by createdate desc) rn
    from t 
)
select * from m where rownum <= 3 and rn =1

4。

  日期                      LOTID        车间
2016-01-01  07:30:01             1           H1
2016-01-01  12:30:01             1           H1

2016-01-01  19:29:59             2           H2
2016-01-01  22:29:59             3           H2

2016-01-01  23:59:59             4           H3
2016-01-02  03:00:00             4           H3
2016-01-02  07:29:00             5           H3

2016-01-02  07:31:00            6            H3
2016-01-02  12:00:00            7            H3
2016-01-02  19:29:00            8            H3
2016-01-03  01:31:00            9            H4
2016-01-03  02:00:00            9            H4
2016-01-03  07:29:00            9            H4

2016-01-04  07:31:00            10           H5

我想要的到数据
 DATE               CNT             车间
2016-01-01           1               H1
2016-01-01           2               H2
2016-01-01           2               H3

2016-01-02           3               H3
2016-01-02           1               H4

2016-01-03    （没有数据 不用处理）

2016-01-04           1               H5


日期统计是从 当天  07:30:01 到第2天07:30:00  算。 LOTID     在当天同一车间下出现多次算一次.

  sql：

    WITH T AS 
( SELECT TO_DATE('2016-01-01 07:30:01','YYYY/MM/DD HH24:MI:SS') AS 日期 ,'1' as LOTID,'H1' AS 车间 from dual
  union all 
  SELECT TO_DATE('2016-01-01 12:30:01','YYYY/MM/DD HH24:MI:SS') AS 日期 ,'1' as LOTID,'H1' AS 车间 from dual
   union all 
  SELECT TO_DATE('2016-01-01 19:29:59','YYYY/MM/DD HH24:MI:SS') AS 日期 ,'2' as LOTID,'H2' AS 车间 from dual
   union all 
  SELECT TO_DATE('2016-01-01 22:29:59','YYYY/MM/DD HH24:MI:SS') AS 日期 ,'3' as LOTID,'H2' AS 车间 from dual
   union all 
  SELECT TO_DATE('2016-01-01 23:59:59','YYYY/MM/DD HH24:MI:SS') AS 日期 ,'4' as LOTID,'H3' AS 车间 from dual 
  union all 
  SELECT TO_DATE('2016-01-02 03:00:00','YYYY/MM/DD HH24:MI:SS') AS 日期 ,'4' as LOTID,'H3' AS 车间 from dual
   union all 
  SELECT TO_DATE('2016-01-02 07:29:00','YYYY/MM/DD HH24:MI:SS') AS 日期 ,'5' as LOTID,'H3' AS 车间 from dual
   union all 
  SELECT TO_DATE('2016-01-02 07:31:00','YYYY/MM/DD HH24:MI:SS') AS 日期 ,'6' as LOTID,'H3' AS 车间 from dual
   union all 
  SELECT TO_DATE('2016-01-02 12:00:00','YYYY/MM/DD HH24:MI:SS') AS 日期 ,'7' as LOTID,'H3' AS 车间 from dual
   union all 
  SELECT TO_DATE('2016-01-02 19:29:00','YYYY/MM/DD HH24:MI:SS') AS 日期 ,'8' as LOTID,'H3' AS 车间 from dual
  union all 
  SELECT TO_DATE('2016-01-03 01:31:00','YYYY/MM/DD HH24:MI:SS') AS 日期 ,'9' as LOTID,'H4' AS 车间 from dual
  union all 
  SELECT TO_DATE('2016-01-03 02:00:00','YYYY/MM/DD HH24:MI:SS') AS 日期 ,'9' as LOTID,'H4' AS 车间 from dual
  union all 
  SELECT TO_DATE('2016-01-03 07:29:00','YYYY/MM/DD HH24:MI:SS') AS 日期 ,'9' as LOTID,'H4' AS 车间 from dual
  union all 
  SELECT TO_DATE('2016-01-04 07:31:00','YYYY/MM/DD HH24:MI:SS') AS 日期 ,'10' as LOTID,'H5' AS 车间 from dual)


select trunc(日期 - (7 * 60 + 30) /24/60) as date1, count(distinct lotid) cnt, 车间
from t 
group by trunc(日期 - (7 * 60 + 30) /24/60) , 车间
order by date1,车间

ROWNUM 使用

返回第5—9条纪录，按月份排序
SQL> select * from (select rownum row_id ,month,sell
2 from (select month,sell from sale group by month,sell))
3 where row_id between 5 and 9;

ROW_ID MONTH SELL
---------- ------ ----------
5 200005 1400
6 200006 1500
7 200007 1600
8 200008 1000
9 200101 1100

row_number() over(partition by a order by b desc) rn 用法

ow_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

SQL> SELECT G.*, ROW_NUMBER() OVER(PARTITION BY a ,b ORDER BY c DESC) ROWN
2    from (select '1' a, '2' b, '1' c
3            from dual
4          union all
5          select '1', '2', '2'
6            from dual
7          union all
8          select '1', '3', '3'
9            from dual
10          union all
11          select '1', '3', '4'
12            from dual
13          union all
14          select '1', '4', '5' from dual
15          union all
16          select '1','3','5'from dual) G
17 ;

A B C       ROWN
- - - ----------
1 2 2          1
1 2 1          2
1 3 5          1
1 3 4          2
1 3 3          3
1 4 5          1

6 rows selected

　与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．

　　row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）．

　　rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．

　　dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的．

　　lag（arg1,arg2,arg3):
arg1是从其他行返回的表达式
arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。
arg3是在arg2表示的数目超出了分组的范围时返回的值。

语句一:

select row_number() over(order by sale/cnt desc) as sort, sale/cnt
from (
select -60 as sale,3 as cnt from dual union
select 24 as sale,6 as cnt from dual union
select 50 as sale,5 as cnt from dual union
select -20 as sale,2 as cnt from dual union
select 40 as sale,8 as cnt from dual);

执行结果:

          SORT       SALE/CNT
---------- ----------
             1             10
             2              5
             3              4
             4            -10
             5            -20

语句二:查询员工的工资,按部门排序

select ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order from scott.emp;

执行结果:

ENAME                           SAL      SAL_ORDER
-------------------- ---------- ----------
KING                           5000              1
CLARK                          2450              2
MILLER                         1300              3
SCOTT                          3000              1
FORD                           3000              2
JONES                          2975              3
ADAMS                          1100              4
SMITH                           800              5
BLAKE                          2850              1
ALLEN                          1600              2
TURNER                         1500              3
WARD                           1250              4
MARTIN                         1250              5
JAMES                           950              6

已选择14行。

语句三:查询每个部门的最高工资

select deptno,ename,sal from
(select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order
from scott.emp) where sal_order <2;

执行结果:

       DEPTNO ENAME                          SAL
---------- -------------------- ----------
           10 KING                          5000
           20 SCOTT                         3000
           30 BLAKE                         2850

已选择3行。

语句四：

select deptno,sal,rank() over (partition by deptno order by sal) as rank_order from scott.emp order by deptno;

执行结果:

     DEPTNO         SAL RANK_ORDER
---------- ---------- ----------
         10        1300           1
         10        2450           2
         10        5000           3
         20         800           1
         20        1100           2
         20        2975           3
         20        3000           4
         20        3000           4
         30         950           1
        30        1250           2
         30        1250           2
         30        1500           4
         30        1600           5
         30        2850           6

已选择14行。

语句五：

select deptno,sal,dense_rank() over(partition by deptno order by sal) as dense_rank_order from scott.emp order by deptn;

执行结果：

     DEPTNO         SAL DENSE_RANK_ORDER
---------- ---------- ----------------
         10        1300                 1
         10        2450                 2
         10        5000                 3
         20         800                 1
         20        1100                 2
         20        2975                 3
         20        3000                 4
         20        3000                 4
         30         950                 1
        30        1250                 2
         30        1250                 2
         30        1500                 3
         30        1600                 4
         30        2850                 5

已选择14行。

语句六：

select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) as lag_ from scott.emp order by deptno;

执行结果：

     DEPTNO ENAME                        SAL LAG_
---------- -------------------- ---------- --------------------
         10 CLARK                       2450
         10 KING                        5000 CLARK
         10 MILLER                      1300 KING
         20 ADAMS                       1100
         20 FORD                        3000 ADAMS
         20 JONES                       2975 FORD
         20 SCOTT                       3000 JONES
         20 SMITH                        800 SCOTT
         30 ALLEN                       1600
         30 BLAKE                       2850 ALLEN
         30 JAMES                        950 BLAKE
         30 MARTIN                      1250 JAMES
         30 TURNER                      1500 MARTIN
         30 WARD                        1250 TURNER

之前用过row_number()，rank()等排序与over( partition by ... ORDER BY ...)，这两个比较好理解: 先分组，然后在组内排名。

今天突然碰到sum(...) over( partition by ... ORDER BY ... )，居然搞不清除怎么执行的，所以查了些资料，做了下实操。

1. 从最简单的开始

　　sum(...) over( )，对所有行求和

　　sum(...) over( order by ... )，和 = 第一行到与当前行同序号行的最后一行的所有值求和，文字不太好理解，请看下图的算法解析。

with aa as
(
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 8 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual
)
SELECT a,b,c,
sum(c) over(order by b) sum1,--有排序，求和当前行所在顺序号的C列所有值
sum(c) over() sum2 from aa--无排序，求和 C列所有值

sum() over()

2. 与 partition by 结合

　　sum(...) over( partition by... )，同组内所行求和

　　sum(...) over( partition by... order by ... )，同第1点中的排序求和原理，只是范围限制在组内

with aa as
(
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 7 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual
)
SELECT a,b,c,sum(c) over( partition by b ) partition_sum,
sum(c) over( partition by b order by a desc) partition_order_sum
FROM aa;

view sql

转载于：http://www.cnblogs.com/luhe/p/4155612.html；

问题：
1、将 2016-02-29 加6个月，结果显示为2016-08-29
2、将2016-08-31 加6个月，结果显示为2017-03-03
同理其他的日期加6个月后也需要是按如上2点的规律进行转换。

曾尝试使用函数 add_month的，但结果分别显示为 “2016-08-31”、“2017-02-28”

请问，如何可以显示为 “2016-08-29”、“2017-03-03”

SQL> select add_months(to_date('2016-02-29','yyyy-mm-dd'),6)+extract(day from date'2016-02-29')-extract(day from add_months(to_date('2016-02-29','yyyy-mm-dd'),6)) from dual;

ADD_MONTHS(TO_DATE(
-------------------
2016-08-29 00:00:00

SQL> select add_months(to_date('2016-08-31','yyyy-mm-dd'),6)+extract(day from date'2016-08-31')-extract(day from add_months(to_date('2016-08-31','yyyy-mm-dd'),6)) from dual;

ADD_MONTHS(TO_DATE(
-------------------
2017-03-03 00:00:00

小新新_88

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
生产各种数据格式的sql语句

1.行转列：样例：重复数据： 2.ROW_NUMBER() OVER函数的基本用法简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号。
复制链接

扫一扫

专栏目录