MYSQL 选择两个不同的值

Question

SELECT DISTINCT v.id, v.make_id, v.model_id, i.attach_location, mk.make, md.model, v.made_year, u.username
FROM wsq_garage_vehicles v, wsq_garage_vehicles_gallery vg, wsq_garage_images i, wsq_garage_makes mk, wsq_garage_models md, wsq_users u
WHERE v.id = i.vehicle_id
AND mk.id = v.make_id
AND md.id = v.model_id
AND v.user_id = u.user_id
ORDER BY v.date_updated DESC
LIMIT 10

以上返回

 id     make_id     model_id    attach_location     make    model   made_year   username
2   25  258     garage_vehicle-2-1373826921.jpg     Ford    Fiesta  2012    John
12  95  836     garage_vehicle-12-1374094864.jpg    Nissan  200SX   1998    Lucky307
12  95  836     garage_vehicle-12-1374095057.jpg    Nissan  200SX   1998    Lucky307
12  95  836     garage_vehicle-12-1374095721.jpg    Nissan  200SX   1998    Lucky307
10  90  752     garage_vehicle-10-1374080908.jpg    Vauxhall    Astra   2003    adm
8   90  756     http://i1279.photobucket.com/albums/y538/allankend...   Vauxhall    Cavalier    1993    muzz
8   90  756     garage_vehicle-8-1374058024.jpg     Vauxhall    Cavalier    1993    muzz
9   25  253     garage_vehicle-9-1374058087.jpg     Ford    Escort  1992    v33bot
1   25  258     garage_vehicle-1-1373755717.jpg     Ford    Fiesta  2005    Beardy
4   43  366     garage_vehicle-4-1373916262.jpg     Land Rover  Defender    1996    Hobbs92

我需要的是只返回一个实际的不同 id 所以它最终会像这样

 id     make_id     model_id    attach_location     make    model   made_year   username
2   25  258     garage_vehicle-2-1373826921.jpg     Ford    Fiesta  2012    John
12  95  836     garage_vehicle-12-1374095057.jpg    Nissan  200SX   1998    Lucky307
10  90  752     garage_vehicle-10-1374080908.jpg    Vauxhall    Astra   2003    adm
8   90  756     http://i1279.photobucket.com/albums/y538/allankend...   Vauxhall    Cavalier    1993    muzz
9   25  253     garage_vehicle-9-1374058087.jpg     Ford    Escort  1992    v33bot
1   25  258     garage_vehicle-1-1373755717.jpg     Ford    Fiesta  2005    Beardy
4   43  366     garage_vehicle-4-1373916262.jpg     Land Rover  Defender    1996    Hobbs92

Answer 1

将 GROUP BY 与任何具有 attach_location 的聚合函数一起使用，如下所示:

SELECT 
  v.id, 
  v.make_id, 
  v.model_id, 
  MAX(i.attach_location), 
  mk.make, 
  md.model, 
  v.made_year, 
  u.username
FROM wsq_garage_vehicles v
INNER JOIN wsq_garage_images i            ON v.id      = i.vehicle_id
INNER JOIN wsq_garage_makes mk            ON mk.id     = v.make_id
INNER JOIN wsq_garage_models md           ON md.id     = v.model_id
INNER JOIN wsq_users u                    ON v.user_id = u.user_id
GROUP BY v.id, 
         v.make_id, 
         v.model_id, 
         mk.make, 
         md.model, 
         v.made_year, 
         u.username
ORDER BY v.date_updated DESC
LIMIT 10;

注意:

• 我使用了明确的 JOIN 语法，而不是您使用的旧连接语法。推荐使用。
• 在您的查询中表 wsq_garage_vehicles v 和 wsq_garage_vehicles_gallery vg 之间没有连接条件，并且您的查询中从未使用过它，因此我将其删除。<
• 实现此目的的另一种方法是忽略 attach_location 的聚合函数，它将在 MySQL 中正常工作，它将获得任意值。