工作中常用的SQL

工作中偶尔用但是又很容易忘记的sql

简书地址

现在项目中用的最多的是免费的mysql，因此下面的sql只保证在mysql上奏效。

1、时间戳格式化 & case-when

查询系统发放的红包的有效时间和状态：

SELECT
    a.amount 红包面额,
    a.member_phone 领取人,
    a.amount_range 使用门槛,
    FROM_UNIXTIME(
        LEFT ( a.rule_start, 10 ),
        '%Y-%m-%d %T' 
    ) 生效时间,
    FROM_UNIXTIME(
        LEFT ( a.rule_end, 10 ),
        '%Y-%m-%d %T' 
    ) 过期时间,
    a.send_time 发送时间,
CASE
        a.`status` 
        WHEN '10' THEN
        '未使用' 
        WHEN '20' THEN
        '已使用' 
        WHEN '30' THEN
        '将过期' 
        WHEN '40' THEN
        '已过期' 
        WHEN '50' THEN
        '已占用' ELSE '其他' 
    END AS '当前状态',
    a.useage_range_name 使用范围,
    b.remarks 活动名称,
    b.rule_start 规则开始时间,
    b.rule_end 规则截止时间,
    a.`use_date` AS 使用或过期时间 
FROM
    `hl_benefit_redpackage_user` a
    INNER JOIN `hl_benefit_redpackage` b ON a.redpackage_id = b.redpackage_id 
WHERE
    a.redpackage_id != '' 
    AND ! ISNULL( a.redpackage_id );

2、group by的合理使用

一般地，select后面只能是group by 的字段或者跟些聚合函数，比如查询某天以后没天注册用户数量：

SELECT
    DATE_FORMAT( member_regist_time, '%Y-%m-%d' ) AS '日期',
    COUNT( 1 ) AS '用户数' 
FROM
    hl_member 
WHERE
    DATE_FORMAT( member_regist_time, '%Y-%m-%d' ) >= '2015-08-20' 
GROUP BY
    DATE_FORMAT( member_regist_time, '%Y-%m-%d' )

这种查询能出的字段有限，那么如果要查出更多，同时group呢？

select * from user where id in(
   select min(id) from user where name = 'Java3y' and pv = 20 and time='7-25' group by name,pv,time;
)

将查询后的结果作为子查询，放在外部查询的where 子句后，这样外部查询是可以select 出其他字段的。
还有一个常见场景那就是查询一个表某些字段重复的数据

SELECT
    * 
FROM
    hl_custom_item a 
WHERE
    ( a.custom_item_name, a.parent_id ) IN (
SELECT
    custom_item_name,
    parent_id 
FROM
    hl_custom_item 
GROUP BY
    custom_item_name,
    parent_id 
HAVING
    COUNT( * ) > 1 
    ) 
GROUP BY
    custom_item_name;

3、select update

更新会员领取红包表的手机号为会员表的手机号

UPDATE `hl_benefit_redpackage_user` a

SET a.member_phone = (SELECT b.member_phone_no FROM `hl_member` b where a.member_id=b.member_Id)

where a.member_id != NULL;

4、 update select

部门表示个树结构的表，里面有几万条数据，更新时会有效率问题，原SQL如下：

UPDATE t_department d set d.parent_id=dept_id ,d.update_time=NOW()
WHERE d.dept_code in (SELECT t.dept_code FROM  t_idm_department t
where t.parent_code=NEW.dept_code);

优化SQL如下

UPDATE t_department d 
JOIN (SELECT t.dept_code FROM  t_idm_department t
where t.parent_code=NEW.dept_code) e on d.dept_code = e.dept_code 
set d.parent_id=dept_id ,d.update_time=NOW()

5、insert select

将从患者表查出的数据某些字段插入到一个新的表

INSERT INTO t_wx_user (id, openid, gender, nick_name, headimgurl, create_time)
SELECT patient_id, openid, gender, nick_name, headimgurl, NOW()
FROM t_patient;

参考

在工作中常用到的SQL