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