从另一个表中查找列的总值?(使用join或其他函数)

I have two tables like this Table 1

我有两个表，像这个表1

Group      ID         submit_date
1          153        2011-08-22
1          158        2011-12-02
1          245        2013-02-22
2          325        2014-01-20
2          285        2015-01-22
3          425        2016-04-22 
3          655        2017-02-22

Table 2

表2

ID         as_date       amount
153        2011-09-01    500
153        2011-10-01    400
153        2011-11-15    350 
153        2012-01-25    250
153        2012-02-15    200
158        2012-01-02    10000
158        2013-05-02    8000
325        2014-02-20    5000
325        2014-03-20    4000
325        2014-04-20    3000

There are multiple records for each id of table 1 in table 2. I want to find total amount for each ID in table 1. Total amount due is equal to total amount of all ID from same group that submitted before current ID. only should use amount due when the as_date is more recent and still less than submit date.

表2中的每个id都有多个记录。我想在表1中找到每个ID的总金额。应付总额等于在当前idis之前提交的同一组的所有ID的总额。仅当as_date更近且仍然小于提交日期时才应使用应付的金额。

Example:

例子:

• ID 153: No ID submitted before then amount Due = 0
• ID 153:未提交ID，则到期金额= 0
• ID 158 : ID 153 submitted before and 2011-11-15 is the most recent as_date that we have 350$ amount due (2011-11-15 is right before ID 158 submit date(2011-12-02))
• ID 158:之前提交的ID 153, 2011-11-15是最近的as_date，我们有350美元到期(2011-11-15是在id158提交日期之前提交的(2011-12-02)

• ID 245 : there are two ID in group 1 that have been submitted before this ID. The amount due should be 200+10000 :

  ID 245:第一组有两个ID是在这个ID之前提交的，应该是200+10000:

  153        2012-02-15    200
  158        2012-01-02    10000
  

The as_of_date are before ID 245 submit date(2013-02-22)

as_of_date在id245提交日期之前(2013-02-22)

Result

结果

Group      ID         submit_date    Total_due_so_far
1          153        2011-08-22     0
1          158        2011-12-02     350
1          245        2013-02-22     10000+200
2          325        2014-01-20     0 (no ID submitted before for Group 2)
2          285        2015-01-22     3000
3          425        2016-04-22     0 (no ID submitted before for Group 3)
3          655        2017-02-22     0 (no ID submitted before for Group 3)

2 个解决方案

#1

MySql does not support analytical functions so far, so such a monster-query in plain SQL had to be created. It will not be very fast:

到目前为止，MySql不支持分析函数，因此必须创建纯SQL中的怪物查询。它不会很快:

SELECT y.group, id, submit_date, coalesce( SUM( amt ), 0 ) As Total_due_so_far
FROM (
   SELECT x.group, id, submit_date,
          ( SELECT amount FROM table2 t2
            WHERE t2.id = id12
            AND t2.as_date <= x.submit_dateeee
            ORDER BY as_date DESC
            LIMIT 1
           ) As amt
    FROM (
      SELECT t11.group, t11.id as id, t11.submit_date, t12.id as id12,
             max( t2.as_date ) as submit_dateeee
      FROM table1 t11
      LEFT JOIN table1 t12
      ON t11.id <> t12.id AND t11.submit_date > t12.submit_date AND t11.group = t12.group
      LEFT JOIN table2 t2
      ON t2.id = t12.id AND t2.as_date < t11.submit_date
      GROUP BY t11.id, t11.submit_date, t12.id
   ) x
) y
GROUP BY y.group, id, submit_date
order by 1,2
;

Demo: http://sqlfiddle.com/#!9/e130e/38

演示:http://sqlfiddle.com/ ! 9 / e130e / 38

| group |  id |          submit_date | Total_due_so_far |
|-------|-----|----------------------|------------------|
|     1 | 153 | 2011-08-22T00:00:00Z |                0 |
|     1 | 158 | 2011-12-02T00:00:00Z |              350 |
|     1 | 245 | 2013-02-22T00:00:00Z |            10200 |
|     2 | 285 | 2015-01-22T00:00:00Z |             3000 |
|     2 | 325 | 2014-01-20T00:00:00Z |                0 |
|     3 | 425 | 2016-04-22T00:00:00Z |                0 |
|     3 | 655 | 2017-02-22T00:00:00Z |                0 |

#2

Using CTE's you can retrieve the latest dates according to your specifications in a readable way. In this example I am using MS SQL Server.

使用CTE，您可以以可读的方式根据您的规范检索最新的日期。在本例中，我使用的是MS SQL Server。

The query uses the CTE to get the most recent as_date. Please note that this example uses a CROSS JOIN so it is assumed that your tables are not extremely large. Otherwise, this can be a huge performance hit. The reasoning behind the CROSS JOIN is to get the combinations of the ID's to ID's and as_date's. From there you can the most recent one using

查询使用CTE获取最新的as_date。请注意，这个示例使用交叉连接，因此假定您的表不是非常大。否则，这将是一个巨大的性能冲击。交叉连接背后的原因是获取ID与ID和as_date的组合。从那里你可以使用最近的一个

...
, MAX
        (
            CASE WHEN main.submit_date > t2.as_date 
                THEN t2.as_date 
                ELSE NULL
            END
        ) OVER (PARTITION BY t1.[group], t2.[ID] ) as recent_date
...

After that, LEFT JOIN back onto table2 and RIGHT JOIN onto table1 in order to get ID's with our without NULL values on the outer query and get the SUM for the amount.

之后，左连接返回到表2，右连接到表1，以便在外部查询中获得ID，并获得金额的和。

Example Data:

示例数据:

DECLARE @table1 TABLE ( [group] INT, ID INT, submit_date DATE )
DECLARE @table2 TABLE ( [ID] INT, as_date DATE, amount INT)

INSERT INTO @table1
VALUES 
( 1          , 153        , '2011-08-22')
,( 1          , 158        , '2011-12-02')
,( 1          , 245        , '2013-02-22')
,( 2          , 325        , '2014-01-20')
,( 2          , 285        , '2015-01-22')
,( 3          , 425        , '2016-04-22') 
,( 3          , 655        , '2017-02-22')

INSERT INTO @table2
VALUES
( 153        ,'2011-09-01'    , 500  ) 
,( 153        ,'2011-10-01'    , 400  )
,( 153        ,'2011-11-15'    , 350  )
,( 153        ,'2012-01-25'    , 250  )
,( 153        ,'2012-02-15'    , 200  )
,( 158        ,'2012-01-02'    , 10000)
,( 158        ,'2013-05-02'    , 8000 )
,( 325        ,'2014-02-20'    , 5000 )
,( 325        ,'2014-03-20'    , 4000 )
,( 325        ,'2014-04-20'    , 3000 )

Solution Query

解决方案查询

;WITH Recent_As_Date_cte AS
(
    SELECT DISTINCT main.[group]
        , main.[ID]
        , t1.ID AS [previous_id]
        , MAX
            (
                CASE WHEN main.submit_date > t2.as_date 
                    THEN t2.as_date 
                    ELSE NULL
                END
            ) OVER (PARTITION BY t1.[group], t2.[ID] ) as recent_date
    FROM @table1 t1 
        LEFT JOIN @table2 t2 ON t1.ID = t2.ID
        CROSS JOIN @table1 main
    WHERE main.[group] = t1.[group]
        AND main.submit_date > t2.as_date
)
SELECT DISTINCT
    t1.[group]
    , t1.[ID]
    , t1.[submit_date]
    , SUM( ISNULL(t2.amount, 0)) AS [Total_due_so_far]

FROM Recent_As_Date_cte c 
    LEFT JOIN @table2 t2 ON t2.as_date = c.recent_date
    RIGHT JOIN @table1 t1 ON c.ID = t1.ID
GROUP BY 
    t1.[group]
    , t1.[ID]
    , t1.[submit_date]
ORDER BY 
    t1.[group]
    , t1.[ID]