if object_id('[TB]') is not null drop table [TB]
create table [TB] (id int,订单号 int,订单总重量 int,完成重量1 int,完成重量2 int,完成重量3 int,生产日期 datetime)
insert into [TB]
select 1,111,1000,200,200,100,'2014-04-29' union all
select 2,111,1000,100,200,200,'2014-04-30' union all
select 3,222,500,100,100,100,'2014-05-03'select * from [TB]SELECT 订单号
FROM ( SELECT 订单号 ,
MIN(订单总重量) AS 订单总重量 ,
SUM(完成重量1 + 完成重量2 + 完成重量3) AS 完成总重量
FROM TB
GROUP BY 订单号
) T
WHERE 订单总重量 <> 完成总重量
create table [TB] (id int,订单号 int,订单总重量 int,完成重量1 int,完成重量2 int,完成重量3 int,生产日期 datetime)
insert into [TB]
select 1,111,1000,200,200,100,'2014-04-29' union all
select 2,111,1000,100,200,200,'2014-04-30' union all
select 3,222,500,100,100,100,'2014-05-03'select * from [TB]SELECT 订单号
FROM ( SELECT 订单号 ,
MIN(订单总重量) AS 订单总重量 ,
SUM(完成重量1 + 完成重量2 + 完成重量3) AS 完成总重量
FROM TB
GROUP BY 订单号
) T
WHERE 订单总重量 <> 完成总重量
if object_id('[ta]') is not null
drop table [ta]
go
create table [ta] ([id] int,[订单号] int,[订单总重量] int,[完成重量1] int,[完成重量2] int,[完成重量3] int,[生产日期] datetime)
insert into [ta]
select 1,111,1000,200,200,100,'2014-04-29' union all
select 2,111,1000,100,200,200,'2014-04-30' union all
select 3,222,500,100,100,100,'2014-05-03'select 订单号
from ta
group by 订单号,订单总重量
having 订单总重量!=SUM(完成重量1+完成重量2+完成重量3)222
WHERE 订单总重量!=SUM(完成重量1 + 完成重量2 + 完成重量3)