首页 文章

SQL Join帮助 - 在加入之前对表进行求和并应用过滤器

提问于
浏览 次
0

我有一个股票视图（列出所有单独的股票和股票日期）和一个销售视图（列出所有销售和销售发生的日期） .

股票视图：

+----+------+-----+------------+
| ID | Item | Qty |    Date    |
+----+------+-----+------------+
|  1 | A    |   3 | 01/01/2000 |
|  2 | A    |   2 | 02/02/2000 |
|  3 | D    |   9 | 05/06/2000 |
|  4 | F    |  22 | 09/01/2001 |
|  5 | A    |  10 | 01/04/2001 |
|  6 | C    |  12 | 01/01/2002 |
+----+------+-----+------------+

销售视图：

+------+-----+------------+
| Item | Qty |    Date    |
+------+-----+------------+
| B    |   3 | 01/01/2001 |
| B    |  77 | 01/12/2001 |
| C    |   9 | 02/02/2002 |
| A    |  10 | 03/03/2002 |
| G    |   2 | 05/06/2002 |
| C    |   3 | 09/10/2012 |
+------+-----+------------+

我想加入这些表..但在这之前：

需要在2个日期参数@StockFrom和@StockTo之间过滤库存视图

需要在2个日期参数@SalesFrom和@SalesTo之间过滤销售视图

然后，Sales视图需要按Item进行分组并具有Qty Summed（因此需要删除日期字段，尽管它已被过滤），然后将其加入到Item字段的Stock View上 .

所以从本质上讲，我希望看到Stock View（但在日期过滤），其中一列显示该项目的两个日期之间的销售额 .

期望的输出：

+----+------+-----+------------+-------+
| ID | Item | Qty |    Date    | Sales |
+----+------+-----+------------+-------+
|  1 | A    |   3 | 01/01/2000 |    10 |
|  2 | A    |   2 | 02/02/2000 |    10 |
|  3 | D    |   9 | 05/06/2000 |     0 |
|  4 | F    |  22 | 09/01/2001 |     0 |
|  5 | A    |  10 | 01/04/2001 |    10 |
|  6 | C    |  12 | 01/01/2002 |    12 |
+----+------+-----+------------+-------+

在此提前获得任何帮助！

sql join filter sum

2 回答

  • 0
    SELECT
  Stock.*,
  IFNULL(SUM(Sales.Qty),0) AS Sales
FROM Stock
LEFT JOIN Sales ON Stock.Item=Sales.Item
WHERE Stock.Date BETWEEN @StockFrom AND @StockTo
AND (
  Sales.Date BETWEEN @SalesFrom AND @SalesTo
  OR Sales.Date IS NULL
)
GROUP BY Stock.ID

    这是针对MySQL的，因为你没有指定diaclect . SQLfiddle

    EDIT

    SELECT
  Stock.ID AS ID,
  MIN(Stock.Item) AS Item,
  MIN(Stock.Qty) AS Qty,
  MIN(Stock.Date) AS Date,
  CASE WHEN SUM(Sales.Qty) IS NULL THEN 0 ELSE SUM(Sales.Qty) END AS Sales
FROM Stock
LEFT JOIN Sales ON Stock.Item=Sales.Item
WHERE Stock.Date BETWEEN @StockFrom AND @StockTo
AND (
  Sales.Date BETWEEN @SalesFrom AND @SalesTo
  OR Sales.Date IS NULL
)
GROUP BY Stock.ID

    适用于MS SQL（SQLfiddle）

    回复于
  • 0

    请尝试以下MS Sql Server查询：

    SELECT DISTINCT
    a.ID, 
    a.Item, 
    a.Qty, 
    a.Date, 
    ISNULL(SUM(b.Qty) OVER (PARTITION BY a.Item, a.[Date]), 0) Sales
FROM 
    StockView a LEFT JOIN SalesView b on a.Item=b.Item
    回复于

相关问题