首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >SQL Group By and SQL

SQL Group By and SQL
EN

Stack Overflow用户
提问于 2012-09-17 13:54:48
回答 3查看 93关注 0票数 0

我有两张桌子。一个是:

代码语言:javascript
运行
复制
+--------------------------+-------------+------+-----+---------+
| Field                    | Type        | Null | Key | Default |  
+--------------------------+-------------+---------+-----+------+ 
| facility_id              | int(10)     | NO   | PRI | NULL    |  
| facility_name            | varchar(30) |      |     | NULL    |                 
| facility_model           | varchar(25) |      |     | NULL    |                 
+--------------------------+-------------+------+-----+---------+ 

另一个是:

代码语言:javascript
运行
复制
+--------------------------+-------------+------+-----+---------+
| 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,并使用:

代码语言:javascript
运行
复制
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

这是错误的。我也尝试了其他方法,但没有成功。什么是正确的查询语法?

EN

回答 3

Stack Overflow用户

发布于 2012-09-17 14:01:12

显然不清楚您的sql的意图是什么,但正确的syntac应该是这样的

代码语言:javascript
运行
复制
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

代码语言:javascript
运行
复制
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

但如果我没理解错的话,你可能会想试试

代码语言:javascript
运行
复制
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
                                    )
票数 0
EN

Stack Overflow用户

发布于 2012-09-17 14:01:40

尝试关注Ref

代码语言:javascript
运行
复制
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
票数 0
EN

Stack Overflow用户

发布于 2012-09-17 14:12:07

代码语言:javascript
运行
复制
select * form f1 from where facility_id in(
select facility_id where f2 group by facility_id
 having count(facility_serial_number) > 2))
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/12453786

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档