为什么这个 SQL 求和值太大了? [英] Why is this SQL summing values that are way too great?

问题描述

我有这个 SQL(改编自

例如，Uchi"部门显示新"销售额超过 10 亿美元，现有"销售额为 7700 万美元.事实并非如此，如下所示:

如果我尝试查看每个子类别的确切数量，请通过以下方式:

从 ReportingMonthlySales RMS 中选择每月销售额在 RMS.Unit = CCL.Unit 上左加入 CustomerCategoryLog CCL其中 RMS.unit = 'Uchi' and CYear = 2017 and cmonth = 3 and Subcategory = 'New'--where RMS.unit = 'Uchi' and CYear = 2017 and cmonth = 3 and Subcategory = 'Existing'--where RMS.unit = 'Uchi' and CYear = 2017 and cmonth = 3 and Subcategory = 'Assumed'--where RMS.unit = 'Uchi' and CYear = 2017 and cmonth = 3 and Subcategory = 'Organic'--where RMS.unit = 'Uchi' and CYear = 2017 and cmonth = 3 and Subcategory = 'Exploding'

...无论我查询哪个子类别值，我最终都会得到0 项".甚至这个 WHERE 子句:

其中 RMS.unit = 'Uchi' 和 Subcategory = 'New'

...什么也没有出现.

为什么总和如此天文数字?我怎样才能让它计算出一个更合理的值(最好是正确的值)，而不仅仅是什么都没有或一切"的极端?

解决方案

您的 JOIN 条件匹配多行.这 可能 是数据的一个属性，但是您的 ON 子句有问题.你有:

on u.Unit = ccl.Unit 和@paramdate >= ccl.begindate 和@paramdate <= ccl.enddate 或 ccl.enddate 为空

您打算:

on u.Unit = ccl.Unit 和@paramdate >= ccl.begindate 和(@paramdate <= ccl.enddate 或 ccl.enddate 为空)

I've got this SQL (adapted from here):

declare @Unit varchar(30);
declare @Year int = 2017;
declare @Month int = 3;
declare @paramdate datetime;
set @paramdate = convert(datetime,convert(char(4),@Year)
                +right('0'+convert(varchar(2),@month),2)
                +'01') 

IF OBJECT_ID('tempdb.dbo.#Units', 'U') IS NOT NULL
DROP TABLE #Units

select distinct unit
into #Units
from ReportingMonthlySales;

select 
    u.Unit
  , New      = sum(case when ccl.Subcategory = 'New'      then rms.MonthlySales else 0 end)
  , Assumed  = sum(case when ccl.Subcategory = 'Assumed'  then rms.MonthlySales else 0 end)
  , Existing = sum(case when ccl.Subcategory = 'Existing' then rms.MonthlySales else 0 end)
  , Organic  = sum(case when ccl.Subcategory = 'Organic'  then rms.MonthlySales else 0 end)
from #Units u
  left join CustomerCategoryLog ccl 
    on u.Unit = ccl.Unit
   and @paramdate >= ccl.begindate
   and @paramdate <= ccl.enddate OR ccl.enddate is null
  left join ReportingMonthlySales rms
    on u.Unit = rms.Unit
   and rms.cyear  = @year
   and rms.cmonth = @month
group by u.unit;

...which (eventually, after much churning) gives me data like this:

The "Uchi" Unit, for example, shows "New" sales of over a billion dollars and "Existing" sales of 77 million. This is not really so, as this shows:

If I try to see exactly how much there is for each Subcategory, this way:

select monthlysales from ReportingMonthlySales RMS
left join CustomerCategoryLog CCL on RMS.Unit = CCL.Unit
where RMS.unit = 'Uchi' and CYear = 2017 and cmonth = 3 and Subcategory = 'New'
--where RMS.unit = 'Uchi' and CYear = 2017 and cmonth = 3 and Subcategory = 'Existing'
--where RMS.unit = 'Uchi' and CYear = 2017 and cmonth = 3 and Subcategory = 'Assumed'
--where RMS.unit = 'Uchi' and CYear = 2017 and cmonth = 3 and Subcategory = 'Organic'
--where RMS.unit = 'Uchi' and CYear = 2017 and cmonth = 3 and Subcategory = 'Exploding'

...I end up with "0 items" regardless of which Subcategory value I query for. Even this WHERE clause:

where RMS.unit = 'Uchi' and Subcategory = 'New'

...turns up nothing.

Why is the sum so astronomical? How can I get it to calculate a more reasonable value (preferaby the right one), and not only extremes of either nothing or "everything"?

解决方案

Your JOIN condition is matching multiple rows. That might be a property of the data, but your ON clause is problematic. You have:

on u.Unit = ccl.Unit and
   @paramdate >= ccl.begindate and
   @paramdate <= ccl.enddate OR ccl.enddate is null

You intend:

on u.Unit = ccl.Unit and
   @paramdate >= ccl.begindate and
   (@paramdate <= ccl.enddate OR ccl.enddate is null)

这篇关于为什么这个 SQL 求和值太大了?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！