如何写“每组最大n"?类型查询，但有其他条件? [英] How to write"greatest n per group" type query, but with additional conditions?

问题描述

昨天我问了一个有关进行每组最大n"类型查询的问题(当时还不知道它被称为每组最大n")，但我问如何使每组最少.表结构如下:

I asked a question about making a "greatest n per group" type query yesterday (at the time not knowing it was called "greatest n per group") except I asked how to get the least per group. The table structure was as follows:

type    name    value
=====================
1       item1    1
1       item2    20
2       item3    0
3       item4    5
3       item5    2
3       item6    50

我收到了几个很好的答案，而最有帮助的是:

I received several great answers, and the most helpful one was this:

SELECT t1.type, t1.name, t1.value
FROM mytable t1
LEFT JOIN mytable t2 ON t1.type = t2.type AND t1.value > t2.value
WHERE t2.value IS NULL

上面的查询结果如下:

type    name    value
=====================
2       item3    0
1       item1    1
3       item5    2

但是，自从提出问题以来，我意识到我遗漏了一项重要要求，我似乎无法弄清楚如何添加到上述查询中.我需要添加一个条件语句，而不是选择每组列的最小值最低的行，而是选择每组列的最小值最低的行，但是该行具有值 greater的另一列超过一些最小值.

However, since asking the question, I realized that I left out an important requirement, one which I can't seem to figure out how to add to the above query. I need to add a conditional statement that, instead of selecting the row with the lowest value for a column per group, selects the row with the lowest value for a column per group but where that row has another column with a value greater than some minimum value.

这是我的新问题/问题:

Here is my new question/problem:

我有下表(产品):

+-----------------------------------------------------------+
|   id   |   type   |   name   |   popularity   |   price   |
+-----------------------------------------------------------+
|    0   |    0     |   item1  |      3.5       |   0.99    |
|    3   |    1     |   item2  |      3         |   1.99    |
|    4   |    1     |   item3  |      6         |   2.95    |
|    6   |    1     |   item4  |      9         |   2.50    |
|    9   |    1     |   item5  |      12        |   3.75    |
|    12  |    2     |   item6  |      16        |   5.25    |
|    13  |    2     |   item7  |      32        |   10.95   |
|    14  |    2     |   item8  |      48        |   7.50    |
+-----------------------------------------------------------+

我需要获得每组中价格最低的商品(各组是不同类型的值)，并且受欢迎程度大于某个数量(如果该组中没有商品的受欢迎程度大于指定数量，则没有商品)该组中的人应退还).那句话的最后一部分是这个问题与我的最后一个问题有何不同.最好将结果按价格升序返回.

I need to get the lowest priced item in each group (the groups being the different values of type) with the popularity being greater than some amount (if no items in the group have popularity greater than the specified amount, then no items from that group should be returned). The last part of that sentence is how this question differs from my last question. Preferably the results should be returned sorted by the price in ascending order.

因此，假设受欢迎程度必须大于3，那么结果应该是:

So, say that the popularity needs to be greater than 3, then the result should be:

+-----------------------------------------------------------+
|   id   |   type   |   name   |   popularity   |   price   |
+-----------------------------------------------------------+
|    0   |    0     |   item1  |      3.5       |   0.99    |
|    6   |    1     |   item4  |      9         |   2.50    |
|    12  |    2     |   item6  |      16        |   5.25    |
+-----------------------------------------------------------+

如果流行度需要大于6，则结果应为:

If the popularity needs to be greater than 6, then the result should be:

+-----------------------------------------------------------+
|   id   |   type   |   name   |   popularity   |   price   |
+-----------------------------------------------------------+
|    6   |    1     |   item4  |      9         |   2.50    |
|    12  |    2     |   item6  |      16        |   5.25    |
+-----------------------------------------------------------+

希望我正确地做了两个例子.无论如何，我想你会明白的.

Hopefully I did both of those examples correctly. In any case, I think you get the idea.

是否可以执行我在一个查询中要问的事情?

Would it be possible to do what I'm asking in one query?

推荐答案

这需要引起注意的地方(where或on?)放在哪里，这样就不会被欺骗:-)需要将t1的条件添加到where子句，并将t2的条件添加到on子句:

This requires some attention where to put the condition (to where or to on?) so that you don't get tricked :-) You need to add the condition for t1 to where clause and for t2 to on clause:

SELECT t1.type, t1.name, t1.value
FROM mytable t1
LEFT JOIN mytable t2 ON t1.type = t2.type AND t1.value > t2.value 
    AND t2.popularity > 3 /* here */
WHERE t2.value IS NULL 
    AND t1.popularity > 3 /* and here */

尚未测试过，但应该可以.

Haven't tested it, but it should work.

尝试进行解释:where子句中的条件会影响您认为哪些元素是价值最低的潜在元素.而on子句中的条件会影响链接:您想与哪些其他元素进行比较?它定义了您要进行比较的组.从技术上讲，它会影响t2.*何时为NULL.如果给where子句指定了t2.popularity的条件，则对于最低元素受欢迎程度较低的组，您将不会收到任何NULL(即找不到具有最低值的元素).

Attempt for an explanation: the condition in where clause affects which elements you consider as potential elements with lowest value. Whereas the condition in on clause affects the linkage: to what other elements you want to compare it? It defines the group within which you compare. Technically, it has an impact on when t2.* will be NULL. Had you given the condition on t2.popularity to where clause instead, you would not receive any NULLs (i.e. not find elements w/lowest value) for groups where the lowest elements have low popularity.

这篇关于如何写“每组最大n"?类型查询，但有其他条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！