从子查询SQL中选择“最大数据"，但它显示子查询中的所有结果 [英] Select Max Data from Subquery SQL, but it Show All Results from Subquery

问题描述

SELECT MAX( t.valuesum ) AS total, t.codebook
FROM (
    SELECT SUM( value ) AS valuesum, codebook
    FROM stock
    GROUP BY codebook
) t
GROUP BY t.codebook

在子查询中，输出为:

b001 35,
b002 20,
b0003 11

我只想看到:b00135.请帮助我.

I want to only see: b001 35. Please help me.

推荐答案

使用where条件的第一个解决方案:

1st solution using where condition:

SELECT t1.valuesum, t1.codebook
FROM (
        SELECT SUM( s.value ) AS valuesum, s.codebook
        FROM stock s
        GROUP BY s.codebook
     ) t1
WHERE t1.valuesum in (SELECT MAX( t2.valuesum ) AS total
                     FROM (
                             SELECT SUM( s2.value ) AS valuesum, s2.codebook
                             FROM stock s2
                             GROUP BY s2.codebook
                           )t2
                     ) 

SQL小提琴演示

使用having条件的第二个解决方案:

SQL Fiddle Demo

2nd solution using having condition:

SELECT max(t1.valuesum), t1.codebook
FROM (
        SELECT SUM( s.value ) AS valuesum, s.codebook
        FROM stock s
        GROUP BY s.codebook
     ) t1
GROUP BY t1.codebook
HAVING max(t1.valuesum) = (SELECT MAX( t2.valuesum ) AS total
                     FROM (
                             SELECT SUM( s2.value ) AS valuesum, s2.codebook
                             FROM stock s2
                             GROUP BY s2.codebook
                           )t2
                     )

这篇关于从子查询SQL中选择“最大数据"，但它显示子查询中的所有结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！