SQLServer不同行列相减实例解析

 

昨天技术主管面试新人时出了道数据库编程题，自己也是新人，所以顺便拿过来研究一下，题目如下：

 

数据库表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   ,'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

 

 

解二：

 

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

 

  drop table #1

 

 

解法一利用视图，写法相对简单点，但是我的水平很次啊，要我写还真不一定能写出来，大家一起研究一下吧。