合并SQL语句

Question

select sum(a.totalmoney) from pikerecord a,vehicleregister b where a.vehiclenumber =b.vehiclenumber and b.enterpriseid='dw03'；select sum(a.totalmoney) from pikerecord a,vehiclelogout b where  b.vehiclenumber not in (select vehiclenumber from vehicleregister ) and  a.vehiclenumber =b.vehiclenumber and b.enterpriseid='dw03' ；上面两条SQL语句得到了两个值，分别是pikerecord 和vehicleregister 关联查询。pikerecord 和vehiclelogout 查询。有没有办法把两条SQL语句写成一条SQL语句呢？

Answer 1

select sum(a.totalmoney),
  sum(case when not exists(select 1 from vehicleregister where vehiclenumber=b.vehiclenumber) then a.totalmoney end) 
from pikerecord a,vehiclelogout b 
where  a.vehiclenumber =b.vehiclenumber and b.enterpriseid='dw03' ；

Answer 2

select sum(case when exists (select 1 from vehicleregister b 
                             where b.vehiclenumber = a.vehiclenumber 
                             and b.enterpriseid = 'dw03')
                then a.totalmoney
           end),
       sum(case when exists (select 1 from vehiclelogout b 
                             where b.vehiclenumber = a.vehiclenumber 
                             and b.enterpriseid = 'dw03'
                             and not exists(select 1 from vehicleregister 
                                            where b.vehiclenumber = vehiclenumber))
                then a.totalmoney
           end)
from  pikerecord a;如果vehiclelogout.enterpriseid='dw03'对应在vehicleregister中的enterpriseid也是'dw03'，那么还可以将第二个case中的not exists去掉，用第二个sum减去第一个sum

Answer 3

楼上的有问题啊！得出的结果和我计算的不相符？我想得到的是，第一条sql得出1000，第二条得出的 是200.最后的结果我要的是1200.而不是两个结果集。另外，第一条结果集计算出来的是错的。

Answer 4

select sum(case when exists (select 1 from vehicleregister b 
                             where b.vehiclenumber = a.vehiclenumber 
                             and b.enterpriseid = 'dw03')
                then a.totalmoney
                when exists (select 1 from vehiclelogout b 
                             where b.vehiclenumber = a.vehiclenumber 
                             and b.enterpriseid = 'dw03'
                             and not exists(select 1 from vehicleregister 
                                            where b.vehiclenumber = vehiclenumber))
                then a.totalmoney
           end)
from  pikerecord a;这样？

Answer 5

select sum(case when exists (select 1 from vehicleregister b 
                             where b.vehiclenumber = a.vehiclenumber 
                             and b.enterpriseid = 'dw03')
                  or exists (select 1 from vehiclelogout b 
                             where b.vehiclenumber = a.vehiclenumber 
                             and b.enterpriseid = 'dw03')
                then a.totalmoney
           end)
from  pikerecord a;not exists去掉应该也可以

Answer 6

看看这个结果对不
select 
  sum(case when not exists(select 1 from vehicleregister where vehiclenumber=b.vehiclenumber) then 2*a.totalmoney else a.totalmoney end) 
from pikerecord a,vehiclelogout b 
where  a.vehiclenumber =b.vehiclenumber and b.enterpriseid='dw03' 

Answer 7

看错表名了。。
这样应该行了
select sum(totalmoney) from pikerecord a
where exists(select 1 from vehiclelogout where vehiclenumber=a.vehiclenumber)
   or exists(select 1 from vehicleregister where vehiclenumber=a.vehiclenumber)
就是求出a表中vehiclenumber存在于另外两个表中的记录的totalmoney的和吧

Answer 8

漏了个条件
select sum(totalmoney) from pikerecord a
where exists(select 1 from vehiclelogout where vehiclenumber=a.vehiclenumber and enterpriseid='dw03')
   or exists(select 1 from vehicleregister where vehiclenumber=a.vehiclenumber and enterpriseid='dw03')