mysql 生成日历

最新推荐文章于 2024-07-19 03:23:53 发布

weixin_34409822

最新推荐文章于 2024-07-19 03:23:53 发布

阅读量575

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：https://my.oschina.net/u/3238650/blog/3020394

2019独角兽企业重金招聘Python工程师标准>>>

SELECT
	max( CASE dw WHEN 2 THEN dm END ) AS Mo,
	max( CASE dw WHEN 3 THEN dm END ) AS Tu,
	max( CASE dw WHEN 4 THEN dm END ) AS We,
	max( CASE dw WHEN 5 THEN dm END ) AS Th,
	max( CASE dw WHEN 6 THEN dm END ) AS Fr,
	max( CASE dw WHEN 7 THEN dm END ) AS Sa,
	max( CASE dw WHEN 1 THEN dm END ) AS Su 
FROM
	(
	SELECT
		DATE_FORMAT( dy, '%u' ) wk,
		DATE_FORMAT( dy, '%d' ) dm,
		DATE_FORMAT( dy, '%w' ) + 1 dw 
	FROM
		(
		SELECT
			DATE_ADD( dy, INTERVAL t500.id - 1 DAY ) dy,
			x.mth 
		FROM
			(
			SELECT
				DATE_ADD(
					CURRENT_DATE,
					INTERVAL - DAYOFMONTH( CURRENT_DATE ) + 1 DAY 
				) dy,
				DATE_FORMAT( CURRENT_DATE, '%m' ) mth 
			FROM
			DUAL 
			) x,
			t500 
		WHERE
			t500.id <= 31 
			AND DATE_FORMAT(
				DATE_ADD( dy, INTERVAL t500.id - 1 DAY ),
				'%m' 
			) = mth 
		) y 
	) z 
GROUP BY
	wk 
ORDER BY
	wk

转载于:https://my.oschina.net/u/3238650/blog/3020394