SQL分组依据 - 选择两列

我有一个包含以下几列的用户表:SQL分组依据 - 选择两列

| User_ID (int) | Name (varchar) | Age (int) | Experience_Level (int) | 

我想创建一个SQL查询输出所有谁在不是唯一的人的ID年龄和经验的结合。

我迄今为止代码:

SELECT Count(*), User_ID FROM Users 

GROUP BY Age,Experience_Level

HAVING Count(*) > 1

显然,这是不完整的,这组非唯一的用户,但它不会告诉我,他们所有的User_IDs的。

在此先感谢您的帮助!

回答:

这里是一个否定的逻辑查询:

SELECT * 

FROM Users

WHERE UserID not in

(

SELECT MIN(UserID)

FROM Users

GROUP BY Age, Experience_Level

HAVING COUNT(*) = 1

)

回答:

由于您想获得关于多个用户组的信息,您希望如何返回此数据?在包含逗号分隔的user_id值列表的字符串中?

您没有用您使用的SQL数据库品牌标记您的问题。

如果你使用MySQL或SQLite的,您可以使用内置的GROUP_CONCAT()功能:

SELECT Count(*), GROUP_CONCAT(User_ID) AS User_List FROM Users 

GROUP BY Age,Experience_Level

HAVING Count(*) > 1

默认情况下,GROUP_CONCAT()与逗号分隔值。如果您想以其他方式格式化,请参阅手册。

还有其他SQL供应商的解决方案。这个问题已经提出了许多次在Stack Overflow:

  • Simulating group_concat MySQL function in MS SQL Server 2005?
  • Group_concat MySQL function’s equivalent in Oracle
  • Postgresql GROUP_CONCAT equivalent?
  • group_concat in informix

回答:

SELECT t.User_ID, t.Age, t.Experience_Level 

FROM Users t INNER JOIN

(SELECT Age, Experience_Level

FROM Users

GROUP BY Age, Experience_Level

HAVING Count(*) > 1) d ON t.Age = d.Age AND t.Experience_Level = d.Experience_Level

测试脚本:

create table Users (

User_ID int,

Name varchar(50),

Age int,

Experience_Level int

)

insert into Users (User_ID, Name, Age, Experience_Level) values (1, 'A', 33, 1)

insert into Users (User_ID, Name, Age, Experience_Level) values (2, 'B', 37, 1)

insert into Users (User_ID, Name, Age, Experience_Level) values (3, 'C', 33, 1)

insert into Users (User_ID, Name, Age, Experience_Level) values (4, 'D', 35, 2)

insert into Users (User_ID, Name, Age, Experience_Level) values (5, 'E', 33, 1)

insert into Users (User_ID, Name, Age, Experience_Level) values (6, 'F', 35, 2)

insert into Users (User_ID, Name, Age, Experience_Level) values (7, 'G', 18, 1)

回答:

从理论上讲,你要的是这样的事情,但遗憾的是SQL Server不允许它:

SELECT * FROM Users 

WHERE (Age, Experience_Level) IN

(

SELECT Age, Experience_Level

FROM Users

GROUP BY Age,Experience_Level

HAVING Count(*) > 1

)

因此,相反,你不得不接受连结到子查询:

SELECT Users.* FROM Users 

INNER JOIN

(

SELECT Age, Experience_Level

FROM Users

GROUP BY Age,Experience_Level

HAVING Count(*) > 1

) subq

ON Users.Age = subq.Age

AND Users.Experience_Level = subq.Experience_Level

以上是 SQL分组依据 - 选择两列 的全部内容, 来源链接: utcz.com/qa/260515.html

回到顶部