第1个回答 2009-08-15
select a.number,a.name,a.bank from 人员 a
where a.number not in(select 工资数据.num from 工资数据);
select a.name,b.imonth,b.jb_gz,b.jj_gz,b.kk_gz,(jb_gz+jj_gz+kk_gz) as sj_gz
)
from 人员 a
inner join 工资数据 b on a.number=b.num;
要下班了 没有检测,请你注意测试一下。
第2个回答 2009-08-15
保证数据正确
以下代码直接可执行
此代码为临时代码
执行完毕后不会有任何数据残存
请您放心使用
declare @member table
(
num char(2) ,
[name] nvarchar(10),
bank varchar(20)
)
insert @member(num,[name],bank)
select '01','张三','1000001'
union select '02','李四','1000002'
union select '03','王五','1000003'
union select '05','孔八','1000004'
declare @Num table
(
num char(2) ,
imonth char(2),
jb_gz int,
jj_gz int ,
kk_gz int
)
insert @Num (num, imonth,jb_gz,jj_gz, kk_gz)
select '01','06', 1000,600, 100
union select '02','06', 1200,800, 300
union select '03','06', 800, 200, 100
union select '04','06', 2000,500, 200
--全部信息
select * from @member as m right join @Num as n on (n.num=m.num)
--缺员工资信息
select n.* from @member as m right join @Num as n on (n.num=m.num) where m.num is null
--计算工资
select 员工=m.name,实际工资=(n.jb_gz+n.jj_gz-n.kk_gz) from @member as m inner join @Num as n on (n.num=m.num)
第3个回答 2009-08-15
问题一:select * from 表1 where num not in (select num from 表2)
问题二:select a.num,b.name,b.bank,a.imonth,a.jb_gz,a.jj_gz,a.kk_gz,jb_gz+jj_gz-kk_gz as sj_gz from 表2 a left join 表1 b on a.num=b.num where a.month='06'
以上希望对你有所帮助