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&""""&":"&","