select * from (
SELECT
datefield
FROM
calendar
WHERE
datefield >= DATE_FORMAT(
(
SELECT
min(local_date_time)
FROM
(
SELECT
min(local_date_time) AS local_date_time
FROM
datavalues
WHERE
site_id = 479
AND variable_id = 58
AND method_id = 1
UNION ALL
SELECT
min(local_date_time) AS local_date_time
FROM
datavalues
WHERE
site_id = 479
AND variable_id = 62
AND method_id = 1
) a
),
'%Y-%m-%d %H:00:00'
)
AND datefield <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')
ORDER BY
datefield DESC
LIMIT 0,
144
) c
查询结果为空
但是select * from (
SELECT
datefield
FROM
calendar
WHERE
datefield between DATE_FORMAT(
(
SELECT
min(local_date_time)
FROM
(
SELECT
min(local_date_time) AS local_date_time
FROM
datavalues
WHERE
site_id = 479
AND variable_id = 58
AND method_id = 1
UNION ALL
SELECT
min(local_date_time) AS local_date_time
FROM
datavalues
WHERE
site_id = 479
AND variable_id = 62
AND method_id = 1
) a
),
'%Y-%m-%d %H:00:00'
)
AND DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')
ORDER BY
datefield DESC
LIMIT 0,
144
) c
有144条记录;
说明:calendar 是每五分钟数据的时间表请教一下是怎么回事。mysql 版本是5.1.54,在5.6下边运行正常。初步判断跟mysql版本有关。有高手帮忙解一下惑吗?
SELECT
datefield
FROM
calendar
WHERE
datefield >= DATE_FORMAT(
(
SELECT
min(local_date_time)
FROM
(
SELECT
min(local_date_time) AS local_date_time
FROM
datavalues
WHERE
site_id = 479
AND variable_id = 58
AND method_id = 1
UNION ALL
SELECT
min(local_date_time) AS local_date_time
FROM
datavalues
WHERE
site_id = 479
AND variable_id = 62
AND method_id = 1
) a
),
'%Y-%m-%d %H:00:00'
)
AND datefield <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')
ORDER BY
datefield DESC
LIMIT 0,
144
) c
查询结果为空
但是select * from (
SELECT
datefield
FROM
calendar
WHERE
datefield between DATE_FORMAT(
(
SELECT
min(local_date_time)
FROM
(
SELECT
min(local_date_time) AS local_date_time
FROM
datavalues
WHERE
site_id = 479
AND variable_id = 58
AND method_id = 1
UNION ALL
SELECT
min(local_date_time) AS local_date_time
FROM
datavalues
WHERE
site_id = 479
AND variable_id = 62
AND method_id = 1
) a
),
'%Y-%m-%d %H:00:00'
)
AND DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')
ORDER BY
datefield DESC
LIMIT 0,
144
) c
有144条记录;
说明:calendar 是每五分钟数据的时间表请教一下是怎么回事。mysql 版本是5.1.54,在5.6下边运行正常。初步判断跟mysql版本有关。有高手帮忙解一下惑吗?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货