SQL查询

最新推荐文章于 2024-07-26 00:24:43 发布

a_cup_lemon

最新推荐文章于 2024-07-26 00:24:43 发布

阅读量47

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/a_cup_lemon/article/details/134458455

版权

SELECT days.date,time.assess_value from
(SELECT
	date_format(
		date_add( DATE_ADD( curdate(), INTERVAL - DAY ( curdate())+ 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 0 ) DAY ),
		'%Y-%m-%d' 
	) date 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < DAY (
		last_day(
		DATE_ADD( curdate(), INTERVAL - DAY ( curdate())+ 1 DAY ))) 
ORDER BY
	help_topic_id)
AS days
LEFT JOIN (
SELECT DATE(t_health_assess.create_time) as date,t_health_assess.assess_value
FROM t_health_assess
WHERE YEARWEEK(create_time, 1) = YEARWEEK(CURDATE(), 1) 
AND type='weight'and base_id IN(
SELECT id FROM t_health_base_information WHERE phone=12451))AS time
ON time.date=days.date

//一周
SELECT days.days,time.assess_value from (
SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 0 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 1 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 2 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 3 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 4 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 5 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 6 DAY) as days ORDER BY days asc)AS days
LEFT JOIN (
SELECT DATE(t_health_assess.create_time) as date,t_health_assess.assess_value
FROM t_health_assess
WHERE YEARWEEK(create_time, 1) = YEARWEEK(CURDATE(), 1) 
AND type='weight'and base_id IN(
SELECT id FROM t_health_base_information WHERE phone=12451))AS time
ON time.date=days.days



SELECT
	DATE_FORMAT( DATE_SUB( DATE_FORMAT( NOW(), '%Y-%m-%d' ), INTERVAL ( -( @i := @i + 1 ) ) HOUR ), '%Y-%m-%d %H' ) AS 'time' 
FROM
	(
	SELECT
		a 
	FROM
		( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' ) AS a
		JOIN ( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS b ON 1 
		) AS b,(
	SELECT
		@i :=- 1 
	) AS i


SELECT DATE(t_health_assess.create_time) AS date, t_health_assess.assess_value
FROM t_health_assess
WHERE type='weight'and base_id IN(
SELECT id FROM t_health_base_information WHERE phone=12451)
ORDER BY date desc
GROUP BY date


SELECT days.days,IFNULL(time.assess_value,'0kg') AS value FROM (
SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 0 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 1 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 2 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 3 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 4 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 5 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 6 DAY) as days ORDER BY days asc)AS days
LEFT JOIN (
SELECT DATE(t_health_assess.create_time) as date,t_health_assess.assess_value
FROM t_health_assess
WHERE YEARWEEK(create_time, 1) = YEARWEEK(CURDATE(), 1) 
AND type='weight'and base_id IN(
SELECT id FROM t_health_base_information WHERE phone=12451)
GROUP BY date)AS time
ON time.date=days.days

//24小时
SELECT hours.time,IFNULL(value,'0kg')as value FROM(
SELECT
	DATE_FORMAT( DATE_SUB( DATE_FORMAT( NOW(), '%Y-%m-%d' ), INTERVAL ( -( @i := @i + 1 ) ) HOUR ), '%Y-%m-%d %H' ) AS 'time' 
FROM
	(
	SELECT
		a 
	FROM
		( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' ) AS a
		JOIN ( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS b ON 1 
		) AS b,(
	SELECT
		@i :=- 1 
	) AS i) as hours
LEFT JOIN(
SELECT DATE(t_health_assess.create_time) AS date, t_health_assess.assess_value AS value
FROM t_health_assess
WHERE type='weight'and base_id IN(
SELECT id FROM t_health_base_information WHERE phone=12451)
GROUP BY date)AS time
ON
time.date=hours.time