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

问题描述：

我正在使用MySQL 5.5。我有一个查询返回这样如何从MySql中的选择查询中选择数据？

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

我该如何编写查询？

答

使用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与你的真实的查询。

谢谢，这真的很有帮助，聪明:) – Shreyash

欢迎来到stackoverflow :)。如果您觉得这是正确的答案，请不要忘记接受它。 –

答

SELECT 
    max(s.quantity) as quantity 
    , max(s.maxPrice) as maxPrice 
    , max(s.minPrice) as minPrice 
    , max(s.AvgPrice) as avgPrice 
    , max(s.totalcounts) as totalcounts 
    FROM (
     SELECT t1.quantity,t1.maxPrice,t1.minPrice,t1.avgPrice, 0 as totalcounts 
     FROM table1 t1 
     ORDER BY quantity DESC 
     LIMIT 1 
    UNION 
     SELECT 0,0,0,0,sum(t2.counts) as totalcounts 
     FROM table1 t2 
    ) s

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

相关推荐