时间戳

首页 > 解决方案 > 比较两个表并检索数据

sql-server - 比较两个表并检索数据

问题描述

我在 SQL Server 中有 2 个表，我想比较它们。我想拿'NEEDED_AMOUNT'和'分钟。“身份证”。我尝试了以下方法：

SELECT S_ID, NEEDED_AMOUNT, ID
FROM (
    select T1.S_ID
        , T2.NEEDED_AMOUNT
        , T1.ID
    from T1
    INNER JOIN T2 MSD ON T1.S_ID = T2.S_ID
) TABLE1
GROUP BY S_ID, NEEDED_AMOUNT, ID

举例来说：在 T1 表中，我将 S_ID 设为“1”，其数量为“20”和“30”。同样在 T2 我有 S_ID 的请求，我需要 '40' 数量。那么在 T1 表中我怎样才能达到 40？我必须取第一行“20”的数量，然后将第二行“30”拆分为“20”。下面你可以看到我想要的输出。

所以这里是表格。

我可以将此表称为 T1（ID 是主键和自动公司）：

ID  AMOUNT    S_ID
1      20       1
2      30       1
3      10       2
4      20       3
5      5        3

我可以称这个表为 T2：

S_ID  NEEDED_AMOUNT    DATE
1      40              01.01.2020
2      5               02.01.2020
3      20              03.01.2020

所以我的输出将是这样的：

S_ID  NEEDED_AMOUNT  ID
1      20            1
1      20            2
2      5             3
3      20            4

感谢您的任何意见

标签： sql-servergroup-by

解决方案

我会为此使用递归方法：

with cte as (
     select id, amount, s_id, needed_amount, 
            (case when amount = needed_amount then 1 else cnt end) as cnt
     from (select t1.*, t2.needed_amount, 
                  row_number() over (partition by t1.s_id order by t1.id) as seq,
                  count(*) over (partition by t1.s_id) as cnt
           from t1 inner join
                t2
                on t2.s_id = t1.s_id
          ) t
     where seq = 1
), cte1 as (
   select c.needed_amount / c.cnt as amount, c.s_id, 1 as start, c.cnt
   from cte c
   union all
   select amount, s_id, start + 1, cnt
   from cte1 c1
   where start < cnt
)
select s_id, amount, row_number() over (order by s_id) as id
from cte1;

推荐阅读