php - 从具有多行的嵌套查询中获取选择

Question

下面是给定的表格。

----------------------------------
| area   |  country  |   date     |  
----------------------------------
| a1     |     c1    | 13-01-2013 | 
----------------------------------
| a2     |     c2    | 06-01-2013 | 
----------------------------------
| a3     |     c1    | 12-01-2013 | 
----------------------------------
| a4     |     c2    | 10-01-2013 | 
----------------------------------
| a5     |     c3    | 13-01-2013 | 
----------------------------------
| a1     |     c1    | 13-01-2013 | 
----------------------------------
| a2     |     c2    | 06-01-2013 | 
----------------------------------
| a3     |     c1    | 12-01-2013 | 
----------------------------------
| a4     |     c2    | 10-01-2013 | 
----------------------------------
| a5     |     c3    | 13-01-2013 | 

我希望我的查询执行以下操作。

“显示在 2 个给定日期之间该地区多次出现的国家/地区名称。”

我尝试了以下查询，但它给了我一个错误。 > #1242 - 子查询返回超过 1 行

SELECT country
FROM table
WHERE area = (
SELECT area
FROM table
WHERE dateandtime > ' 13-01-2013'
AND dateandtime < '20-01-2013'
GROUP BY area
HAVING count(DISTINCT date) > 1 );

Answer 1

要将值与值列表进行比较，您需要使用 IN。我还做了一些更改来修复您的语法。

SELECT country
FROM table
WHERE area IN (
SELECT area
FROM table
WHERE dateandtime > ' 13-01-2013'
AND dateandtime < '20-01-2013'
GROUP BY area, country, `date`
HAVING count(date) > 1 )
);

我相信查询也可以简化为:

SELECT country
FROM table
GROUP BY area, country, `date`
HAVING count(date) > 1
AND dateandtime BETWEEN '14-01-2013' AND '19-01-2013'
);