SQL 常用的SQL

1.根据时段统计，不同类型,并累加次数

SELECT 
  SUM(CASE click_type WHEN 1 THEN 1 ELSE 0 END) '曝光量',
  SUM(CASE click_type WHEN 2 THEN 1 ELSE 0 END) '点击量',
    SUM(CASE click_type WHEN 3 THEN 1 ELSE 0 END) '转换量',
  DATE_FORMAT(
    `create_time`,
    '%Y-%m-%d %H:00:00'
  )
FROM
  TAdvertising_report  GROUP BY DATE_FORMAT(`create_time`, '%Y-%m-%d %H')

2.根据用户生日日期，分组去重相同用户，并统计用户数

SELECT age_temp  age,COUNT(*) AS total FROM (
SELECT TIMESTAMPDIFF(YEAR, tu.birthday, CURDATE()) ,
CASE
WHEN TIMESTAMPDIFF(YEAR, tu.birthday, CURDATE()) <=25 THEN '25'
WHEN TIMESTAMPDIFF(YEAR, tu.birthday, CURDATE()) BETWEEN 26 AND 35 THEN '26-35' 
WHEN TIMESTAMPDIFF(YEAR, tu.birthday, CURDATE()) BETWEEN 36 AND 45 THEN '36-45' 
WHEN TIMESTAMPDIFF(YEAR, tu.birthday, CURDATE()) >45 THEN '36-45'
WHEN tu.birthday IS NULL THEN '其它' ELSE 0 END AS age_temp
FROM TUser tu INNER JOIN TAdvertising_report tr ON tu.id =tr.user_id GROUP BY tu.id 
)AS USER GROUP BY age_temp;

 

3.不同状态 分组 并 合并 统计

SELECT 
SUM(CASE WHEN state = 4 OR state = 6 THEN 1 ELSE 0 END) toBeSettled,
SUM(CASE state  WHEN 9 THEN 1 ELSE 0 END) settled,
SUM(s.`settlement_cost`) settlementCost,
DATE_FORMAT(wo.`update_time`, '%Y-%m')   FROM work_order wo 
LEFT JOIN `settlement_order_detail` s 
  ON wo.id = s.`order_id` WHERE 1 = 1  GROUP BY DATE_FORMAT(wo.`update_time`, '%Y-%m')
  ORDER BY DATE_FORMAT(wo.`update_time`, '%Y-%m')

4.根据分数统计不同司机，然后根据司机别名排序

SELECT (CASE WHEN score_safe >= 80 AND score_safe <= 100 THEN '優秀駕駛司機'
WHEN score_safe >= 60 AND score_safe <= 79 THEN '良好駕駛司機'
WHEN score_safe >= 40 AND score_safe <= 59 THEN '正常駕駛司機'
ELSE '危險駕駛司機' END) title, COUNT( 1 ) scoreProportion
FROM driving_behavior_score_safe
GROUP BY title 

ORDER BY CASE title WHEN '優秀駕駛司機' THEN 1
WHEN '良好駕駛司機' THEN 2
WHEN '正常駕駛司機' THEN 3
WHEN '危險駕駛司機' THEN 4 END ASC

5.关联子查询

 SELECT wo.id,
        CASE
        WHEN  wo.`state`= 7  THEN  1
        WHEN wo.`state`= 8 THEN 2
        WHEN  wo.`state`= 9 THEN 3
        END AS state,wo.sn 
        FROM `work_order` wo 
        LEFT JOIN   
        ( SELECT sor.*,sod.`order_id` FROM settlement_order_detail sod INNER JOIN settlement_order sor ON sod.settlement_id=sor.id AND sor.state !=4)
        so ON so.order_id=wo.id
       

6.批量查询并批量新增数据

INSERT INTO `alarm_data`(`car_id`,`device_sn`,`plate_number`,`create_time`)
SELECT `car_id`,`device_sn`,`plate_number`,NOW() FROM `adas_data` 

7.字符串拼接,多条数据拼接成一条,会有长度限制

SELECT GROUP_CONCAT(CONCAT("'",`device_sn`,"'")) FROM TDevice

SELECT GROUP_CONCAT("'",`device_sn`,"'") FROM TDevice