mysql连接

643ylb08 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(317)

表：数据表

+----+-------+-------+
    | 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["??"]

sql mysql Join

来源：https://stackoverflow.com/questions/53100709/mysql-join-on-same-table-with-different-ids

2条答案

按热度按时间

lskq00tm1#

使用表别名

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

赞(0）回复(0）举报 2021-06-18

2vuwiymt2#

你可以做一个 Group By 在 Name .
现在，您可以使用 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

赞(0）回复(0）举报 2021-06-18

我来回答

mysql连接

2条答案

相关问题

热门标签

最新问答