MySql好用技巧

Group_concat

完整语法如下：

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

SELECT * FROM testgroup 

SELECT id,GROUP_CONCAT(score) FROM testgroup GROUP BY id  

SELECT id,GROUP_CONCAT(DISTINCT score) FROM testgroup GROUP BY id  

SELECT id,GROUP_CONCAT(score ORDER BY score DESC) FROM testgroup GROUP BY id  

SELECT id,GROUP_CONCAT(score SEPARATOR ';') FROM testgroup GROUP BY id  

连续统计（没有的数据以0代替，以统计一周的数据为例）

以union来串联缺失的数据

SELECT count,date from (SELECT
         sum(SALES_AMT) count,
         DATE_FORMAT(DOS_DATE,'%Y-%m-%d') date
     FROM tg_dos_merch
     WHERE
         DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DOS_DATE
         GROUP BY DATE_FORMAT(DOS_DATE,'%Y-%m-%d') 
      UNION (SELECT 0, CURDATE())
      UNION (SELECT 0, DATE_SUB(CURDATE(), INTERVAL 1 DAY))
      UNION (SELECT 0, DATE_SUB(CURDATE(), INTERVAL 2 DAY))
      UNION (SELECT 0, DATE_SUB(CURDATE(), INTERVAL 3 DAY))
      UNION (SELECT 0, DATE_SUB(CURDATE(), INTERVAL 4 DAY))
      UNION (SELECT 0, DATE_SUB(CURDATE(), INTERVAL 5 DAY))
      UNION (SELECT 0, DATE_SUB(CURDATE(), INTERVAL 6 DAY)))F WHERE date>= DATE_SUB(CURDATE(), INTERVAL 6 DAY) GROUP BY date