昨天技术主管面试新人时出了道数据库编程题,自己也是新人,所以顺便拿过来研究一下,题目如下:
数据库表TEST,表结构及数据如下:
CREATE TABLE TEST(
ID char(10)PRIMARY KEY,
NAME char(10),
AMOUNT numeric(9)
)
ID NAME AMOUNT
-----------------------------
101 dr 100
102 cr 200
101 cr 50
102 dr 150
101 dr 300
103 dr 300
103 cr 300
104 cr 345
104 dr 355
104 dr 225
105 dr 225
102 cr 200
101 cr 50
102 dr 150
101 dr 300
103 dr 300
103 cr 300
104 cr 345
104 dr 355
104 dr 225
105 dr 225
105 cr 500
用一条SQL语句得出以下查询结果(要求SQL的查询性能和效率为最高):
ID AMOUNT
--------------------
101 350
102 -50
103 0
104 235
105 -275
102 -50
103 0
104 235
105 -275
要得出以上结果,先得分析一下,可以看出是将ID相同并且NAME为dr的AMOUNT减去NAME为cr的AMOUNT得出。
在不考虑效率的情况下,可使用以下SQL语句:
select id,amount=(select sum(amount) from test where name='dr' and id=t.id)
-(select sum(amount) from test where name='cr'and id=t.id)
from test t group by id
但这条语句还是有问题,例如:当去掉
ID NAME AMOUNT
----------------------------
105 cr 500
这条记录后,查询结果如下:
ID AMOUNT
--------------------
101 350
102 -50
103 0
104 235
105 NULL
102 -50
103 0
104 235
105 NULL
出现了一条空值,因为ID='105'的记录只有一条,没有与之相匹配的记录。
再看下面的语句:
select id, sum((case name WHEN 'dr' THEN 1 ELSE -1 END)*amount) as amount
from test
group by id
from test
group by id
select id, sum(case name WHEN 'dr' THEN amount ELSE -amount END) as amount
from test
group by id
from test
group by id
这两条语句的效率和查询结果等同,也是我们所要的SQL语句。其实要获得高效率的SQL语句,解题的思路很重要,这里运用SQLServer的CASE函数实现将AMOUNT字段的值根据NAME的不同赋不同的值(加个负号),再用sum函数实现数据的相减。
让我们再看一个复杂点的不同行列相减实例
表t_a中数据:
zh zhlb xh fkcode jdflag je
--------- -------- ------- -------- ------ ----------
1002 F 1 RMB 1 400.0000
1002 F 2 ## 0 200.0000
1003 F 1 RMB 1 200.0000
1003 F 2 MD 1 200.0000
1003 F 3 MD 1 200.0000
1003 F 4 ## 0 200.0000
1003 F 5 ## 0 200.0000
1003 F 6 ## 0 120.0000
1004 F 1 RMB 1 250.0000
1004 F 2 AC 1 250.0000
1004 F 3 MD 1 200.0000
1004 F 4 ## 0 100.0000
1004 F 5 ## 0 50.0000
zh zhlb xh fkcode jdflag je
--------- -------- ------- -------- ------ ----------
1002 F 1 RMB 1 400.0000
1002 F 2 ## 0 200.0000
1003 F 1 RMB 1 200.0000
1003 F 2 MD 1 200.0000
1003 F 3 MD 1 200.0000
1003 F 4 ## 0 200.0000
1003 F 5 ## 0 200.0000
1003 F 6 ## 0 120.0000
1004 F 1 RMB 1 250.0000
1004 F 2 AC 1 250.0000
1004 F 3 MD 1 200.0000
1004 F 4 ## 0 100.0000
1004 F 5 ## 0 50.0000
根据要求得出如下数据:
zh zhlb fkcode je
------- ---- ---------- ---------
1002 F RMB 200.0000
1003 F RMB 80.0000
1004 F AC 250.0000
1004 F MD 50.0000
1004 F RMB 250.0000
要求:
1. zh,zhlb相同的数据中,把jdflag为1的数据的je减jdflag为0的je
2. fkcode中MD的先减,RMB的后减
表t_a:
if exists( SELECT * FROM sysobjects WHERE xtype = 'u' and name = 't_a' )
drop table t_a
create table t_a (zh decimal,zhlb char(1),xh integer,fkcode char(10), jdflag char(1), je money default 0)
insert t_a
select 1002 ,'F',1,'RMB','1',400.0000 union all
select 1002 ,'F',2,'##','0', 200.0000 union all
select 1003 ,'F',1,'RMB','1',200.0000 union all
select 1003 ,'F',2,'MD','1', 200.0000 union all
select 1003 ,'F',3,'MD','1', 200.0000 union all
select 1003 ,'F',4,'##','0', 200.0000 union all
select 1003 ,'F',5,'##','0', 200.0000 union all
select 1003 ,'F',6,'##','0', 120.0000 union all
select 1004 ,'F',1,'RMB','1',250.0000 union all
select 1004 ,'F',2,'AC','1', 250.0000 union all
select 1004 ,'F',3,'MD','1', 200.0000 union all
select 1004 ,'F',4,'##','0', 100.0000 union all
select 1004 ,'F',5,'##','0', 50.0000
这里字段较多,要求也复杂些,下面有两种解法:
解一:
if exists(select 1 from sysobjects where id=object_id(N'v_a') and xtype='V')
drop view v_a
go
create view v_a
as
select zh,zhlb,fkcode,newcode=case fkcode when 'MD' then 1 when 'AC' then 2 when 'RMB' then 3 else 0 end,je=sum(case jdflag when 1 then je else -je end)
from t_a
group by zh,zhlb,fkcode
go
select t.* from
(select a.zh,
a.zhlb,
a.fkcode,
je=case when (select sum(je) from v_a c where c.newcode<a.newcode and c.zh=a.zh and c.zhlb=a.zhlb)<=0
then (select sum(je) from v_a b where b.newcode<=a.newcode and b.zh=a.zh and b.zhlb=a.zhlb)
else je end
from v_a a
where a.je>0 and fkcode<>'##'
) t
where je>0
order by t.zh,t.zhlb,t.fkcode
drop view v_a
drop view v_a
go
create view v_a
as
select zh,zhlb,fkcode,newcode=case fkcode when 'MD' then 1 when 'AC' then 2 when 'RMB' then 3 else 0 end,je=sum(case jdflag when 1 then je else -je end)
from t_a
group by zh,zhlb,fkcode
go
select t.* from
(select a.zh,
a.zhlb,
a.fkcode,
je=case when (select sum(je) from v_a c where c.newcode<a.newcode and c.zh=a.zh and c.zhlb=a.zhlb)<=0
then (select sum(je) from v_a b where b.newcode<=a.newcode and b.zh=a.zh and b.zhlb=a.zhlb)
else je end
from v_a a
where a.je>0 and fkcode<>'##'
) t
where je>0
order by t.zh,t.zhlb,t.fkcode
drop view v_a
解二:
select a.zh,a.zhlb,fkcode,jd1=isnull(a.je,0),jd0=isnull(b.je,0) into #1 from
(select zh,zhlb,fkcode,je=sum(je) from t_a
where jdflag=1 group by zh,zhlb,fkcode)a
left join
(select zh,zhlb,je=sum(je) from t_a where jdflag=0
group by zh,zhlb) b
on a.zh=b.zh and a.zhlb=b.zhlb
--补全所有的记录
insert into #1
select distinct b.zh,b.zhlb,a.fkcode,isnull(c.jd1,0),b.jd0 from #1 b
left join (Select distinct fkcode from #1)a
on 1>0
left join #1 c
on c.zh=b.zh and c.zhlb=b.zhlb and c.fkcode=a.fkcode
where c.jd1 is null
update #1 set jd1=jd1-jd0
where fkcode='Md'
update #1 set jd1=a.jd1+b.jd1
from #1 a
inner join (select zh,zhlb,jd1 from #1 where fkcode='md' and jd1<0)b
on a.fkcode='AC' and a.zh=b.zh and a.zhlb=b.zhlb
update #1 set jd1=a.jd1+b.jd1
from #1 a
inner join (select zh,zhlb,jd1 from #1 where fkcode='AC' and jd1<0)b
on a.fkcode='RMB' and a.zh=b.zh and a.zhlb=b.zhlb
select zh,zhlb,fkcode,je=jd1 from #1
where jd1>0
(select zh,zhlb,fkcode,je=sum(je) from t_a
where jdflag=1 group by zh,zhlb,fkcode)a
left join
(select zh,zhlb,je=sum(je) from t_a where jdflag=0
group by zh,zhlb) b
on a.zh=b.zh and a.zhlb=b.zhlb
--补全所有的记录
insert into #1
select distinct b.zh,b.zhlb,a.fkcode,isnull(c.jd1,0),b.jd0 from #1 b
left join (Select distinct fkcode from #1)a
on 1>0
left join #1 c
on c.zh=b.zh and c.zhlb=b.zhlb and c.fkcode=a.fkcode
where c.jd1 is null
update #1 set jd1=jd1-jd0
where fkcode='Md'
update #1 set jd1=a.jd1+b.jd1
from #1 a
inner join (select zh,zhlb,jd1 from #1 where fkcode='md' and jd1<0)b
on a.fkcode='AC' and a.zh=b.zh and a.zhlb=b.zhlb
update #1 set jd1=a.jd1+b.jd1
from #1 a
inner join (select zh,zhlb,jd1 from #1 where fkcode='AC' and jd1<0)b
on a.fkcode='RMB' and a.zh=b.zh and a.zhlb=b.zhlb
select zh,zhlb,fkcode,je=jd1 from #1
where jd1>0
drop table #1
解法一利用视图,写法相对简单点,但是我的水平很次啊,要我写还真不一定能写出来,大家一起研究一下吧。