SQL - 选择值以成为列值？

作者: 無口君
发布时间: 2024-12-17 09:06:29 (1月前)
转自：

3 条回复

0#
回复此人
哦豁 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 条件聚合应该做的伎俩： </p> <pre> <code> SELECT u.id, MAX(CASE WHEN cv.field_id = 'first_name' THEN m.value END) first_name, MAX(CASE WHEN cv.field_id = 'last_name' THEN m.value END) last_name FROM user u INNER JOIN mapping m ON m.user_id = u.id INNER JOIN Column_Values cv ON cv.id = m.field_id GROUP BY u.id </code> </pre> <P> 注意：如果您需要的只是用户ID及其名字和姓氏，那么您不需要引入 <code> user </code> 表。只是： </p> <pre> <code> SELECT m.user_id, MAX(CASE WHEN cv.field_id = 'first_name' THEN m.value END) first_name, MAX(CASE WHEN cv.field_id = 'last_name' THEN m.value END) last_name FROM mapping m ON INNER JOIN Column_Values cv ON cv.id = m.field_id GROUP BY m.user_id </code> </pre> </DIV>

编辑
1#
回复此人
坚挺的阿袁 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 试试这个 </p> <pre> <code> select User_ID, max(FirstName) FirstName, max(LastName) LastName from ( select User_ID, case when c.Field_Id= 'first_name' then Value end as FirstName, case when c.Field_Id = 'last_name' then Value end as LastName from Column_Values c inner join MappingTable m on c.Id = m.FieldId ) t group by User_ID </code> </pre> </DIV>

编辑

登录后才能参与评论