SELECT TOP 1 B,D FROM ( select B,[D]=sum(D) from (SELECT B,C,D=SUM(D) FROM table_name GROUP BY B,C) t1 group by B )t ORDER BY D DESC
楼上的兄弟,第二次求的不是sum而是max。 不知我对你的sql理解是否正确。
Create Table #tb_test (A int,B varchar(20),C varchar(20),D decimal(18,2)) insert #tb_test values(1,'A01','001',2) insert #tb_test values(2,'A01','001',8) insert #tb_test values(3,'A01','002',3) insert #tb_test values(4,'A01','002',4) insert #tb_test values(5,'A02','003',1) insert #tb_test values(6,'A02','003',3) SELECT TOP 1 D FROM ( Select D = SUM(D),B From (SELECT B,C,D=SUM(D) FROM #tb_test GROUP BY B,C) Ta Group By Ta.B ) Tb ORDER BY Tb.D DESC
insert @tb2 values(1,'A01','001',2)
insert @tb2 values(2,'A01','001',8)
insert @tb2 values(3,'A01','002',3)
insert @tb2 values(4,'A01','002',4)
insert @tb2 values(5,'A02','003',1)
insert @tb2 values(6,'A02','003',3)
select 批次号,产品编号,sum(入库数量)as '入库数量' from @tb2 group by 批次号,产品编号select top 1 批次号,产品编号,sum(入库数量)as '入库数量' from @tb2 group by 批次号,产品编号
order by 入库数量 desc--借上面的数据.
在以b分组,求出sum值最大的那条记录。
TOP 1 B,D
FROM
(
select B,[D]=sum(D)
from
(SELECT B,C,D=SUM(D) FROM table_name GROUP BY B,C) t1
group by B
)t
ORDER BY
D DESC
不知我对你的sql理解是否正确。
insert #tb_test values(1,'A01','001',2)
insert #tb_test values(2,'A01','001',8)
insert #tb_test values(3,'A01','002',3)
insert #tb_test values(4,'A01','002',4)
insert #tb_test values(5,'A02','003',1)
insert #tb_test values(6,'A02','003',3)
SELECT
TOP 1 D
FROM
( Select D = SUM(D),B
From (SELECT B,C,D=SUM(D) FROM #tb_test GROUP BY B,C) Ta
Group By Ta.B ) Tb
ORDER BY Tb.D DESC