mysql高级分组查询

Question

我需要一些帮助来找出一个查询

我有三张桌子

sources
id, name, rank

origin
id, source_id (FK to sources id), name

One source can have many origins

product
id, origin_id (FK to origin id), name, time_added

One origin can have many products

现在，我想要的是选择每个来源的最新产品，按排名降序排序

有什么建议吗？

Answer 1

这应该是你所要求的，尽管没有样例输出很难百分之百确定。内部查询选择链接到源id的产品，按照添加的日期从最新到最旧排序，然后连接到源并分组。

SELECT
  *
FROM sources AS s
INNER JOIN (
    SELECT 
        origins.source_id,
        product.*
     FROM origin
     INNER JOIN product
     ON product.origin_id = origin.origin_id
     ORDER BY time_added DESC
) AS productsOrdered
ON productsOrdered.source_id = sources.source_id

ORDER BY s.rank DESC, productsOrdered.time_added DESC

这避免了必须进行可能代价高昂的操作，因为内部select应该相当快，并且可以根据需要进行限制

Answer 2

执行此操作的典型方法是

• 查找每个源的MAX(time_added)
• 获取每个源的产品id
• 与sources和origin表连接以检索所有列

请注意，如果有多个记录具有完全相同的time_added，则此操作将失败。

SQL语句

SELECT  *
FROM    sources s
        INNER JOIN origin o ON o.source_id = s.id
        INNER JOIN product p ON p.origin_id = o.id
        INNER JOIN (
          SELECT  id
          FROM    product p
                  INNER JOIN (
                    SELECT  origin_id
                            , MAX(time_added) AS time_addded
                    FROM    product p
                    GROUP BY
                            origin_id
                  ) pmax ON pmax.origin_id = p.origin_id
                            AND pmax.time_added = p.time_added                            
        ) pmax ON pmax.id = p.id                  

Answer 3

SELECT o.id,count(o.id) as numOfProdFromOrig p.id, p.name, p.time_added, s.rank
FROM product as p NATURAL JOIN sources as s NATURAL JOIN origin as o
GROUP BY (numOfProdFromOrig)
ORDER BY s.rank DESC