select orderparentid, min(ordermoney) as ordermoney from (select orderparentid, sum(ordermondy) as ordermoney from tb group by orderparentid) t order by orderparentid
select orderparentid, min(ordermoney) as ordermoney from (select orderparentid, sum(ordermondy) as ordermoney from tb group by orderparentid) t order by orderparentid
我这里测试数据,就两列create table Lnorder(OrderParentId int,OrderMoney int) insert Lnorder select 100,500 union all select 101,500 union all select 101,500 union all select 101,500 union all select 100,500 union all select 100,500 union all select 100,500 union all select 100,500 union all select 103,500 union all select 103,500 union all select 0,0 goselect case when (select sum(OrderMoney) from Lnorder where OrderParentId = 101) > (select sum(OrderMoney) from Lnorder where OrderParentId = 103) then (select sum(OrderMoney) from Lnorder where OrderParentId = 103) else (select sum(OrderMoney) from Lnorder where OrderParentId = 101) end ----------- 1000(所影响的行数为 1 行)
select orderparentid, min(ordermoney) as ordermoney from (select orderparentid, sum(ordermondy) as ordermoney from tb group by orderparentid) t order by orderparentid
select orderparentid, min(ordermoney) as ordermoney from (select orderparentid, sum(ordermondy) as ordermoney from tb where orderparentid in('101','103') group by orderparentid) t order by orderparentid
create table Lnorder(OrderParentId int,OrderMoney int) insert Lnorder select 100,500 union all select 101,500 union all select 101,500 union all select 101,500 union all select 100,500 union all select 100,500 union all select 100,500 union all select 100,500 union all select 103,500 union all select 103,500 union all select 0,0 if (select sum(OrderMoney) from Lnorder where OrderParentId = 101)>(select sum(OrderMoney) from Lnorder where OrderParentId = 103) select sum(OrderMoney) from Lnorder where OrderParentId = 103 else select sum(OrderMoney) from Lnorder where OrderParentId = 101/* 1000 */
SELECT TOP 1 * FROM ( SELECT ORDERPARENTID ,SUM(ORDERMONEY) 'TOTAL' FROM LNORDER WHERE ORDERPARENTID IN (101,103) GROUP BY ORDERPARENTID ) ORDER BY TOTAL ASC
declare @Lnorder table(OrderParentId int,OrderMoney int) insert @Lnorder select 100,500 union all select 101,500 union all select 101,500 union all select 101,500 union all select 100,500 union all select 100,500 union all select 100,500 union all select 100,500 union all select 103,500 union all select 103,500 union all select 0,0 select top 1 OrderParentId,sum(OrderMoney) OrderMoney from @Lnorder where OrderParentId in(101,103) group by OrderParentId order by OrderMoney asc
declare @Lnorder table(OrderParentId int,OrderMoney int) insert @Lnorder select 100,500 union all select 101,500 union all select 101,500 union all select 101,500 union all select 100,500 union all select 100,500 union all select 100,500 union all select 100,500 union all select 103,500 union all select 103,500 union all select 0,0 select top 1 OrderParentId,sum(OrderMoney) OrderMoney from @Lnorder where OrderParentId in(101,103) group by OrderParentId order by OrderMoney asc OrderParentId OrderMoney ------------- ----------- 103 1000(1 行受影响)
declare @Lnorder table(OrderParentId int,OrderMoney int) insert @Lnorder select 100,500 union all select 101,500 union all select 101,500 union all select 101,500 union all select 100,500 union all select 100,500 union all select 100,500 union all select 100,500 union all select 103,500 union all select 103,500 union all select 0,0 --(1) ;with hgo as ( select OrderParentId,sum(OrderMoney) OrderMoney from @Lnorder where OrderParentId in(101,103) group by OrderParentId ) select min(OrderMoney) OrderMoney from hgo --(2) select top 1 OrderParentId,sum(OrderMoney) OrderMoney from @Lnorder where OrderParentId in(101,103) group by OrderParentId order by OrderMoney asc
select orderparentid, min(ordermoney) as ordermoney from (select orderparentid, sum(ordermondy) as ordermoney from tb group by orderparentid) t order by orderparentid
--借果果数据declare @Lnorder table(OrderParentId int,OrderMoney int) insert @Lnorder select 100,500 union all select 101,500 union all select 101,500 union all select 101,500 union all select 100,500 union all select 100,500 union all select 100,500 union all select 100,500 union all select 103,500 union all select 103,500 union all select 0,0 select top 1 OrderParentId ,sum(OrderMoney) as OrderMoney from @lnorder where OrderParentId in(101,103) group by OrderParentId order by OrderMoney asc OrderParentId OrderMoney ------------- ----------- 103 1000(1 行受影响)
from (select orderparentid, sum(ordermondy) as ordermoney from tb
group by orderparentid) t
order by orderparentid
select orderparentid, min(ordermoney) as ordermoney
from (select orderparentid, sum(ordermondy) as ordermoney from tb
group by orderparentid) t
order by orderparentid
insert Lnorder
select 100,500 union all
select 101,500 union all
select 101,500 union all
select 101,500 union all
select 100,500 union all
select 100,500 union all
select 100,500 union all
select 100,500 union all
select 103,500 union all
select 103,500 union all
select 0,0 goselect
case when
(select sum(OrderMoney) from Lnorder where OrderParentId = 101)
>
(select sum(OrderMoney) from Lnorder where OrderParentId = 103)
then
(select sum(OrderMoney) from Lnorder where OrderParentId = 103)
else
(select sum(OrderMoney) from Lnorder where OrderParentId = 101)
end
-----------
1000(所影响的行数为 1 行)
from (select orderparentid, sum(ordermondy) as ordermoney from tb
group by orderparentid) t
order by orderparentid
from (select orderparentid, sum(ordermondy) as ordermoney from tb where orderparentid in('101','103') group by orderparentid) t
order by orderparentid
create table Lnorder(OrderParentId int,OrderMoney int)
insert Lnorder
select 100,500 union all
select 101,500 union all
select 101,500 union all
select 101,500 union all
select 100,500 union all
select 100,500 union all
select 100,500 union all
select 100,500 union all
select 103,500 union all
select 103,500 union all
select 0,0
if (select sum(OrderMoney) from Lnorder where OrderParentId = 101)>(select sum(OrderMoney) from Lnorder where OrderParentId = 103)
select sum(OrderMoney) from Lnorder where OrderParentId = 103
else
select sum(OrderMoney) from Lnorder where OrderParentId = 101/*
1000
*/
SELECT ORDERPARENTID ,SUM(ORDERMONEY) 'TOTAL' FROM LNORDER WHERE ORDERPARENTID IN (101,103)
GROUP BY ORDERPARENTID
)
ORDER BY TOTAL ASC
insert @Lnorder
select 100,500 union all
select 101,500 union all
select 101,500 union all
select 101,500 union all
select 100,500 union all
select 100,500 union all
select 100,500 union all
select 100,500 union all
select 103,500 union all
select 103,500 union all
select 0,0 select top 1 OrderParentId,sum(OrderMoney) OrderMoney from @Lnorder where OrderParentId in(101,103)
group by OrderParentId order by OrderMoney asc
insert @Lnorder
select 100,500 union all
select 101,500 union all
select 101,500 union all
select 101,500 union all
select 100,500 union all
select 100,500 union all
select 100,500 union all
select 100,500 union all
select 103,500 union all
select 103,500 union all
select 0,0 select top 1 OrderParentId,sum(OrderMoney) OrderMoney from @Lnorder where OrderParentId in(101,103)
group by OrderParentId order by OrderMoney asc
OrderParentId OrderMoney
------------- -----------
103 1000(1 行受影响)
insert @Lnorder
select 100,500 union all
select 101,500 union all
select 101,500 union all
select 101,500 union all
select 100,500 union all
select 100,500 union all
select 100,500 union all
select 100,500 union all
select 103,500 union all
select 103,500 union all
select 0,0
--(1)
;with hgo as
(
select OrderParentId,sum(OrderMoney) OrderMoney from @Lnorder where OrderParentId in(101,103)
group by OrderParentId
)
select min(OrderMoney) OrderMoney from hgo
--(2)
select top 1 OrderParentId,sum(OrderMoney) OrderMoney from @Lnorder where OrderParentId in(101,103)
group by OrderParentId order by OrderMoney asc
select orderparentid, min(ordermoney) as ordermoney
from (select orderparentid, sum(ordermondy) as ordermoney from tb
group by orderparentid) t
order by orderparentid
--借果果数据declare @Lnorder table(OrderParentId int,OrderMoney int)
insert @Lnorder
select 100,500 union all
select 101,500 union all
select 101,500 union all
select 101,500 union all
select 100,500 union all
select 100,500 union all
select 100,500 union all
select 100,500 union all
select 103,500 union all
select 103,500 union all
select 0,0
select top 1 OrderParentId ,sum(OrderMoney) as OrderMoney from @lnorder
where OrderParentId in(101,103)
group by OrderParentId
order by OrderMoney asc OrderParentId OrderMoney
------------- -----------
103 1000(1 行受影响)