SQLServer不同行列相减实例解析

发布时间：2020-12-12 16:02:35 所属栏目：MsSql教程来源：网络整理

导读：? 昨天技术主管面试新人时出了道数据库编程题，自己也是新人，所以顺便拿过来研究一下，题目如下： ? 数据库表TEST，表结构及数据如下：? CREATE TABLE TEST（ ?? ID char（10）PRIMARY KEY， ?? NAME char（10）， ?? AMOUNT numeric（9））? ID???????? N

? 昨天技术主管面试新人时出了道数据库编程题，自己也是新人，所以顺便拿过来研究一下，题目如下： ? 数据库表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 105????????cr?????????500 ? ? 用一条SQL语句得出以下查询结果（要求SQL的查询性能和效率为最高）： ? ID???????? AMOUNT -------------------- 101?????? ?350
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 ? 出现了一条空值，因为ID='105'的记录只有一条，没有与之相匹配的记录。 ? 再看下面的语句： ? select id,sum((case name?? WHEN 'dr' THEN 1 ELSE -1 END)*amount) as amount??
?????? from test
?????? group by id ? select id,sum(case name?? WHEN 'dr' THEN amount ELSE -amount END) as amount??
?????? 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???? ?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 ?,2,'##','0',? 200.0000 ? union ? all ?
? select ? 1003 ?,200.0000 ? union ? all ?
? select ? 1003 ?,'MD',3,4,5,6,? 120.0000 ? union ? all ?
? select ? 1004 ?,250.0000 ? union ? all ?
? select ? 1004 ?,'AC',? 250.0000 ? union ? all ?
? select ? 1004 ?,? 200.0000 ? union ? all ?
? select ? 1004 ?,? 100.0000 ? union ? all ?
? select ? 1004 ?,? 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,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,jd1=isnull(a.je,0),jd0=isnull(b.je,0)?? into?? #1?? from??
? (select?? zh,je=sum(je)?? from?? t_a??
? where?? jdflag=1?? group?? by?? zh,fkcode)a??
? left?? join????
? (select?? zh,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,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,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,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,je=jd1?? from?? #1??
? where?? jd1>0 ? ? drop table #1 ? ? 解法一利用视图，写法相对简单点，但是我的水平很次啊，要我写还真不一定能写出来，大家一起研究一下吧。?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!