22,209
社区成员
发帖
与我相关
我的任务
分享
select * ,
case when (select count(1) from 部门帐户表 where 帐号=a.帐号) >1 then 'xxx'
else (select 部门 from 部门帐户表 where 帐号=a.帐号) end
from 收到流水帐 a
create table 部门帐户表(帐号 varchar(10),部门 varchar(10))
insert into 部门帐户表
select 'A001','A2部' union all
select 'A001','A1部' union all
select 'A001','A3部' union all
select 'A001','A4部' union all
select 'A002','A02部'
create table 收到流水帐(日期 datetime,金额 int,帐号 varchar(10))
insert into 收到流水帐
select '20110601',3000,'A001' union all
select '20110602',4000,'A002'
go
select *,
case when (select count(*)c from 部门帐户表 where 帐号=a.帐号)=1 then
(select top 1 部门 from 部门帐户表 where 帐号=a.帐号)
else 'XXX' end
from 收到流水帐 a
/*
日期 金额 帐号
----------------------- ----------- ---------- ----------
2011-06-01 00:00:00.000 3000 A001 XXX
2011-06-02 00:00:00.000 4000 A002 A02部
(2 行受影响)
*/
go
drop table 部门帐户表,收到流水帐
declare @部门帐户表 table (帐号 varchar(4),部门 varchar(4))
insert into @部门帐户表
select 'A001','A2部' union all
select 'A001','A1部' union all
select 'A001','A3部' union all
select 'A001','A4部'
declare @收到流水帐 table (日期 datetime,金额 int,帐号 varchar(4))
insert into @收到流水帐
select '20110601',3000,'A001' union all
select '20110602',4000,'A001'
select *,
(select top 1 部门 from @部门帐户表 where 帐号=a.帐号 order by newid())
from @收到流水帐 a
/*
日期 金额 帐号
----------------------- ----------- ---- ----
2011-06-01 00:00:00.000 3000 A001 A3部
2011-06-02 00:00:00.000 4000 A001 A4部
*/
select *,
isnull((select top 1 部门 from 部门帐户表 where 帐号=a.帐号 order by newid()),'XX')部门
from 收到流水帐 a
;with f as
(select
a.部门,b.*
from
a,b
where
a.帐号=b.帐号)
select * from f t where 部门=(select min(部门) from f where 日期=t.日期 金额=t. 金额 and 帐号=t.帐号)
select a.*,isnull(b.部门,'XX') from
(select row_number() over (partition by 帐号 order by 日期) as id , * from 收到流水帐) a
left join
(select row_number() over (partition by 帐号 order by 部门) as id , * from 部门帐户表 ) b
on a.帐号 =b.帐号 and a.id = b.id
select *,
(select top 1 部门 from 部门帐户表 where 帐号=a.帐号 order by newid())部门
from 收到流水帐 a
SELECT *,'xx' FROM 收到流水帐
SELECT *,(SELECT TOP 1 部门 FROM 部门帐户表 WHERE 账号=A.账号 ORDER BY NEWID()) 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