mysql - 比较 2 个数据集时找到唯一值

Question

我有下表，称为 versiontrim，其中包含一些示例数据:

version_id   trim_id
    410        3
    410        5
    410        10
    410        15
    410        20
    411        2
    411        5
    411        15
    411        25
    411        28

另一个表 trims 包含每个 trim_id 的详细信息，如下所示:

 trim_id    trim 
    2       Air conditioner auto            
    3       Air conditioner manual
    5       Airbag
    10      Cloth seats
    25      Leather seats
    etc.....

我希望为每个 SET OF 2 version_id 提取唯一的 trim_id。结果应如下所示:

version_id trim_id  trim                   version_id  trim_id     trim
                                           411         2           Air conditioner auto
410        3        Air conditioner manual
410        10       Cloth seats
                                           411         25        Leather seats
410        20       blahblah
                                           411         28         blah....
etc...

我确实设法使用下面的查询分别获取每个集合，交换 version_id:

SELECT version_id, trim_id, trim
FROM versiontrim a
INNER JOIN trims USING(trim_id)
WHERE a.version_id = 411
AND NOT EXISTS
(
 SELECT * FROM versiontrim b 
 INNER JOIN trims USING(trim_id)
 WHERE a.trim_id = b.trim_id
 AND b.version_id = 410
)

我确信有一种更直接的方法可以做到这一点，只需一个查询，这样我就可以正确地输出它。感谢您的帮助。

Answer 1

SELECT 
  CASE 
    WHEN v.version_id = 410 THEN v.version_id
    ELSE ''
  END  AS Left_Version
  ,CASE 
    WHEN v.version_id = 410 THEN v.trim_id
    ELSE ''
  END  AS Left_Trim_id
  ,CASE 
    WHEN v.version_id = 410 THEN t.trim
    ELSE ''
  END  AS Left_Trim
  ,CASE 
    WHEN v.version_id = 411 THEN v.version_id
    ELSE ''
  END  AS Right_Version
  ,CASE 
    WHEN v.version_id = 411 THEN v.trim_id
    ELSE ''
  END  AS Right_Trim_id
  ,CASE 
    WHEN v.version_id = 411 THEN t.trim
    ELSE ''
  END  AS Right_Trim
FROM(SELECT trim_id, COUNT(*)
     FROM versiontrim
     WHERE version_id IN (410,411)
     GROUP BY trim_id
     HAVING COUNT(*) = 1) AS vt
JOIN versiontrim AS v
 ON v.trim_id = vt.trim_id
   AND v.version_id IN (410,411)
JOIN trims AS t
 ON v.trim_id = t.trim_id
GROUP BY v.trim_id
ORDER BY v.trim_id;