选择所有具有最大值的行

我有这个SQL查询：

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

现在，我想选择所有具有表格最大价格的行。 我已经尝试过，但不幸的是它没有返回任何行：

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);

我有些失落，有人有任何指针吗？

---

这对我来说看起来很简单：

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

---

尝试此解决方案：

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

编辑 ：虽然我不能想出任何方法来重写这个，以便不必冗余地编写基本查询，但你可能做的是这样的：

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

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

---

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

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;

链接地址: http://www.djcxy.com/p/61245.html

上一篇: selecting all rows with maximum value

下一篇: Project Name Issue when Importing Android Projects into Eclipse