I'm developing an in-game shop for the game Minecraft, in which an item is defined as a pair of a Material string and a Data integer. Each advertised item is inserted in a table of a MySQL database with a price integer associated to it and the same item can be advertised multiple times with the same price or not, like in the example below, where the items (ink_sack, 3) and (apple, 0) appear twice:
我正在为游戏《我的世界》开发一个游戏商店，其中一个项目被定义为一对材料字符串和一个数据整数。每个广告项都被插入到MySQL数据库的一个表中，该表的价格为与之关联的整数，同一项可以多次以相同的价格或不相同的价格进行广告，如下面的示例所示，其中项目(ink_sack, 3)和(apple, 0)出现两次:
+----------+------+-------+
| material | data | price |
+----------+------+-------+
| ink_sack | 0 | 1 |
| ink_sack | 3 | 2 |
| ink_sack | 3 | 6 |
| apple | 0 | 2 |
| apple | 0 | 5 |
|iron_sword| 0 | 10 |
What I want is to retrieve the entry for each possible item (pair of Material and Data) with the lowest price, like in the following example:
我想要的是检索每个可能的项目(对材料和数据)的条目，价格最低，比如下面的例子:
+----------+------+-------+
| material | data | price |
+----------+------+-------+
| ink_sack | 0 | 1 |
| ink_sack | 3 | 2 |
| apple | 0 | 2 |
|iron_sword| 0 | 10 |
However, I don't have any idea of how to do that considering these pairs of values instead of a single column when using the LEAST keyword or even if thats the right option in this case. How should I do that with a single query and no post-processing?
然而，当使用最小关键字时，我不知道如何考虑这些值对而不是单个列，或者即使在这种情况下这是正确的选择。我应该如何使用一个查询而不进行后处理?
2 个解决方案
#1
2
Group the records by the columns you want to be unique. aggregate functions like min() apply to each group
将记录按希望惟一的列分组。聚合函数(如min())适用于每个组
select material, data, min(price) as min_price
from your_table
group by material, data
#2
0
Try this query ! Here I am using 'GROUP BY' clause which makes the group according to the data & material in the table ,then apply the aggregate function of minimum 'min' on the price column of group data & sort it by price using 'ORDER BY' clause. Also I am attaching the screenshot of the result of query.
试试这个查询!在这里，我使用“GROUP BY”子句，根据表中的数据和材料组成GROUP，然后在GROUP data的price列上应用最小“min”的聚合函数，并按“ORDER BY”子句对其进行排序。我还附上了查询结果的截图。
SELECT
material ,
data ,
min(price) AS price
FROM
[Table Name]
GROUP BY
data ,
material
ORDER BY
price
#1
2
Group the records by the columns you want to be unique. aggregate functions like min() apply to each group
将记录按希望惟一的列分组。聚合函数(如min())适用于每个组
select material, data, min(price) as min_price
from your_table
group by material, data
#2
0
Try this query ! Here I am using 'GROUP BY' clause which makes the group according to the data & material in the table ,then apply the aggregate function of minimum 'min' on the price column of group data & sort it by price using 'ORDER BY' clause. Also I am attaching the screenshot of the result of query.
试试这个查询!在这里，我使用“GROUP BY”子句，根据表中的数据和材料组成GROUP，然后在GROUP data的price列上应用最小“min”的聚合函数，并按“ORDER BY”子句对其进行排序。我还附上了查询结果的截图。
SELECT
material ,
data ,
min(price) AS price
FROM
[Table Name]
GROUP BY
data ,
material
ORDER BY
price