mysql - 如何将多行合并为一行

Question

我想将多行合并为一行。表 type 是动态的

餐 table 室

room_id | room_name
-------------------
1       | room_A
2       | room_B
3       | room_C

表格类型

type_id | type_name
-------------------
1       | type_I
2       | type_II
3       | type_II
4       | type_IV

表价

price_id | room_id | type_id | price
------------------------------------
1        |    1    |    1    | 100
2        |    1    |    2    | 150
3        |    1    |    3    | 200
4        |    1    |    4    | 250
5        |    2    |    1    | 100
6        |    2    |    2    | 200
7        |    2    |    3    | 300
8        |    2    |    4    | 400
9        |    3    |    1    | 150
10       |    3    |    2    | 250
11       |    3    |    3    | 350
12       |    3    |    4    | 450

我想要的是这样的

       |                   price
room   |---------------------------------------
       | type_I | type_II | type_III | type_IV 
-----------------------------------------------
room_A |   100  |   150   |   200    |   250
room_B |   100  |   200   |   300    |   400
room_C |   150  |   250   |   350    |   450

Answer 1

试试这个查询，希望这对你有帮助:

SELECT TR.Room_Name
    ,MAX(CASE WHEN P.Type_ID=1 THEN Price END)type_I
    ,MAX(CASE WHEN P.Type_ID=2 THEN Price END)type_II
    ,MAX(CASE WHEN P.Type_ID=3 THEN Price END)type_III
    ,MAX(CASE WHEN P.Type_ID=4 THEN Price END)type_IV
FROM TABLE_PRICE P
INNER JOIN TABLE_ROOM TR ON TR.Room_ID=P.Room_ID
INNER JOIN TABLE_TYPE TT ON TT.Type_ID=P.Type_ID
GROUP BY TR.Room_Name