mysql - 如何从 MySql 中的选择查询中选择数据？

Question

我正在使用 MySQL 5.5。我有一个查询返回这样的结果

quantity| maxPrice| minPrice |avgPrice| counts
"10"    "23.50"   "23.50"  "23.500000"  "1"
"15"    "15.75"   "15.75"  "15.750000"  "1"
"23"    "100.71"  "100.71" "100.710000" "1"
"25"    "210.00"  "200.00" "205.000000" "2"

现在我想从这些数据中提取最大数量的元组和所有计数的总和...这样结果看起来像这样

quantity| maxPrice| minPrice |avgPrice| counts
"25"    "210.00"  "200.00" "205.000000"  5

我该如何为此编写查询？

Answer 1

使用ORDER BY quantity DESC 和LIMIT 1 来提取元组。用另一个选择包围您的查询，并使用 SUM(counts) AS counts 作为总数。

示例:

SELECT 
 x.quantity, x.maxPrice, x.minPrice, 
 x.avgPrice, SUM(x.counts) AS counts
FROM 
(
 SELECT * 
 FROM mytable
 ORDER BY quantity DESC
) AS x
LIMIT 1;

将 SELECT * FROM mytable 替换为您的真实查询。