SQL used query statements

sql Multiple tables associated query , Carry out daily data statistics

## complex 

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 Query daily task volume （ There is only one data in the table , Perform multi table associated query , Date data of target table ）

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 The average

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=' Enterprises ')
 )
 * 100,2),'%'
) as rate from wx_account_stat group by ds;

sql Format date

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

sql Field add

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

sql Calculate the daily increment （ Cumulative data ）

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 Remove null values for calculation

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

sql Use when the window is black （ Columns show ）

 Inquire about ：select * from t_copr_tort limit 1 \G;

sql （LEFT JOIN Fill up ）

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 Subtraction before and after （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