从 PHP 表中选择行的子集 [英] Selecting a subset of rows from a PHP table

问题描述

我对使用 SQL 还很陌生，所以我希望有人能指出我正确的方向来设置比我过去需要使用的查询稍微复杂一些的查询.

I'm pretty new to using SQL, so I was hoping someone could point me in the right direction for setting up a slightly more complex query than I have needed to use in the past.

我有一个简单的 mySql 表，代表一个游戏的市场，它看起来像这样:(数字用于后面的例子:

I have a simple mySql table representing a market for a game, which looks like this: (numbers for the purposes of examples later:

id seller price amount
1  tom    330   100
2  tom    370   500
3  tom    400   750
4  jerry  700   250

目前我正在使用 php 加载这个表:

currently I am loading this table using php:

$items = dbquery("SELECT * 
                  FROM $marketdb 
                  WHERE price=$minprice 
                  ORDER BY amount;");

这很好，但我想做的是只加载每个卖家的一行:特别是，我只想加载每个卖家的金额"值最大的行.在上面的例子中，这意味着结果将只包含表格中的最后两行.

This is fine, but what I would like to do instead is to only load one row per seller: in particular, I would like to load only the row with the largest value of 'amount' for each seller. In the example above, this means the result would only contain the last two lines in the table.

推荐答案

您可以有一个子查询，分别获取每个卖家的最大金额，然后再次将其与表连接以获取额外的列.

You can have a subquery which separately get the greatest amount for every seller and join it with the table again to get the extra columns.

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  seller, MAX(amount) amount
            FROM    tableName
            GROUP   BY seller
        ) b ON a.seller = b.seller AND
                a.amount = b.amount

• SQLFiddle 演示

或

SELECT  a.*
FROM    tableName a
WHERE   a.amount =
        (
            SELECT  MAX(amount)
            FROM    tableName b
            WHERE   a.seller = b.seller
        )

• SQLFiddle 演示

两个查询都会输出

╔════╦════════╦═══════╦════════╗
║ ID ║ SELLER ║ PRICE ║ AMOUNT ║
╠════╬════════╬═══════╬════════╣
║  3 ║ tom    ║   400 ║    750 ║
║  4 ║ jerry  ║   700 ║    250 ║
╚════╩════════╩═══════╩════════╝

这篇关于从 PHP 表中选择行的子集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！