我有两张桌子。一个是:
+--------------------------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+--------------------------+-------------+---------+-----+------+
| facility_id | int(10) | NO | PRI | NULL |
| facility_name | varchar(30) | | | NULL |
| facility_model | varchar(25) | | | NULL |
+--------------------------+-------------+------+-----+---------+
另一个是:
+--------------------------+-------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+--------------------------+-------------+---------+-----+------+
| facility_id | int(10) | NO | PRI | NULL |
| facility_serial_number | varchar(30) | | | NULL |
+--------------------------+-------------+------+-----+---------+
不同的设施可以有相同的facility_serial_number (这是我们想要找出并修复的)。我想要获取其facility_serial_number多次出现的设施的所有信息。
我尝试使用group by,并使用:
select f1.facility_id, f1.facility_name, f1.facility_model, f2.facility_serial_number
from f1, f2 on f1.facility_id = f2.facility_id
group by f2.facility_serial_number
having count (facility_serial_number) > 2
这是错误的。我也尝试了其他方法,但没有成功。什么是正确的查询语法?
发布于 2012-09-17 14:01:12
显然不清楚您的sql的意图是什么,但正确的syntac应该是这样的
select f1.facility_id,
f1.facility_name,
f1.facility_model,
f2.facility_serial_number
from f1 INNER JOIN
f2 on f1.facility_id = f2.facility_id
group by f1.facility_id,
f1.facility_name,
f1.facility_model,
f2.facility_serial_number
having count (facility_serial_number) > 2
或
select f2.facility_serial_number
from f1 INNER JOIN
f2 on f1.facility_id = f2.facility_id
group by f2.facility_serial_number
having count (facility_serial_number) > 2
但如果我没理解错的话,你可能会想试试
select f1.facility_id,
f1.facility_name,
f1.facility_model,
f2.facility_serial_number
from f1 INNER JOIN
f2 on f1.facility_id = f2.facility_id
WHERE f2.facility_serial_number IN (
select f2.facility_serial_number
from f1 INNER JOIN
f2 on f1.facility_id = f2.facility_id
group by f2.facility_serial_number
having count (facility_serial_number) > 2
)
发布于 2012-09-17 14:01:40
尝试关注Ref
SELECT f1.facility_id, f1.facility_name, f1.facility_model, f2.facility_serial_number
FROM f1 LEFT JOIN f2 on f1.facility_id = f2.facility_id
GROUP BY f2.facility_serial_number
HAVING count (f2.facility_serial_number) > 2
发布于 2012-09-17 14:12:07
select * form f1 from where facility_id in(
select facility_id where f2 group by facility_id
having count(facility_serial_number) > 2))
https://stackoverflow.com/questions/12453786
复制相似问题