这个?Declare @Order table (ID varchar(10) ,WareCode varchar(10),Amount int) insert @Order select '001', 'A', 12 union all select '001', 'B', 12 union all select '003', 'A', 13 union all select '003', 'B', 20select * from @Order --更新 update @Order set Amount = (select sum(Amount) from @Order where ID = '003') where WareCode ='B' and ID='003'select * from @Order--查询 select a.ID ,a.WareCode , case when a.WareCode='B' then ( select sum(b.Amount) from @Order b where b.ID=a.ID ) else a.Amount end as Amount from @Order a
Select ID ,sum(WARECODE) as WARECODE.B form ORDER Group by ID
select t1.ID,'B' as WARECODE,sum(t1.AMOUNT) as AMOUNT from [order] t1 where exists(select 1 from [order] t2 where t2.id=t1.id) group by t1.ID
或许这是你想要的结果CREATE TABLE [dbo].[ORDER1]( [id] [bigint] NULL, [WARECODE] [varchar](50) NULL, [AMOUNT] [int] NULL ) ON [PRIMARY]insert into dbo.ORDER1 values ('001','A',12), ('001','B',12), ('003','A',13), ('003','B',20)select * from dbo.ORDER1-- sql select id,WARECODE,AMOUNT= case WARECODE when 'B' then (select sum(AMOUNT) from dbo.ORDER1 as t where t.id=ORDER1.id) else AMOUNT end from dbo.ORDER1 结果: id WARECODE AMOUNT 1 A 12 1 B 24 3 A 13 3 B 33
insert @Order
select '001', 'A', 12 union all
select '001', 'B', 12 union all
select '003', 'A', 13 union all
select '003', 'B', 20select * from @Order
--更新
update @Order
set Amount = (select sum(Amount) from @Order where ID = '003')
where WareCode ='B' and ID='003'select * from @Order--查询
select a.ID
,a.WareCode
, case when a.WareCode='B' then (
select sum(b.Amount) from @Order b where b.ID=a.ID
) else a.Amount end as Amount
from @Order a
select t1.ID,'B' as WARECODE,sum(t1.AMOUNT) as AMOUNT
from [order] t1
where exists(select 1 from [order] t2 where t2.id=t1.id)
group by t1.ID
[id] [bigint] NULL,
[WARECODE] [varchar](50) NULL,
[AMOUNT] [int] NULL
) ON [PRIMARY]insert into dbo.ORDER1 values
('001','A',12),
('001','B',12),
('003','A',13),
('003','B',20)select * from dbo.ORDER1-- sql
select id,WARECODE,AMOUNT=
case WARECODE
when 'B' then (select sum(AMOUNT) from dbo.ORDER1 as t where t.id=ORDER1.id)
else AMOUNT
end
from dbo.ORDER1
结果:
id WARECODE AMOUNT
1 A 12
1 B 24
3 A 13
3 B 33