mysql连接不同ID的同一张表

Question

表:数据表

    +----+-------+-------+
    | Id | Name  | Value |
    +----+-------+-------+
    | 50 | Eric  | 1200  |
    | 50 | Barb  | 1195  |
    | 50 | Joe   | 1180  |
    | 51 | Barb  |   6   |
    | 51 | Eric  |   3   |
    | 51 | Joe   |   5   |
    +----+-------+-------+

我要这个结果

    +-------+---------+--------+
    | Name  | Value1  | Value2 |
    +-------+---------+--------+
    | Eric  |  1200   |   3    |
    | Barb  |  1195   |   6    |
    | joe   |  1180   |   5    |
    +-------+---------+--------+

我不知道如何合并这个。

SELECT name, value from datatable WHERE Id=50 AS Value1 
JOIN datatable 
WHERE Id=51 AS  Value2 ON value1.Name = value2.Name ORDER BY value1.Value

不知道如何调用 value2？

$row["??"]

Answer 1

• 您可以对 Name 进行Group By。
• 现在，您可以使用 Case .. When 进行条件聚合和 Max()功能。

尝试以下查询:

SELECT 
  t.Name, 
  MAX(CASE WHEN t.Id = 50 THEN t.VALUE END) AS Value1,
  MAX(CASE WHEN t.Id = 51 THEN t.VALUE END) AS Value2 
FROM datatable AS t
GROUP BY t.Name