下面是给定的表。
----------------------------------
| 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 |
我想让我的查询做如下。
"显示该地区在两个给定日期之间出现一次以上的国家的名称。"
我尝试了下面的查询，但它给我一个错误。>#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 );
要将一个值与一个值列表进行比较，您需要使用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'
);
如果你想要一个国家，不要选择一个地区:
SELECT DISTINCT country
FROM (
SELECT country
FROM table
WHERE dateandtime > ' 13-01-2013'
AND dateandtime < '20-01-2013'
GROUP BY area
HAVING count(DISTINCT date) > 1
) areas;
你可能在找这个
SELECT country
FROM table1
WHERE date between ' 13-01-2013' AND '20-01-2013'
GROUP BY country
HAVING count(date) > 1 ;
演示