求助一个SQL

Question

表1(tb1)：Province P93      P97
101  7.03     7.66
101  null   7.67
101  7.11   7.67
102  7.03     7.66
102  7.08   null
102  7.11   7.67
表2(tb2)：Province guiding93     guidingP97
101  7.03          7.66
102  7.03          7.66
需求是表1与表2通过province联查，获取(表1中p93之和-表2guidingjia93之和)/表1数量sql如下:select Province,
       (sum(guidingjiaP93)-sum(Petrol93))/count(*), 
       (sum(guidingjiaP97)-sum(Petrol97))/count(*), 
 from tbl1 left join tb2 on tb1.province = tb2.province但还有个需求，当tb1中p93为null时，计算sum就不把为null的计算在内（当然97不为null的话，就还要计算），反之亦然。
请问应该如何做呢？不知道我有没有表达清楚，在线等谢谢

Answer 1

其实你加个isnull(p93,0)此时sum起来就不会有影响拉。不过还是没怎么看懂

Answer 2

而且sum其实也忽略了null值的，所以直接计算也不会影响啊，

Answer 3

但是count(*)没有忽略null哇……

Answer 4

比如这一行：
Province P93      P97
101     null      7.67你是想在count那里忽略，但是sum那里又保留是吧？

Answer 5

select 
  a.province,
  (b.guiding93-a.sum93)/a.cnt93,
  (guidingP97-a.sum97)/cnt97
from
(
  select province,sum(p93) sum93,count(p93) cnt93,sum(p97) sum97,count(p97) as cnt97
  from tb1
  group by province
) a
left join
(
  select province,sum(guiding93) as guiding93,sum(guidingP97) as guidingP97
  from tb2
  group by province
) b
on a.province=b.province

Answer 6

----------------------------------------------------------------
-- Author  :TravyLee(物是人非事事休，欲语泪先流！)
-- Date    :2012-10-10 09:51:57
-- Version:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
-- Jul  9 2008 14:43:34 
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据：[tb1]
if object_id('[tb1]') is not null drop table [tb1]
go 
create table [tb1]([Province] int,[P93] numeric(3,2),[P97] numeric(3,2))
insert [tb1]
select 101,7.03,7.66 union all
select 101,null,7.67 union all
select 101,7.11,7.67 union all
select 102,7.03,7.66 union all
select 102,7.08,null union all
select 102,7.11,7.67
--> 测试数据：[tb2]
if object_id('[tb2]') is not null drop table [tb2]
go 
create table [tb2]([Province] int,[guiding93] numeric(3,2),[guidingP97] numeric(3,2))
insert [tb2]
select 101,7.03,7.66 union all
select 102,7.03,7.66
gowith t
as(
select 
[Province],
SUM(ISNULL([P93],0)) as [P93],
SUM(ISNULL([P97],0)) as [P97],
COUNT(1) as [counts]
from
tb1
group by
[Province]
)
select 
t.Province,
(b.guiding93-t.P93)/t.counts as t1,
(b.guidingP97-t.P97)/t.counts as t2
from 
t
inner join
[tb2] b
on 
t.Province=b.Province
----------------结果----------------------------
/* Province t1 t2
---------------------------------------
101 -2.370000 -5.113333
102 -4.730000 -2.556666
*/

Answer 7

另外有个问题，楼主你用left join，那么tb2的记录直接用sum不是被多次计算了吗？

Answer 8

你好，
with t
as(
select 
    [Province],
    SUM(ISNULL([P93],0)) as [P93],
    SUM(ISNULL([P97],0)) as [P97],
    COUNT(1) as [counts]
from
    tb1
group by
    [Province]
)虽然将为null的变为了0，但是，变为0的，数量同时也要减少的。
顺便补充一下
当Pertol93变为0后，Pertol93的数量减少1，所以后边 (sum(guidingjiaP93)-sum(Petrol93))/count(*), 数量也是会减少1
当Pertol97变为0后，Pertol97的数量减少1，所以后边 (sum(guidingjiaP97)-sum(Petrol97))/count(*), 数量也是会减少1

Answer 9

如果为null,那么是不计算的

Answer 10

----------------------------------------------------------------
-- Author  :TravyLee(物是人非事事休，欲语泪先流！)
-- Date    :2012-10-10 09:51:57
-- Version:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
-- Jul  9 2008 14:43:34 
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------------------------------------------
--> 测试数据：[tb1]
if object_id('[tb1]') is not null drop table [tb1]
go 
create table [tb1]([Province] int,[P93] numeric(3,2),[P97] numeric(3,2))
insert [tb1]
select 101,7.03,7.66 union all
select 101,null,7.67 union all
select 101,7.11,7.67 union all
select 102,7.03,7.66 union all
select 102,7.08,null union all
select 102,7.11,7.67
--> 测试数据：[tb2]
if object_id('[tb2]') is not null drop table [tb2]
go 
create table [tb2]([Province] int,[guiding93] numeric(3,2),[guidingP97] numeric(3,2))
insert [tb2]
select 101,7.03,7.66 union all
select 102,7.03,7.66
gowith t
as(
select 
[Province],
SUM(ISNULL([P93],0)) as [P93],
SUM(ISNULL([P97],0)) as [P97],
SUM(case when [P93] is not null then 1 else 0 end) as [p93counts],
SUM(case when [P97] is not null then 1 else 0 end) as [p97counts]
from
tb1
group by
[Province]
)
select 
t.Province,
(b.guiding93-t.P93)/t.[p93counts] as t1,
(b.guidingP97-t.P97)/t.[p97counts] as t2
from 
t
inner join
[tb2] b
on 
t.Province=b.Province
----------------结果----------------------------
/* Province t1 t2
101 -3.555000 -5.113333
102 -4.730000 -3.835000
*/