mysql - 比较两个表并添加具有重复 ID 的临时列

Question

我有两张 table

表1

+----+--------+----------+
| id | userId | assetId  | 
+----+--------+----------+
|  1 |    561 | 2256     |
|  2 |    561 | 2257     |
|  3 |    561 | 2258     |
|  4 |    561 | 2259     |
|  5 |    561 | 2265     |
+----+--------+----------+

表2

+---------+----------+
| assetId | assetName |
+---------+-----------+
|   2256  | Asset 1   |
|   2257  | Asset 2   |
|   2258  | Asset 3   |
|   2259  | Asset 4   |
|   2265  | Asset 5   |
|   2266  | Asset 6   |
|   2267  | Asset 7   |
|   2268  | Asset 8   |
|   2269  | Asset 9   |
|   2270  | Asset 10  |
+---------+-----------+

现在我想要一个带有临时列(“匹配”)的结果，其中对“assetId”列进行了比较。

+---------+----------+---------+
| assetId | assetName| matching |
+---------+----------+----------+
|   2256  | Asset 1  |  true    |
|   2257  | Asset 2  |  true    |
|   2258  | Asset 3  |  true    |
|   2259  | Asset 4  |  true    |
|   2265  | Asset 5  |  true    |
|   2266  | Asset 6  |  false   |
|   2267  | Asset 7  |  false   |
|   2268  | Asset 8  |  false   |
|   2269  | Asset 9  |  false   |
|   2270  | Asset 10 |  false   |
+---------+----------+----------+

这是否可以仅在一个查询中实现？

Answer 1

DROP TABLE IF EXISTS user_asset;

CREATE TABLE user_asset
(userId INT NOT NULL
,assetId INT NOT NULL
,PRIMARY KEY(userid,assetid)
);

INSERT INTO user_asset VALUES
(561,2256),
(561,2257),
(561,2258),
(561,2259),
(561,2265),
(562,2265),
(562,2265),
(561,3000),
(562,2270);


DROP TABLE IF EXISTS asset;

CREATE TABLE asset
(assetId INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,assetName VARCHAR(20) NOT NULL UNIQUE
);

INSERT INTO asset VALUES
(2256,'Asset 1'),
(2257,'Asset 2'),
(2258,'Asset 3'),
(2259,'Asset 4'),
(2265,'Asset 5'),
(2266,'Asset 6'),
(2267,'Asset 7'),
(2268,'Asset 8'),
(2269,'Asset 9'),
(2270,'Asset 10');

SELECT a.*
     , COALESCE(ua.assetid = a.assetid,0) `matching` 
  FROM asset a 
  LEFT 
  JOIN user_asset ua 
    ON ua.assetid = a.assetid 
   AND ua.userid = 561;
+---------+-----------+----------+
| assetId | assetName | matching |
+---------+-----------+----------+
|    2256 | Asset 1   |        1 |
|    2257 | Asset 2   |        1 |
|    2258 | Asset 3   |        1 |
|    2259 | Asset 4   |        1 |
|    2265 | Asset 5   |        1 |
|    2266 | Asset 6   |        0 |
|    2267 | Asset 7   |        0 |
|    2268 | Asset 8   |        0 |
|    2269 | Asset 9   |        0 |
|    2270 | Asset 10  |        0 |
+---------+-----------+----------+
10 rows in set (0.00 sec)