34,593
社区成员
发帖
与我相关
我的任务
分享
SELECT SUM(LSTotalWeight) as TotalWeight,ContractNO,productno,techflag
FROM TechniqueInfo A
WHERE NOT EXISTS (SELECT 1 FROM TechniqueInfo WHERE ContractNO=A.ContractNO and Productno=A.Productno AND techflag<A.techflag) and (techflag='LS' or techflag='NX' or techflag='YZ' ) group by ContractNO,productno,techflag
这是我写的,统计的数据不对呢[/quote]
那你就把你要的3道工序生成一个带序号的辅助表,比如以下,然后再和业务表关联进行处理
SELECT 'LS' AS STEP,1 AS ID UNION ALL
SELECT 'NX',2 UNION ALL
SELECT 'YZ',3
SELECT SUM(LSTotalWeight) as TotalWeight,ContractNO,productno,techflag
FROM TechniqueInfo A
WHERE NOT EXISTS (SELECT 1 FROM TechniqueInfo WHERE ContractNO=A.ContractNO and Productno=A.Productno AND techflag<A.techflag) and (techflag='LS' or techflag='NX' or techflag='YZ' ) group by ContractNO,productno,techflag
SELECT SUM(LSTotalWeight) as TotalWeight,ContractNO,productno,techflag
FROM TechniqueInfo A
WHERE NOT EXISTS (SELECT 1 FROM TechniqueInfo WHERE ContractNO=A.ContractNO and Productno=A.Productno AND techflag<A.techflag) and (techflag='LS' or techflag='NX' or techflag='YZ' )
if object_id('tempdb..#tab')is not null drop table #tab
create table #tab(name nvarchar(500),class nvarchar(500),quantity decimal(18,2))
insert into #tab(name,class,quantity)
select 'AAA','工序1',100 union all
select 'AAA','工序2',50 union all
select 'AAA','工序3',100 union all
select 'BBB','工序2',100 union all
select 'CCC','工序1',100 union all
select 'CCC','工序1',100
select sum(quantity) from(
select row_number()over(partition by name order by class)as num , * from #tab
)t where num='1'