27,579
社区成员
发帖
与我相关
我的任务
分享
create table a(zhanghu varchar(50),bumen varchar(50))
create table b(riqi varchar(50),moneys varchar(50),zhanghu varchar(50))
insert a
select 'A001','A2部'
union all
select 'A001','A1部'
union all
select 'A001','A3部'
union all
select 'A001','A4部'
insert b
select '20110601','3000','A001'
union all
select '20110602','4000','A001'
select distinct riqi,moneys,a.zhanghu,bumen=(select top 1 bumen from a) from
a,b where a.zhanghu=b.zhanghu
select distinct riqi,moneys,a.zhanghu,bumen=(select top 1 bumen from a) from a,b where a.zhanghu=b.zhanghu
select * ,
case when (select count(1) from 部门帐户表 where 帐号=a.帐号) >1 then 'xxx'
else (select top 1 部门 from 部门帐户表 where 帐号=a.帐号) end
from 收到流水帐 a
select 日期,金额,帐号,case when 部门次数>1 then 'XX' else 部门 end as 部门
from
(
select a.日期,a.金额,a.帐号,count(*) as 部门次数,max(b.部门) as 部门
from 收到流水帐 a ,部门帐户表 b
where a.帐号=b.帐号
group by a.日期,a.金额,a.帐号
) tb
select * ,(case 金额 when 3000 then 'A1部' end)as 部门 from 收到流水帐 where 金额=3000
union
select * ,(case 金额 when 4000 then 'A2部' end)as 部门 from 收到流水帐 where 金额=4000