left join时的on和whereLIMIT的用法OFFSET的用法case...when...用法with as用法left join时的on和where# 表格
ID Uname Price BuyDate
1 张三 180 2017-12-1
2 张三 280 2017-12-7
3 李四 480 2017-12-10
4 李四 280 2017-12-11
5 王武 280 2017-12-1
6 王武 880 2017-12-11
7 王武 380 2017-12-15
# 想要
ID Uname Price BuyDate
2 张三 280 2017-12-7
3 李四 480 2017-12-10
6 王武 880 2017-12-11
# 语句
SELECT * FROM (
SELECT *,ROW_NUMBER()OVER(PARTITION BY Uname ORDER BY Price DESC ) AS rn FROM #t
) AS t WHERE t.rn=1
# 使用降序或者升序配合n=1，可以选择最小或者最大的
# 配合类似n<=5，可以选择最小或者最大的5个
需要遵循以下顺序：
| 子句 | 说明 | 是否必须使用 |
|---|---|---|
| SELECT | 要返回的列或者表达式 | 是 |
| FROM | 从中检索数据的表 | 仅在从表中选择数据时使用 |
| WHERE | 行级过滤 | 否 |
| GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
| HAVING | 组级过滤 | 否 |
| ORDER BY | 输出排序顺序 | 否 |
| LIMIT | 要检索的行数 | 否 |
LIMIT的用法select * from tableName limit i,ni：查询结果的索引值，默认从n开始n：查询结果返回的数量--检索前10行数据，显示1-10条数据
select * from Customer LIMIT 10;
--检索从第2行开始，累加10条id记录，共显示id为2....11
select * from Customer LIMIT 1,10;
--检索从第6行开始向前加10条数据，共显示id为6,7....15
select * from Customer limit 5,10;
--检索从第7行开始向前加10条记录，显示id为7,8...16
select * from Customer limit 6,10;
OFFSET的用法OFFSET nn：表示跳过n个记录-- 跳过第一个记录
select * from article OFFSET 1
-- 跳过第一个记录，提取接下来的3个记录
-- 当LIMIT和OFFSET联合使用的时候，limit后面只能有一个参数
select * from article LIMIT 3 OFFSET 1
-- 跳过第一个记录，从index=1开始，提取接下来的3个记录
select* from article LIMIT 1,3
case...when...用法条件表达式函数：
CASE WHEN condition THEN result
[WHEN...THEN...]
ELSE result
END
例子：
SELECT
STUDENT_NAME,
(CASE WHEN score < 60 THEN '不及格'
WHEN score >= 60 AND score < 80 THEN '及格' ---不能连续写成 60<=score<80
WHEN score >= 80 THEN '优秀'
ELSE '异常' END) AS REMARK
FROM
TABLE
参考这里：Percentage from Total SUM after GROUP BY SQL Server：
--- count based
--- 要加和的是总entry数目
SELECT
t.device_model,
COUNT(t.device_model) AS num,
COUNT(t.device_model)/SUM(COUNT(t.device_model)) OVER () AS Percentage
--- sum based
--- 要加和是另外一列
SELECT P.PersonID, SUM(PA.Total),
SUM(PA.Total) * 100.0 / SUM(SUM(PA.Total)) OVER () AS Percentage
参考这里，注意不用于直接sql中的函数:
--方法1: from_unixtime+ unix_timestamp
--20171205转成2017-12-05
select from_unixtime(unix_timestamp('20171205','yyyymmdd'),'yyyy-mm-dd') from dual;
--2017-12-05转成20171205
select from_unixtime(unix_timestamp('2017-12-05','yyyy-mm-dd'),'yyyymmdd') from dual;
--方法2: substr + concat
--20171205转成2017-12-05
select concat(substr('20171205',1,4),'-',substr('20171205',5,2),'-',substr('20171205',7,2)) from dual;
--2017-12-05转成20171205
select concat(substr('2017-12-05',1,4),substr('2017-12-05',6,2),substr('2017-12-05',9,2)) from dual;
with as用法--后面就可以直接把query_name1，query_name2当做表格来用了
WITH query_name1 AS (
SELECT ...
)
, query_name2 AS (
SELECT ...
FROM query_name1
...
)
SELECT ...
参考：SQL书写规范
-- 获取1-2号的新设备
-- SET时不用添加引号，使用时添加引号
SET date1 = 20180701;
SET date2 = 20180702;
SET app = app_name;
WITH new_did AS (
SELECT
p_date,
device_id
FROM
your_table
WHERE
p_date BETWEEN '${hiveconf:pdate1}'
AND '${hiveconf:pdate2}'
AND product = '${hiveconf:app}'
AND is_today_new = 1
)
-- 设置变量名称，放在最前面
SET pdate1 = 20200701;
SET pdate1 = 20200702;
-- DROP TABLE IF EXISTS `save_table_name`; -- 不允许有drop操作
-- CREATE TABLE IF NOT EXISTS `save_table_name ` AS -- 建表时放在这with...as前
WITH new_did AS (
-- 每日新设备
SELECT
p_date,
device_id
FROM
tA
WHERE
p_date BETWEEN '${hiveconf:pdate1}'
AND '${hiveconf:pdate2}'
)
INSERT OVERWRITE TABLE `save_table_name` -- 更新表时放在select前
SELECT
tA.p_date,
......
SELECT split('919.99&usd', '&')[0],split('919.99&usd', '&')[1],split('919.99&usd', '&')[2]
# 切分的结果index从0开始，不是1
_c0 _c1 _c2
919.99 usd
参考这里：
-- 字段信息
[{"userId":89224,"contactName":"","contactPhone":"+","originPhone":"+"},
{"userId":89223,"contactName":"","contactPhone":"+","originPhone":""},
{"userId":89221,"contactName":"","contactPhone":"+","originPhone":"+"}]
-- sql
select user_id, get_json_object(contacts,'$[*].userId') as cons
from XXXX
-- 提取后
user_id cons
1 107521212 [8397213,85,7418687,304388953,198708241,148,111920,5285539,97773639]