27,579
社区成员
发帖
与我相关
我的任务
分享
declare @Emppayroll table(ID int,Employee varchar(10),Bank varchar(20))
insert into @Emppayroll select 1,'employee1', '工商银行'
union all select 2,'employee2', '工商银行'
union all select 3,'employee3', '工商银行'
union all select 4,'employee4', '工商银行'
declare @BankAccount table(ID int,Employee varchar(10),Bank varchar(20),Account varchar(20),IsDefault int)
insert into @BankAccount select 1,'employee1','工商银行',1111111,0
union all select 2,'employee1','工商银行',1111122,1
union all select 3,'employee1','工商银行',1111133,0
union all select 4,'employee2','工商银行',2222211,1
union all select 5,'employee3','工商银行',3333311,0
union all select 6,'employee4','工商银行',4444411,0
union all select 7,'employee4','工商银行',4444422,0
select a.Employee,a.bank,[account]=(select top 1 b.account from @BankAccount b
where b.Employee=a.Employee and b.bank=a.bank order by IsDefault desc,id)
from @Emppayroll a
/*
employee1 工商银行 1111122
employee2 工商银行 2222211
employee3 工商银行 3333311
employee4 工商银行 4444411
*/
select id, max(isnull(isdefault,0)) from table group by id
select a.* , b.* from BankAccount a,Emppayroll b where a.employee = b.employee and a.bank = b.bank and a.IsDefault = ?
不知道你的 IsDefault 字段是什么,定义的标准是什么,如果下面这句不行,那就吧 desc 去掉就可以了
select top 1 * from BankAccount where ID=职工ID order by IsDefault desc