SQL收集oracle 排序分页 关联update

1、GROUP_CONCAT(d.name ORDER BY d.type) ，SQL分组后拼接内容排序

2、oracle 先排序后查询前N条：

select ril.*

from (select ril.*, row_number() over (order by t_stamp desc) as seqnum

      from raceway_input_labo ril

     ) ril

where seqnum = 1;

-- 错误写法
SELECT * FROM receipt r WHERE ROWNUM<10 ORDER BY r.SERIALKEY ;
-- row_number函数
SELECT a.* FROM (
SELECT r.*,ROW_NUMBER() OVER (ORDER BY r.SERIALKEY) AS rnum FROM RECEIPT r ) a WHERE rnum<10;
-- fetch 
SELECT * FROM RECEIPT r ORDER BY r.SERIALKEY FETCH FIRST 10 ROWS ONLY;
-- rownum 子查询（常用省略代码）

select * from（
SELECT * FROM RECEIPT r ORDER BY r.SERIALKEY） a
ROWNUM<10 

3、oracle 关联 update 几种写法

1）mysql 写法

UPDATE table1
INNER JOIN table2 ON table1.value = table2.DESC
SET table1.value = table2.CODE
WHERE table1.UPDATETYPE='blah';

2）oracle写法 一：

UPDATE table1 SET table1.value = (SELECT table2.CODE
                                  FROM table2 
                                  WHERE table1.value = table2.DESC)
WHERE table1.UPDATETYPE='blah'
AND EXISTS (SELECT table2.CODE
            FROM table2 
            WHERE table1.value = table2.DESC);

3）oracle 写法二：

UPDATE 
(SELECT table1.value as OLD, table2.CODE as NEW
 FROM table1
 INNER JOIN table2
 ON table1.value = table2.DESC
 WHERE table1.UPDATETYPE='blah'
) t
SET t.OLD = t.NEW

前提更新关联的字段必须为主键

4）oracle 写法三：

MERGE
INTO    table1 trg
USING   (
        SELECT  t1.rowid AS rid, t2.code
        FROM    table1 t1
        JOIN    table2 t2
        ON      table1.value = table2.DESC
        WHERE   table1.UPDATETYPE='blah'
        ) src
ON      (trg.rowid = src.rid)
WHEN MATCHED THEN UPDATE
    SET trg.value = code;

5）oracle 写法四：

update some_table s
set   (s.col1, s.col2) = (select x.col1, x.col2
                          from   other_table x
                          where  x.key_value = s.key_value
                         )
where exists             (select 1
                          from   other_table x
                          where  x.key_value = s.key_value
                         )

 

 

注意：子查询返回的数据必须是明确的，也就最好关联字段用主键

 

-- excel 拼接SQL 
 -- 1、单引号：="'"&A1&"'," 2、拼接双引号：=""""&A2&""""&":"&"，"