使用LIMIT和BETWEEN运算符的类似子查询的较短SQL语句

ignisruber 发表于 Dev

点火器

我在下面的代码中添加了子查询的结果，以获取一月份的数据。

子查询的建立方式相同。唯一更改的是BETWEEN运算符中指定的时间范围。

有没有编写此SQL语句的较短方法？因为我还想要其他月份的子查询，这样12个月的SQL将变得非常大。

所以最后，我想添加子查询以获取一月，二月，三月，四月等的一行。

（我正在使用PHP和MySQL。）

这是SELECT语句：

SELECT 
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-01 01:00:00' AND '2014-01-01 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-02 01:00:00' AND '2014-01-02 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-03 01:00:00' AND '2014-01-03 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-04 01:00:00' AND '2014-01-04 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-05 01:00:00' AND '2014-01-05 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-06 01:00:00' AND '2014-01-06 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-07 01:00:00' AND '2014-01-07 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-08 01:00:00' AND '2014-01-08 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-09 01:00:00' AND '2014-01-09 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-10 01:00:00' AND '2014-01-10 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-11 01:00:00' AND '2014-01-11 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-12 01:00:00' AND '2014-01-12 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-13 01:00:00' AND '2014-01-13 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-14 01:00:00' AND '2014-01-14 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-15 01:00:00' AND '2014-01-15 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-16 01:00:00' AND '2014-01-16 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-17 01:00:00' AND '2014-01-17 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-18 01:00:00' AND '2014-01-18 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-19 01:00:00' AND '2014-01-19 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-20 01:00:00' AND '2014-01-20 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-21 01:00:00' AND '2014-01-21 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-22 01:00:00' AND '2014-01-22 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-23 01:00:00' AND '2014-01-23 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-24 01:00:00' AND '2014-01-24 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-25 01:00:00' AND '2014-01-25 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-26 01:00:00' AND '2014-01-26 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-27 01:00:00' AND '2014-01-27 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-28 01:00:00' AND '2014-01-28 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-29 01:00:00' AND '2014-01-29 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-30 01:00:00' AND '2014-01-30 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) +
(
SELECT DaySum_1+DaySum_2+DaySum_3+DaySum_4+DaySum_5+DaySum_6+DaySum_7+DaySum_8+DaySum_9+DaySum_15
FROM   rysolarplus
WHERE DatumUhrzeit BETWEEN '2014-01-31 01:00:00' AND '2014-01-31 23:00:00'
ORDER BY DatumUhrzeit DESC
LIMIT 1
) AS january;

表摘录：

在此处输入图片说明

托马斯·克里默斯

该查询将采用每天的最大值，然后每月求和：

SELECT
  YEAR(TagTotalen.Tag),
  Month(TagTotalen.Tag),
  SUM(TagTotalen.TagTotal)
FROM
  (
    SELECT
      DATE(DatumUhrzeit) AS Tag,
      MAX(DaySum_1) + MAX(DaySum_2) AS TagTotal
    FROM
      rysolarplus
    GROUP BY
      DATE(DatumUhrzeit)
  ) AS TagTotalen
GROUP BY
  YEAR(TagTotalen.Tag),
  MONTH(TagTotalen.Tag)