sql 使用过的查询语句

sql多表联查，进行每天数据统计

##复杂

SELECT
  b.ds,
  substring_index(
    substring_index(a.key_words, '|', b.cotitle + 1),
    '|',
    -1
  ) course
FROM
  search_task a
  JOIN daily b on b.cotitle < (
    length(a.key_words) - length(REPLACE(a.key_words, '|', '')) + 1
  )

sql查询每日任务量（表中只有一条数据，进行多表联查，目标表的日期数据）

SELECT
  b.ds,(
    SELECT
      COUNT(0)
    FROM
      tousu.heimao_search_task
  ) as count_i
FROM
  mao_search a
  JOIN video_daily b
GROUP by
  ds

sql平均数

select ds,concat(
TRUNCATE(
(
(SELECT count(1)  FROM wx_account_stat where company_id > 0)/
(SELECT count(1)  FROM wx_account_stat where unit_nature='企业')
 )
 * 100,2),'%'
) as rate from wx_account_stat group by ds;

sql格式化日期

select DATE_FORMAT(update_time,'%Y-%m-%d') AS created from wx_accountc

sql字段添加

replace into video_collection_stat.video_daily (ds , cnt ,platform_name)

sql 计算每天增量（累计数据）

SELECT a.ds AS date_t,SUM(b.num) AS cum FROM
(SELECT sum(cnt) as num ,ds  FROM  video_daily GROUP BY ds)  a
JOIN
(SELECT sum(cnt) as num ,ds  FROM  video_daily GROUP BY ds)  b
ON a.ds >= b.ds GROUP BY a.ds

sql 去除空值进行计算

select  cotitle,  sum(cnt) as sum_int from  video_daily
where cotitle!='' GROUP BY  cotitle

sql 黑窗口时候使用（列显示）

查询：select * from t_copr_tort limit 1 \G;

sql （LEFT JOIN向上填充）

SELECT
  a.ds,
  COUNT(1)
FROM
(SELECT ds FROM yuqing_daily GROUP BY  ds) a
LEFT JOIN 
(SELECT DATE_FORMAT(beg_time, '%Y-%m-%d') AS ds FROM t_search_task) b 
ON a.ds >= b.ds
GROUP BY a.ds

sql 前后相减（join）

SELECT a.day , a.account_number - b.account_number as ram_sum FROM
(SELECT DATE_FORMAT(DATE_SUB(day,INTERVAL 1 DAY),'%Y-%m-%d') as day,account_number from wx_public) AS a
		 
JOIN 

(SELECT day,account_number from wx_public) AS b

ON a.day = b.day