MYSQL自行加入以选择去年的销售额

I'm trying to select historical totals (t1.date - 1 for now) off of a range of given dates (t1.DATE >= '2011-01-01' AND t1.DATE <= '2011-11-11')

我试图从一系列给定日期中选择历史总计（t1.date - 1）（t1.DATE> ='2011-01-01'和t1.DATE <='2011-11-11' ）

SELECT 
t1.date as DATE, SUM(t1.total), t2.date, sum(t2.total)
FROM T_SALES t1

LEFT OUTER JOIN T_SALES t2
on (t2.date = (t1.date - 1)
AND t2.rc = t1.rc
AND t2.storenum = t1.storenum)
WHERE 
    t1.RC = 0 
AND t1.STORENUM = '1,'
AND t1.DATE >= '2011-01-01' 
AND t1.DATE <= '2011-11-11' 
GROUP BY t1.date

ORDER BY t1.DATE ASC

My 2nd aggregate t2.total is where my problem is - SUM(t1.total) represents accurate sales totals)

我的第二个聚合t2.total是我的问题所在 - SUM（t1.total）表示准确的销售总额）

Below is my output

以下是我的输出

The value 27.90, should be 5.58--It looks like I'm over summing on sum(t2.total).

值27.90，应该是5.58 - 看起来我总结了总和（t2.total）。

Grouping by t1.total shows that as well: (5.58 * 5 = 27.90)

按t1.total分组也显示：（5.58 * 5 = 27.90）

I've tried every imaginable grouping but just can't get it right, any ideas out there? thanks all!

我已经尝试了所有可以想象到的分组，但只是无法把它弄好，有什么想法吗？谢谢大家！

3 个解决方案

#1

Your problem is that you're getting a semi-cartesian product between the two tables — two sales in the T1 table and three sales in the T2 table will produce six rows in the pre-aggregated result set.

您的问题是您在两个表之间获得半笛卡尔产品--T1表中的两个销售和T2表中的三个销售将在预聚合结果集中产生六行。

There are two ways to solve this:

有两种方法可以解决这个问题：

1. Aggregate the tables first and then JOIN the aggregated tables together.

   首先聚合表，然后将聚合表连接在一起。

2. Base the entire query off a simple select on the first table and nest a query in the results to get the second total.

   将整个查询基于第一个表上的简单选择，并在结果中嵌套查询以获得第二个总计。

Here's solution 1 (note that this is broken down by RC (region code?) and storenum, if that's not what you want simple remove all references to those columns):

这是解决方案1（请注意，这是按RC（区域代码？）和storenum细分的，如果这不是您想要的简单删除对这些列的所有引用）：

CREATE VIEW DailyTotals (RC, StoreNum, Date, TotalAmount) AS 
SELECT RC, StoreNum, Date, SUM(Total) FROM T_Sales
GROUP BY RC, StoreNum, Date

SELECT TODAY.RC, TODAY.StoreNum, TODAY.Date, TODAY.TotalAmount, 
       YDAY.Date, YDAY.TotalAmount
FROM DailyTotals TODAY 
   LEFT OUTER JOIN DailyTotals YDAY ON YDAY.Date = TODAY.Date-1
WHERE TODAY.Date BETWEEN '2011-01-01' AND '2011-11-11'

and here's solution 2 (again with region and store breakdown, which can be removed):

这里是解决方案2（再次进行区域和商店细分，可以删除）：

  SELECT RC, StoreNum, Date, SUM(Total), Date-1,
    (SELECT SUM(Total) FROM T_Sales YDAY WHERE 
     YDAY.RC = TODAY.RC AND YDAY.StoreNum = TODAY.StoreNum AND YDAY.Date = TODAY.Date-1)
  FROM T_Sales TODAY WHERE Date BETWEEN '2011-01-01' AND '2011-11-11'

#2

Maybe sub-selects?

也许是次选？

select *
  from (
select sum(total)
     , date 
     , rc
     , storenum
 from T_SALES
group by date, rc, storenum)T1
left join(
select sum(total)
     , date 
     , rc
     , storenum
 from T_SALES
group by date, rc, storenum)T2
 on T2.date = (T1.date - 1)
and T2.storenum = T1.storenum

#3

try to modify your query into something like this:

尝试将您的查询修改为以下内容：

SELECT 
t1.date as DATE, SUM(t1.total), t1.date, sum(t1.total)
FROM T_SALES t1
WHERE 
    t1.RC = 0 
AND t1.STORENUM = '1,'
t1.date BETWEEN '2011-01-01' AND '2011-11-11' 
GROUP BY t1.date
ORDER BY t1.date ASC

your aliases t1 and t2 are just using the same table and do the computation you need again. your previous query is very confusing. then add the conditions you need on the where statement and post how you did it again here

您的别名t1和t2只是使用相同的表并再次进行计算。您之前的查询非常混乱。然后在where语句中添加所需的条件，并在此处再次发布