mysql - SELECT DISTINCT 没有做我希望它会做的事情

Question

我正在尝试取回属性列表。属性可以有很多“通行证”。我想带回一个唯一属性列表，但如果它们有多个 channel ，我想要具有最大 pass_id 的 channel 。这是我目前所拥有的:

select * from passes
inner join properties on properties.prop_id = passes.prop_id
where pass_id NOT IN (select pass_id from queue where user_id = 1)

+---------+---------+---------+---------+------------------+--------------+-------+
| pass_id | prop_id | user_id | prop_id | full_street_name | house_number | zip   |
+---------+---------+---------+---------+------------------+--------------+-------+
|      18 |      21 |       1 |      21 |  N KEELER AVE    |         6200 | 60646 |
|      20 |      21 |       1 |      21 |  N KEELER AVE    |         6200 | 60646 |
|      21 |      21 |       1 |      21 |  N KEELER AVE    |         6200 | 60646 |
|      22 |      22 |       1 |      22 |  E CHESTNUT ST   |          111 | 60611 |
+---------+---------+---------+---------+------------------+--------------+-------+

我希望它只返回 pass_id 21 和 22，因为 18 和 20 是 21 的重复属性。在此先感谢。

Answer 1

尝试:

 Select * From passes p
 Where Pass_Id =
     (Select Max(pass_Id) From Passes
      Where Prop_Id = p.Prop_Id)