insert into E (除id的字段列表)
select * ,报名费小计+手续费小计+装修费小计+运费小计 as 合计from
(
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 运费小计
from A left join b on A.序列号=B.序列号
left join c on A.序列号=C.序列号
left join d on A.序列号=d.序列号) a
where 报名费小计+手续费小计+装修费小计+运费小计>2000
select * ,报名费小计+手续费小计+装修费小计+运费小计 as 合计from
(
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 运费小计
from A left join b on A.序列号=B.序列号
left join c on A.序列号=C.序列号
left join d on A.序列号=d.序列号) a
where 报名费小计+手续费小计+装修费小计+运费小计>2000
select 所需字段
from a left join b on a.序列号=b.序列号
left join c on a.序列号=c.序列号
left join d on a.序列号=d.序列号
where isnull(b.报名费,0)-isnull(d.退报名费,0)+isnull(b.手续费1,0)+
isnull(b.手续费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)
isnull(c.运费1,0)+isnull(c.运费2,0)-isnull(d.退运费1,0)-isnull(d.退运费2,0)>2000
select 所需字段
from a left join b on a.序列号=b.序列号
left join c on a.序列号=c.序列号
left join d on a.序列号=d.序列号
where isnull(b.报名费,0)-isnull(d.退报名费,0)+isnull(b.手续费1,0)+
isnull(b.手续费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)+
isnull(c.运费1,0)+isnull(c.运费2,0)-isnull(d.退运费1,0)-isnull(d.退运费2,0)>2000
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 合计>2000
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 合计>2000
select * ,报名费小计+手续费小计+装修费小计+运费小计 as 合计from
(
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 运费小计
from A left join b on A.序列号=B.序列号
left join c on A.序列号=C.序列号
left join d on A.序列号=d.序列号) a
where 报名费小计+手续费小计+装修费小计+运费小计>2000
可能是我描述的不详细!
比如 在表A里 有5个人的记录(王一,李二,张三,刘丽,杨华)
B , C , D 表里面只有王一的记录。
B表里面有李二的记录
可是用你们的办法按条件查询的话只能查出每个表里都存在的 ”王一“ 的记录,
比如合计=<0 应该表A里的五个人都显示出来,
可是用你们的办法无法实现!
也就是说B,C,D表里不存在的人的合计=0 来计算。