sql - SQL Select Columns WHERE value IN GROUP BY Clause BY Distinct
问题描述
如果有一个分组的结果集。IE:
(结果集)
Id (bigint), UserId (int), Field1 (varchar), Field2 (varchar)
=============================================================
1, 22, 'Job', 'Teacher'
2, 22, 'Car', 'Mustang'
3, 22, 'Age', '35'
4, 47, 'Job', 'Clerk'
5, 47, 'Car', 'Corolla'
6, 47, 'Age', '23'
7, 85, 'Job', 'Banker'
8, 85, 'Car', 'Volkswagon'
9, 85, 'Age', '44'
如何根据唯一列(在本例中为 UserId)返回结果集并根据特定情况在 where 子句中获取字段?
匹配这个:
Id (bigint), UserId (int), Car (varchar), Job (varchar), Age (varchar)
======================================================================
1, 22, 'Mustang', 'Teacher', '35'
2, 47, 'Corolla', 'Clerk', '23'
3, 85, 'Volkswagon', 'Banker', '44'
我知道这是糟糕的数据库设计,但我正在使用旧应用程序。我尝试过基于单独的选择查询的联合或加入,但无济于事。
感谢任何帮助,因为我不精通 SQL。
解决方案
您可以尝试使用条件聚合和row_number()
窗口函数。
SELECT row_number() OVER (ORDER BY x.userid) id,
*
FROM (SELECT t.userid,
max(CASE t.field1
WHEN 'Car' THEN
t.field2
END) car,
max(CASE t.field1
WHEN 'Job' THEN
t.field2
END) job,
max(CASE t.field1
WHEN 'Age' THEN
t.field2
END) age
FROM elbat t
GROUP BY t.userid) x;
(顺便说一句,它是“大众”,而不是“大众”。)
推荐阅读
- sql-server - 事务日志已满(由于 NOTHING)...但此数据库处于简单恢复模式
- nginx - nginx proxy URl failing
- javascript - 编写递归搜索函数返回一个对象;
- tomcat - Alfresco 社区版“在此服务器上找不到 Alfresco 存储库”
- database - Looking for resources for designing tables to analyze user behavior on my company's ecommerce app/website
- java - Null pointer exception when trying to create table in sqlite database with Java
- performance - Why Server restarts are improving Load balancing?
- mysql - How to allow a column to be equal to only 1 of 3 values
- python - 从字符串中提取一定范围内的unicode字符
- python - How to writea file in %APPDATA% folder python