mysql - Mysql通过合并多列创建新列并显示结果
问题描述
我正在处理一个数据集:'data_1',通过将多个列合并到这个单列及其值来创建一个新列'Category'。
例如,data_1:
user_id | family | friend | roommate | college
===============================================
1002345 | 1 | 0 | 1 | 0
-----------------------------------------------
1002346 | 0 | 1 | 0 | 1
-----------------------------------------------
... | ... | ... | ... | ...
------------------------------------------------
我在 Mysql 中尝试过“Case When”或“Unpivot”功能,但它们不起作用。
select
user_id,
category
from data_1
unpivot (category for col_name in (
data_1.family,
data_1.friend,
data_1.roommate,
data_1.college)
)
预期输出表:
user_id | Category | Value
============================
1002345 | Family | 1
----------------------------
1002345 | Friend | 0
----------------------------
1002345 | Roommate | 1
----------------------------
1002345 | College | 0
----------------------------
1002346 | Family | 0
----------------------------
1002346 | Friend | 1
----------------------------
1002346 | Roommate | 0
----------------------------
1002346 | College | 1
----------------------------
... | ... | ...
----------------------------
谢谢!
解决方案
我们可以使用UNION ALL
集合运算符来组合来自多个查询的结果。
我们可以做一个JOIN
操作,从表中的每一行生成四行。像这样的东西:
SELECT t.user_id
, q.category AS `Category`
, CASE q.category
WHEN 'Family' THEN t.family
WHEN 'Friend' THEN t.friend
WHEN 'Roommate' THEN t.roommate
WHEN 'College' THEN t.college
END AS `Value`
FROM data_1 t
CROSS
JOIN ( SELECT 'Family' AS category
UNION ALL SELECT 'Friend'
UNION ALL SELECT 'Roommate'
UNION ALL SELECT 'College'
) q
ORDER
BY t.user_id
, q.category='College'
, q.category
或者,我们可以这样做:
( SELECT t1.user_id
, 'Family' AS `Category`
, t1.family AS `Value`
FROM data_1 t1
)
UNION ALL
( SELECT t2.user_id
, 'Friend'
, t2.friend
FROM data_1 t2
)
UNION ALL
( SELECT t3.user_id
, 'Roommate'
, t3.roommate
FROM data_1 t3
)
UNION ALL
( SELECT t4.user_id
, 'College'
, t4.college
FROM data_1 t4
)
ORDER BY user_id, `Category`='College',`Category`
推荐阅读
- websocket - 我应该如何使用 locust 为 WebSocket 编写压力测试
- jquery - ElementUI - 在 el-select 中无法转换为空格
- android - 由于 PackageList 自动生成的文件,React Native FBSDKPackage 在 React Native 0.60 版本上链接错误
- templates - 使用 Mixin 类扩展 Groovy MarkupTemplateEngine
- spring-boot - @Validated 如何在常用方法上工作?
- c# - 使用 TPL 数据流块处理异常
- mysql - 更新-sql-查询
- javascript - 如何将jquery中的值打印到html文本中?
- laravel - 计算 JSON 数组中相同值的数量并将其转换为字符串
- javascript - 如何将 div 内容以字符串格式复制到剪贴板