jquery - 如何在多列中选择特定且不同的记录数？

Question

我在 MySql 数据库中有这样的表。

                    "table1"____________________________________________________id      Category      Type     rating     Value----------------------------------------------------1          1           1         5          232          1           1         6          273          2           1         4          264          2           2         2          255          3           1         4          216          3           1         5          28

我想在此表中选择特定数量的具有唯一类别和类型的不同文档。像这样:

select distinct category, type from table1 order by rating desc limit 0,3

        "table2"__________________________id      Category      Type     --------------------------1          1           1       5          3           1    3          2           1    

然后从具有此类别和类型的表中选择所有值。

select id,value from table1 where type and category is in table2

                    "table3"____________________________________________________id      Category      Type     rating     Value----------------------------------------------------1          1           1         5          232          1           1         6          275          3           1         4          216          3           1         5          283          2           1         4          26

我如何使用一个 Sql 语句来实现这个目标？

谢谢。

Answer 1

您可以通过使用查询生成派生表来完成此操作。然后将这个派生表与原始表连接起来:

SELECT id, t1.Category, t1.Type, rating, Value
FROM table1 AS t1
INNER JOIN (SELECT DISTINCT category, type 
            FROM table1 
            ORDER BY rating DESC limit 0,2) AS t2
ON t1.category = t2.category AND t1.type = t2.type

但上面实际上并没有返回OP的预期结果集。如果您希望所有行都具有与前 3 个评级值相关的 (Category, Type) 值，那么您可以使用:

SELECT id, t1.Category, t1.Type, rating, Value
FROM table1 AS t1
INNER JOIN (
  SELECT category, type
  FROM table1
  GROUP BY category, type
  ORDER BY MAX(rating) DESC LIMIT 0,3
) AS t2 ON t1.category = t2.category AND t1.type = t2.type

Demo here