|
一、分组并给每组排序
select row_number() over(partition by '分组字段' order by '排序字段' desc ) from '表名';
二、关联两个sql的查询结果
-- union在连接查询的两张表的时候，会自动去除 重复的数据。
-- union all 在连接查询的时候，只是简单的将两张表中的数据进行连接，不会去除重复的数据。
sql1 union sql2;
--例如
select * from test where name like '%张%'
union
select * from test where name like '%李%'
建议：单表链接查询的话使用union的效率比连接查询要高。
三、case when 用法
case
when sex = '1' then '男'
when sex = '2' then '女'
else '未知' end
--例如 与 group by 结合
SELECT country,
SUM( CASE WHEN sex = '1' THEN population ELSE 0 END), --男性人口
SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) --女性人口
FROM Table_A GROUP BY country;
-- 与 distinct 结合
select
count(distinct case when photo='1' then id else null end ）photo1_count,
count(distinct case when photo='2' then id else null end ）photo2_count
from photos;
-- 与 in EXISTS
SELECT keyCol,
CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B ) THEN 'Matched'
ELSE 'Unmatched' END Label
FROM tbl_A;
SELECT keyCol,
CASE WHEN EXISTS ( SELECT * FROM tbl_B WHERE tbl_A.keyCol = tbl_B.keyCol ) THEN 'Matched' ELSE 'Unmatched' END Label
FROM tbl_A;
四、分组
group by
--例如 查找的字段一定要在group by 后面
select p.project_id from project p group by p.project_id;
五、排序
order by
--例如
select * from test order by createTime;
六、当记录不存在时 insert,当记录存在时 update
---mysql
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
--Oracle Merge
MERGE INTO T T1 USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a) WHEN MATCHED THEN
UPDATE SET T1.b = T2.b WHEN NOT MATCHED
THEN INSERT (a,b) VALUES(T2.a,T2.b);
| 
转播
