SQL语句

1、mysql数据库查上一周，本周，上一月，本月的数据

-- 本周
SELECT
	news_id,
	COUNT(news_id) AS num 
FROM
	news_follow 
WHERE
	YEARWEEK(
		date_format( follow_time, '%Y-%m-%d' ))= YEARWEEK(
	now())
GROUP BY
	news_id 
ORDER BY num DESC
	LIMIT 3


-- 上周
SELECT news_id FROM news where YEARWEEK(date_format(news_time,'%Y-%m-%d')) = YEARWEEK(now())-1
-- 上上周
SELECT news_id FROM news where YEARWEEK(date_format(news_time,'%Y-%m-%d')) = YEARWEEK(now())-2

-- 当月
SELECT 
	news_time
FROM
	news
WHERE
	date_format( news_time, '%Y-%m' )= date_format(
	now(),
	'%Y-%m')

-- 上月
select news_title,news_time from news where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

2、mysql按逗号分隔，取第一个

SELECT 	substring_index( news.stock_name, ',', 1 ) AS stock_name FROM news

3、在两个数据库中，取时间最大值的数据，并去重

         SELECT distinct
            a.news_title AS news_title,
            a.news_author AS news_author,
            a.news_time AS news_time,
            c.news_index AS news_index,
            c.forward_num AS forward_num,
            c.read_num AS read_num,
            b.stock_name AS stock_name,
            b.dq_price AS dq_price,
            b.dq_ratio AS dq_ratio
        FROM
            electric_system.news a
                LEFT JOIN
            electric_system.news_statistics c
            ON
                a.news_id = c.news_id
                LEFT JOIN
            message.stock_information b
            ON
                a.stock_name = b.stock_name
        WHERE b.dq_time = (select max(message.stock_information.dq_time) FROM message.stock_information)
          and a.news_id = "LocoLoco"

4、更新数据库中的ID，让ID成为UUID

1:
UPDATE company SET company_id= UUID() where company_name="MIRISE Technologies Corporation";

2:
UPDATE company SET company_id = (SELECT REPLACE(company_id,'-','')) WHERE company_name="蔚来汽车科技有限公司";


先1后2，生成的UUID是带"-"的，2是为了将"-",替代为""。