如何管理MySQL中的版本数据?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (25)

现有以下表格:

Cars
--------
ID  Make    Model       Version 
1   Honda   Accord      v1
2   Honda   Civic       v1
3   Honda   Odysey      v1
1   Honda   AccordLX    v2
1   Honda   AccordEX    v3

CarProperties
-------------
CarID   key value   Version
1   color   red v1
1   doors   4   v1
2   color   blue    v1
2   doors   2   v1
3   color   black   v1
1   color   blue    v2
1   color   green   v3

Versions
-----------
ID  Status
v1  Approved
v2  Pending
v3  Approved

在下面的示例中,carid:1的批准版本是

Honda AccordEX color:green doors:4

有什么好的解决方法吗?

提问于
用户回答回答于

这样试试:

WITH car_version_latest AS (
     SELECT c.*, ROW_NUMBER() OVER (PARTITION BY (c.ID, c.Make, c.Model) ORDER BY c.Version DESC) AS rn
     FROM Cars AS c
     )

SELECT * FROM car_version_latest cvl
WHERE cvl.rn = 1;

扫码关注云+社区

领取腾讯云代金券