SQL - 选择具有最大值的所有行

I have this SQL query:

我有这个SQL查询:

SELECT id, COUNT(*) AS price
FROM (SELECT * FROM rt WHERE somecondition) AS st
      JOIN tt
      ON st.id = tt.id
GROUP BY id;

Now, I want to select all rows which have the maximum price of the table. I have tried this, which unfortunately returns no row at all:

现在,我想选择具有表的最大价格的所有行。我试过这个,遗憾的是根本没有返回任何行:

SELECT id, COUNT(*) AS price
FROM (SELECT * FROM rt WHERE somecondition) AS st
      JOIN tt
      ON st.id = tt.id
GROUP BY id
HAVING price = MAX(price);

I'm somewhat lost, does anybody have any pointers?

我有点失落,有没有人有任何指针?

6 个解决方案

#1

This looks fairly simple to me:

这看起来很简单:

select * from <table> 
where <column name> in(
   SELECT MAX(column name) FROM table
)

#2

Try this solution:

试试这个解决方案

SELECT a.id, a.price
FROM
(
    SELECT aa.id, COUNT(1) AS price
    FROM rt aa
    INNER JOIN tt bb ON aa.id = bb.id
    WHERE aa.somecondition
    GROUP BY aa.id
) a
INNER JOIN
(
    SELECT MAX(aa.price) AS maxprice
    FROM
    (
        SELECT COUNT(1) AS price
        FROM rt aaa
        INNER JOIN tt bbb ON aaa.id = bbb.id
        WHERE aaa.somecondition
        GROUP BY aaa.id
    ) aa
) b ON a.price = b.maxprice

Edit: While I can't think of any way to rewrite this so as to not have to write the base-queries redundantly, what you could perhaps do is this:

编辑:虽然我想不出任何重写方法,以便不必冗余地编写基本查询,你可能做的是:

SELECT GROUP_CONCAT(a.id) AS ids, a.price
FROM
(
    SELECT aa.id, COUNT(1) AS price
    FROM rt aa
    INNER JOIN tt bb ON aa.id = bb.id
    WHERE aa.somecondition
    GROUP BY aa.id
) a
GROUP BY a.price
ORDER BY a.price DESC
LIMIT 1

This produces a comma-separated-list of the ids that share the same maximum value. This is probably not the format you are looking for though, but it is one way to avoid having to write the base-query twice. Just putting that out there.

这将生成以逗号分隔的id,这些id共享相同的最大值。这可能不是您正在寻找的格式,但它是避免必须编写两次基本查询的一种方法。把它放在那里。

#3

try this, put MAX in select, this should be the correct way

试试这个,把MAX放在选中,这应该是正确的方法

SELECT id, COUNT(*) AS price, MAX(price) AS max_price
FROM (SELECT some_table_name FROM rt WHERE somecondition LIMIT 1) AS st
      JOIN thenextTable as tt
      ON st.id = tt.id
GROUP BY id;

#4

Assuming that @Zane's answer is what you do want, here's a portable version of his query that also avoids LIMIT/TOP operations. I'm not really familiar with mysql dialects, but I imagine this will work without problem.

假设@ Zane的答案是你想要的,这里是他的查询的可移植版本,也避免了LIMIT / TOP操作。我对mysql方言并不是很熟悉,但我想这可以毫无问题地工作。

SELECT a.id, a.price
FROM (
    SELECT aa.id, COUNT(1) AS price
    FROM rt aa
    INNER JOIN tt bb ON aa.id = bb.id
    WHERE [somecondition]
    GROUP BY aa.id
) a
WHERE
    a.price >= ALL (
        SELECT COUNT(1) AS maxprice
        FROM rt aa
        INNER JOIN tt bb ON aa.id = bb.id
        WHERE [somecondition]
        GROUP BY aa.id
    )

#5

HAVING is used to check conditions after the aggregation takes place.

HAVING用于在聚合发生后检查条件。

WHERE is used before the aggregation takes place.

在聚合发生之前使用WHERE。

SELECT id, COUNT(*) AS price
FROM (SELECT * FROM rt WHERE somecondition) AS st
  JOIN tt
  ON st.id = tt.id
WHERE price = (SELECT MAX(price) FROM ...table)
GROUP BY id

#6

You asked for an approach that didn't require the redundancy of stating the inner query more than once. That's certainly what a cte is good for. These are two other solutions rewritten to use that tactic.

您要求的方法不需要多次声明内部查询的冗余。这肯定是cte的好处。这是另外两个使用该策略重写的解决方案。

WITH basequery as (
    SELECT aa.id, COUNT(1) AS price
    FROM rt aa INNER JOIN tt bb ON aa.id = bb.id
    WHERE [aa.somecondition]
    GROUP BY aa.id
)
SELECT a.id, a.price
FROM
    basequery as a INNER JOIN
    (SELECT MAX(price) AS maxprice FROM basequery) as b
        ON a.price = b.maxprice
-- or
WITH basequery as (
    SELECT aa.id, COUNT(1) AS price
    FROM rt aa INNER JOIN tt bb ON aa.id = bb.id
    WHERE [aa.somecondition]
    GROUP BY aa.id
)
SELECT a.id, a.price
FROM
    basequery as a
WHERE
    a.price >= ALL (SELECT price FROM basequery)

#1