insert E (姓名,性别,年龄,报名费,手续费1,手续费2,装修费1,装修费2,运费1,运费1,退报名费,退手续费1,退手续费2,退装修费1,退装修费1,退运费1,退运费2,报名费小计,手续费小计,装修费小计,运费小计,合计) select *,报名费小计+手续费小计+装修费小计+运费小计 from (select a.姓名,a.性别,a.年龄,b.装修费1,b.手续费2,c.装修费1,c.装修费2,c.运费1,c.运费1,b.报名费-d退报名费 报名费小计, isnull(b.手续费1,0)+isnull(b.手续费2,0)-isnull(d.退手续费1,0)-isnull(d.退手续费1,0) 手续费小计, isnull(c.装修费1,0)+isnull(c.装修费2,0)-isnull(d.退装修费1,0)-isnull(d.退装修费2,0) 装修费小计, isnull(c.运费1,0)+isnull(c.运费2,0)-isnull(d.退运费1,0)-isnull(d.退运费2,0) 运费小计 from a left join b on a.序列号=b.序列号 left join c on a.序列号=c.序列号 left join d on a.序列号=d.序列号 ) tem where 报名费小计+手续费小计+装修费小计+运费小计>@你的条件数
insert E (序列号,姓名,性别,年龄,报名费,手续费1,手续费2,装修费1,装修费2,运费1,运费1,退报名费,退手续费1,退手续费2,退装修费1,退装修费1,退运费1,退运费2,报名费小计,手续费小计,装修费小计,运费小计,合计) (SELECT A.序列号, A.姓名,A.性别,A.年龄,B.报名费,B.手续费1,B.手续费2,C.装修费1,C.装修费2,C.运费1,C.运费2,D.退报名费,D.退手续费1,D.退手续费2,D.退装修费1,D.退装修费1,D.退运费1,D.退运费2, (CASE WHEN B.报名费 > 0 THEN B.报名费 ELSE 0 END)- (CASE WHEN D.退报名费 > 0 THEN D.退报名费 ELSE 0 END), (CASE WHEN B.手续费1 > 0 THEN B.手续费1 ELSE 0 END) + (CASE WHEN B.手续费2 > 0 THEN B.手续费2 ELSE 0 END) - (CASE WHEN D.退手续费1 > 0 THEN D.退手续费1 ELSE 0 END) - (CASE WHEN D.退手续费1 > 0 THEN D.退手续费1 ELSE 0 END) , (CASE WHEN C.装修费1 > 0 THEN C.装修费1 ELSE 0 END) + (CASE WHEN C.装修费2 > 0 THEN C.装修费2 ELSE 0 END) - (CASE WHEN D.退装修费1 > 0 THEN D.退装修费1 ELSE 0 END) - (CASE WHEN D.退装修费2 > 0 THEN D.退装修费2 ELSE 0 END), (CASE WHEN C.运费1 > 0 THEN C.运费1 ELSE 0 END) + (CASE WHEN C.运费2 > 0 THEN C.运费2 ELSE 0 END)- (CASE WHEN D.退运费1 > 0 THEN D.退运费1 ELSE 0 END) - (CASE WHEN D.退运费2 > 0 THEN D.退运费2 ELSE 0 END), (CASE WHEN B.报名费 > 0 THEN B.报名费 ELSE 0 END)- (CASE WHEN D.退报名费 > 0 THEN D.退报名费 ELSE 0 END) + (CASE WHEN B.手续费1 > 0 THEN B.手续费1 ELSE 0 END) + (CASE WHEN B.手续费2 > 0 THEN B.手续费2 ELSE 0 END) - (CASE WHEN D.退手续费1 > 0 THEN D.退手续费1 ELSE 0 END) - (CASE WHEN D.退手续费1 > 0 THEN D.退手续费1 ELSE 0 END) + (CASE WHEN C.装修费1 > 0 THEN C.装修费1 ELSE 0 END) + (CASE WHEN C.装修费2 > 0 THEN C.装修费2 ELSE 0 END) - (CASE WHEN D.退装修费1 > 0 THEN D.退装修费1 ELSE 0 END) - (CASE WHEN D.退装修费2 > 0 THEN D.退装修费2 ELSE 0 END) + (CASE WHEN C.运费1 > 0 THEN C.运费1 ELSE 0 END) + (CASE WHEN C.运费2 > 0 THEN C.运费2 ELSE 0 END)- (CASE WHEN D.退运费1 > 0 THEN D.退运费1 ELSE 0 END) - (CASE WHEN D.退运费2 > 0 THEN D.退运费2 ELSE 0 END) FROM A,B,C,D WHERE A.序列号 *= B.序列号 AND A.序列号 *= C.序列号 AND A.序列号 *= D.序列号 AND (CASE WHEN B.报名费 > 0 THEN B.报名费 ELSE 0 END)- (CASE WHEN D.退报名费 > 0 THEN D.退报名费 ELSE 0 END) + (CASE WHEN B.手续费1 > 0 THEN B.手续费1 ELSE 0 END) + (CASE WHEN B.手续费2 > 0 THEN B.手续费2 ELSE 0 END) - (CASE WHEN D.退手续费1 > 0 THEN D.退手续费1 ELSE 0 END) - (CASE WHEN D.退手续费1 > 0 THEN D.退手续费1 ELSE 0 END) + (CASE WHEN C.装修费1 > 0 THEN C.装修费1 ELSE 0 END) + (CASE WHEN C.装修费2 > 0 THEN C.装修费2 ELSE 0 END) - (CASE WHEN D.退装修费1 > 0 THEN D.退装修费1 ELSE 0 END) - (CASE WHEN D.退装修费2 > 0 THEN D.退装修费2 ELSE 0 END) + (CASE WHEN C.运费1 > 0 THEN C.运费1 ELSE 0 END) + (CASE WHEN C.运费2 > 0 THEN C.运费2 ELSE 0 END)- (CASE WHEN D.退运费1 > 0 THEN D.退运费1 ELSE 0 END) - (CASE WHEN D.退运费2 > 0 THEN D.退运费2 ELSE 0 END) >= 0)
insert into E select a.序列号,a.姓名,a.性别,a.年龄, isnull(b.报名费,0), isnull(手续费1,0), isnull(b.手续费2,0), isnull(c.装修费1,0), isnull(c.装修费2,0), isnull(c.运费1,0), isnull(c.运费1,0), isnull(d.退报名费,0), isnull(d.退手续费1,0), isnull(退手续费2,0), isnull(d.退装修费1,0), isnull(d.退运费1,0), isnull(d.退运费2,0), isnull(b.报名费,0)-isnull(d.退报名费,0) as 报名费小计, isnull(b.手续费1,0)+isnull(b.手续费2,0)-isnull(d.退手续费1,0)-isnull(d.退手续费1,0) as 手续费小计, isnull(c.装修费1,0)+isnull(装修费2,0)-isnull(d.退装修费1,0)-isnull(d.退装修费2,0) as 装修费小计, isnull(c.运费1,0)+isnull(c.运费2,0)-isnull(d.退运费1,0)-isnull(退运费2,0) as 运费小计, [报名费小计+手续费小计+装修费小计+运费小计] as 合计 from A left join b on a.序列号=b.序列号 left join c on a.序列号=c.序列号 left join d on a.序列号=d.序列号where 合计>0
select *,报名费小计+手续费小计+装修费小计+运费小计 from
(select a.姓名,a.性别,a.年龄,b.装修费1,b.手续费2,c.装修费1,c.装修费2,c.运费1,c.运费1,b.报名费-d退报名费 报名费小计,
isnull(b.手续费1,0)+isnull(b.手续费2,0)-isnull(d.退手续费1,0)-isnull(d.退手续费1,0) 手续费小计,
isnull(c.装修费1,0)+isnull(c.装修费2,0)-isnull(d.退装修费1,0)-isnull(d.退装修费2,0) 装修费小计,
isnull(c.运费1,0)+isnull(c.运费2,0)-isnull(d.退运费1,0)-isnull(d.退运费2,0) 运费小计
from a left join b on a.序列号=b.序列号 left join c on a.序列号=c.序列号 left join d on a.序列号=d.序列号
) tem where 报名费小计+手续费小计+装修费小计+运费小计>@你的条件数
(CASE WHEN B.报名费 > 0 THEN B.报名费 ELSE 0 END)- (CASE WHEN D.退报名费 > 0 THEN D.退报名费 ELSE 0 END),
(CASE WHEN B.手续费1 > 0 THEN B.手续费1 ELSE 0 END) + (CASE WHEN B.手续费2 > 0 THEN B.手续费2 ELSE 0 END) -
(CASE WHEN D.退手续费1 > 0 THEN D.退手续费1 ELSE 0 END) - (CASE WHEN D.退手续费1 > 0 THEN D.退手续费1 ELSE 0 END) ,
(CASE WHEN C.装修费1 > 0 THEN C.装修费1 ELSE 0 END) + (CASE WHEN C.装修费2 > 0 THEN C.装修费2 ELSE 0 END) -
(CASE WHEN D.退装修费1 > 0 THEN D.退装修费1 ELSE 0 END) - (CASE WHEN D.退装修费2 > 0 THEN D.退装修费2 ELSE 0 END),
(CASE WHEN C.运费1 > 0 THEN C.运费1 ELSE 0 END) + (CASE WHEN C.运费2 > 0 THEN C.运费2 ELSE 0 END)-
(CASE WHEN D.退运费1 > 0 THEN D.退运费1 ELSE 0 END) - (CASE WHEN D.退运费2 > 0 THEN D.退运费2 ELSE 0 END),
(CASE WHEN B.报名费 > 0 THEN B.报名费 ELSE 0 END)- (CASE WHEN D.退报名费 > 0 THEN D.退报名费 ELSE 0 END) +
(CASE WHEN B.手续费1 > 0 THEN B.手续费1 ELSE 0 END) + (CASE WHEN B.手续费2 > 0 THEN B.手续费2 ELSE 0 END) -
(CASE WHEN D.退手续费1 > 0 THEN D.退手续费1 ELSE 0 END) - (CASE WHEN D.退手续费1 > 0 THEN D.退手续费1 ELSE 0 END) +
(CASE WHEN C.装修费1 > 0 THEN C.装修费1 ELSE 0 END) + (CASE WHEN C.装修费2 > 0 THEN C.装修费2 ELSE 0 END) -
(CASE WHEN D.退装修费1 > 0 THEN D.退装修费1 ELSE 0 END) - (CASE WHEN D.退装修费2 > 0 THEN D.退装修费2 ELSE 0 END) +
(CASE WHEN C.运费1 > 0 THEN C.运费1 ELSE 0 END) + (CASE WHEN C.运费2 > 0 THEN C.运费2 ELSE 0 END)-
(CASE WHEN D.退运费1 > 0 THEN D.退运费1 ELSE 0 END) - (CASE WHEN D.退运费2 > 0 THEN D.退运费2 ELSE 0 END)
FROM A,B,C,D WHERE
A.序列号 *= B.序列号 AND A.序列号 *= C.序列号 AND A.序列号 *= D.序列号 AND
(CASE WHEN B.报名费 > 0 THEN B.报名费 ELSE 0 END)- (CASE WHEN D.退报名费 > 0 THEN D.退报名费 ELSE 0 END) +
(CASE WHEN B.手续费1 > 0 THEN B.手续费1 ELSE 0 END) + (CASE WHEN B.手续费2 > 0 THEN B.手续费2 ELSE 0 END) -
(CASE WHEN D.退手续费1 > 0 THEN D.退手续费1 ELSE 0 END) - (CASE WHEN D.退手续费1 > 0 THEN D.退手续费1 ELSE 0 END) +
(CASE WHEN C.装修费1 > 0 THEN C.装修费1 ELSE 0 END) + (CASE WHEN C.装修费2 > 0 THEN C.装修费2 ELSE 0 END) -
(CASE WHEN D.退装修费1 > 0 THEN D.退装修费1 ELSE 0 END) - (CASE WHEN D.退装修费2 > 0 THEN D.退装修费2 ELSE 0 END) +
(CASE WHEN C.运费1 > 0 THEN C.运费1 ELSE 0 END) + (CASE WHEN C.运费2 > 0 THEN C.运费2 ELSE 0 END)-
(CASE WHEN D.退运费1 > 0 THEN D.退运费1 ELSE 0 END) - (CASE WHEN D.退运费2 > 0 THEN D.退运费2 ELSE 0 END) >= 0)
select a.序列号,a.姓名,a.性别,a.年龄,
isnull(b.报名费,0),
isnull(手续费1,0),
isnull(b.手续费2,0),
isnull(c.装修费1,0),
isnull(c.装修费2,0),
isnull(c.运费1,0),
isnull(c.运费1,0),
isnull(d.退报名费,0),
isnull(d.退手续费1,0),
isnull(退手续费2,0),
isnull(d.退装修费1,0),
isnull(d.退运费1,0),
isnull(d.退运费2,0),
isnull(b.报名费,0)-isnull(d.退报名费,0) as 报名费小计,
isnull(b.手续费1,0)+isnull(b.手续费2,0)-isnull(d.退手续费1,0)-isnull(d.退手续费1,0) as 手续费小计,
isnull(c.装修费1,0)+isnull(装修费2,0)-isnull(d.退装修费1,0)-isnull(d.退装修费2,0) as 装修费小计,
isnull(c.运费1,0)+isnull(c.运费2,0)-isnull(d.退运费1,0)-isnull(退运费2,0) as 运费小计,
[报名费小计+手续费小计+装修费小计+运费小计] as 合计
from A left join b on a.序列号=b.序列号
left join c on a.序列号=c.序列号
left join d on a.序列号=d.序列号where 合计>0