MySQL -组中的条件选择

Question

我有一个简单的表，其中有几行，我想按条件对它们进行分组，并仅在条件为真时选择id_room。问题是，即使有一行的日期列year_month正确，条件也总是为假。

以下是模式：

CREATE TABLE tbl_account_room (
  `id` int, 
  `year_month` date, 
  `value` int,
  `id_room` int
);

INSERT INTO tbl_account_room
    (`id`, `year_month`, `value`, `id_room`)
VALUES
    (1, '2016-08-01', 1, 300),
    (2, '2016-09-01', 2, 300),
    (3, '2016-10-01', 3, 300);

and here查询：

SELECT 
  (case when '2016-10-01' = ar.year_month then ar.value else 0 end) as total
FROM tbl_account_room AS ar
WHERE ar.year_month >= "2016-08-01"
  AND ar.year_month <= "2016-11-01"
  and ar.id_room = '300'
GROUP BY ar.id_room
LIMIT 10

这是SQL Fiddle

在列total中，我得到了0，我想得到值3，因为year_month是2016-10-01。为什么会发生这种情况？

Answer 1

您当然不需要该CASE条件，并将该条件包含在WHERE子句中，如下所示

SELECT 
ar.value as total,
GROUP_CONCAT(ar.year_month)
FROM tbl_account_room AS ar
WHERE ar.year_month = '2016-10-01'
GROUP BY ar.id_room;

Answer 2

不知道为什么你想要这样的结果，这里你可以使用self join来实现：

SELECT 
  MAX(t1.value) as total,
  GROUP_CONCAT(ar.year_month)
FROM tbl_account_room AS ar
LEFT JOIN tbl_account_room AS t1
ON ar.id_room = t1.id_room
AND ar.year_month = t1.year_month
AND t1.year_month = '2016-10-01'
WHERE ar.year_month >= "2016-08-01"
  AND ar.year_month <= "2016-11-01"
  and ar.id_room = '300'
GROUP BY ar.id_room
LIMIT 10;

这是。