MySQL 选择具有特定条件的行以及多对多中这些条件的最后插入的行

Question

我有 3 个具有多对多关系的表。我想选择特定用户和特定产品最后插入的行。

表“user”包含用户的个人信息:

userID     persID     firstName     lastName     gender
42         9559000    Jane          Rae          female
43         9559001    John          Doe          male
.
.
.

表“storage1”包含产品信息:

storageID     product     productAttribute     productSize     storageQTY
1             shirt       red                  S               10
2             shirt       blue                 M               10
.
.
.
13            shirt       green                L               10
14            shirt       green                XL              9
15            shirt       green                XXL             7
.
.
39            trousers    male                 60              10
40            trousers    male                 62              8
.
.
59            shoes       standard             41              10
60            shoes       standard             42              7
61            shoes       standard             43              10
62            shoes       standard             44              10
63            shoes       standard             45              9
.
.
72            jacket      red                  L               10
73            jacket      red                  XL              9
74            jacket      red                  XXL             6

和表activityRecords:

recordsID     userID     storageID     startDate     expDate
99            43         15            2017-09-14
100           43         74            2017-09-14
101           43         39            2017-09-14
102           43         13            2017-09-14
103           43         14            2017-09-14
104           43         40            2017-09-14
105           43         14            2017-09-14
106           43         63            2017-09-14
107           43         59            2017-09-14

到目前为止，我有这段代码，它仅产生与特定用户相关的产品的最大值。

select 
        persID, 
        firstName, 
        lastName, 
        gender, 
        max(case when storage1.product = 'shoes' then storage1.productSize end) shoes, 
        max(case when storage1.product = 'trousers' then storage1.productSize end) trousers, 
        max(case when storage1.product = 'shirt' then storage1.productSize end) shirt, 
        max(case when storage1.product = 'shirt' then storage1.productAttribute end) color, 
        max(case when storage1.product = 'jacket' then storage1.productSize end) jacket, 
        startDate, 
        expDate 
    from activityRecords 
        left join user on activityRecords.userID = user.userID 
        left join storage1 on activityRecords.storageID = storage1.storageID  
    where persID='9559001' 
    group by persID, firstName, lastName, gender, startDate, expDate
    order by persID

结果如下:

persID     firstName     lastName     gender     shoes     trousers     shirt     color     jacket     startDate     expDate
9559001    John          Doe          male       45        null         null      null      null       2017-09-14
9559001    John          Doe          male       null      62           null      null      null       2017-09-14
9559001    John          Doe          male       null      null         XXL       green     null       2017-09-14
9559001    John          Doe          male       null      null         null      null      XXL        2017-09-14

但我想得到这个:

persID     firstName     lastName     gender     shoes     trousers     shirt     color     jacket     startDate     expDate
9559001    John          Doe          male       41        null         null      null      null       2017-09-14
9559001    John          Doe          male       null      62           null      null      null       2017-09-14
9559001    John          Doe          male       null      null         XL        green     null       2017-09-14
9559001    John          Doe          male       null      null         null      null      XXL        2017-09-14

因为鞋号 41 是 ActivityRecords 表 (recordsID 107) 中鞋款的最新条目，衬衫尺码 XL 是 ActivityRecords 表 (recordsID 105) 中衬衫的最新条目。虽然现在鞋子选择的记录 ID 为 106，衬衫选择的记录 ID 为 99。我知道这是相关的，因为我的选择代码(使用 max(case when...))，但我不知道如何选择大多数特定产品和人员的最新条目。任何人都可以帮我解决这个问题吗？谢谢

Answer 1

我建议您继续使用子查询或 View ，以便更轻松地获得结果。

Views Syntax Reference

您可以为每个产品创建一个 View ，例如:

create view shirt_activity_view
as select
  ar.userID,
  ar.recordsID,
  s1.productSize as shirt,
  s1.productAttribute as color,
  ar.startDate, 
  ar.expDate   
from activityRecords as ar
  inner join storage1 as s1
    on ar.storageID = s1.storageID
   and s1.product = 'shirt';

如果您想获取所有用户的最新衬衫事件:

select
  *
from shirt_activity_view
inner join (
    select
      userID,
      max(startDate) as max_start_date
    from shirt_activity_view
    group by userID
  ) as max_dates
  on shirt_activity_view.userID = max_dates.userID
 and shirt_activity_view.startDate = max_dates.max_start_date

您可以使用最后一个查询作为新 View 或作为更复杂查询中的子查询。

通过这种方式，您甚至可以获得所有数据的扁平结构(使用结果集中您期望的所有列)