求一条高效SQL的实现

〇〇

select * from(
select round(id/2) id2,t0.*,'in'tm1,lead(tm)over(partition by ceil(id/2),emp_id order by val desc) from t0
)
where mod(id,2)=1;

  ID2         ID     EMP_ID EMP_NM VAL    TM       TM1  LEAD(TM)
----- ---------- ---------- ------ ------ -------- ---- --------
    1          1        100 NM     OUT    1100     in   1200
    2          3        100 NM     OUT    1300     in   1400
    3          5        200 NM1    OUT    1300     in   1330
    4          7        300 NM2    OUT    1500     in   1530

bell6248

测试如下：

SQL> select * from pps;

        ID     EMP_ID EMP_NM VAL TM
---------- ---------- ------ --- ----
         1        100 NM     OUT 1100
         2        100 NM     IN  1200
         3        100 NM     OUT 1300
         4        100 NM     IN  1400
         5        200 NM1    OUT 1300
         6        200 NM1    IN  1330
         7        300 NM2    OUT 1500
         8        300 NM2    IN  1530

8 rows selected

SQL>
SQL> select  id,
  2          emp_id,
  3          emp_nm,
  4          'OUT' val,
  5          max(decode(val, 'OUT', tm, null)) tm,
  6          'IN' valin,
  7          max(decode(val, 'IN', tm, null)) tmin
  8  from
  9  (select dense_rank() over(order by rn) id,
10          emp_id,
11          emp_nm,
12          val,
13          tm
14    from (select a.*,
15                 max(decode(val, 'OUT', id, 0)) over(order by id) rn
16            from pps a))
17  group by id, emp_id, emp_nm
18  order by 1;

        ID     EMP_ID EMP_NM VAL TM   VALIN TMIN
---------- ---------- ------ --- ---- ----- ----
         1        100 NM     OUT 1100 IN    1200
         2        100 NM     OUT 1300 IN    1400
         3        200 NM1    OUT 1300 IN    1330
         4        300 NM2    OUT 1500 IN    1530

SQL>

flzhang

bell6248 发表于 2017-3-1 22:00
如语句可以得到结果1，接下来就是用我前面说的

select   dense_rank() over(order by t.rn)  id,
...

谢谢您啊，这种方式是可以的，但因为是sqlserver 2008，不支持max()over(order by) 这种方式，但我也学习了，谢谢

bell6248

flzhang 发表于 2017-3-2 14:57
谢谢您啊，这种方式是可以的，但因为是sqlserver 2008，不支持max()over(order by) 这种方式，但我也学习 ...

sql server早就支持了， 除非目前使用的的数据库版本非常低

qqjue

1. with t as (
   
2. SELECT 1 ID, 100 EMP_ID,'NM' EMP_NM,'OUT' VAL ,'1100' TM
   
3. FROM DUAL
   
4. UNION ALL
   
5. SELECT 2 ID, 100 EMP_ID,'NM' EMP_NM,'IN' VAL , '1200' TM
   
6. FROM DUAL
   
7. UNION ALL
   
8. SELECT 3 ID, 100 EMP_ID,'NM' EMP_NM,'OUT' VAL ,'1300' TM
   
9. FROM DUAL
   
10. UNION ALL
    
11. SELECT 4 ID, 100 EMP_ID,'NM' EMP_NM,'IN' VAL , '1400' TM
    
12. FROM DUAL
    
13. UNION ALL
    
14. SELECT 5 ID, 200 EMP_ID,'NM1' EMP_NM,'OUT' VAL ,'1300' TM
    
15. FROM DUAL
    
16. UNION ALL
    
17. SELECT 6 ID, 200 EMP_ID,'NM1' EMP_NM,'IN' VAL,'1330' TM
    
18. FROM DUAL
    
19. UNION ALL
    
20. SELECT 7 ID, 300 EMP_ID,'NM2' EMP_NM,'OUT' VAL,'1500' TM
    
21. FROM DUAL
    
22. UNION ALL
    
23. SELECT 8 ID, 300 EMP_ID,'NM2' EMP_NM,'IN' VAL,'1530' TM
    
24. FROM DUAL )
    
25. select id,emp_id,emp_nm,max(val) val,max(tm) tm,max(valin) valin,max(tmin) tmin from (
    
26. select round(id/2) id,emp_id,emp_nm,decode(val,'OUT',val,null) val,decode(val,'OUT',TM,null) TM,
    
27. decode(val,'IN',val,null) valin,decode(val,'IN',TM,null) TMIN from t) group by id ,emp_id,emp_nm order by 1
    

复制代码