主页 / dba / 问题 / 303365
Accepted
yossico
Asked: 2021-12-02 13:51:20 +0800 CST

资源分配查询

  • 772

我有两张表，一张用于资源，另一张用于所需分配。

例如：

资源 数数
一个 3
乙 2
C 8
必需的 数数
X 4
是 3
Z 5

资源表告诉我从每个资源中得到了多少东西，所需表告诉我需要多少资源来分配这个项目。

在上面的例子中：

  • 对于 X，我们需要分配 3 次 A 和 1 次 B。
  • 对于 Y，我们需要分配 1 个时间 B 和 2 个时间 C。
  • 对于 Z，我们需要分配 5 次 C。

我们将有一些备用的 C 项目...

目前，我尝试为两个表添加一个计算列（运行总计 - https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server）：

资源 数数 Running_total
一个 3 3
乙 2 5
C 8 13
必需的 数数 Running_total
X 4 4
是 3 7
Z 5 12

并且有一个表的笛卡尔乘法，但这是我的死胡同，我的主要问题是如何有一个公式知道将一个资源分成多个分配或从多个资源中分配一个分配。

有人可以提出线索吗？

编辑

SqlFiddle 链接：http ://sqlfiddle.com/#!18/de1417/3

架构：

CREATE TABLE Resources (
    ResID int primary key,
    ResName varchar(255),
    ResCnt int
);

CREATE TABLE Required (
    ReqID int primary key,
    ReqName varchar(255),
    ReqCnt int
);

insert into Resources values(1, 'A', 3);
insert into Resources values(2, 'B', 2);
insert into Resources values(3, 'C', 8);

insert into Required values(101, 'X', 4);
insert into Required values(102, 'Y', 3);
insert into Required values(103, 'Z', 5);

查询：

 SELECT ResName, ResCnt,
  SUM(ResCnt) OVER(ORDER BY ResName 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM Resources;
  
  
  SELECT ReqName, ReqCnt,
  SUM(ReqCnt) OVER(ORDER BY ReqName 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM Required;
sql-server query

1 个回答

  1. Best Answer

    解决此问题的一种方法是定义间隔，将两个表交叉连接在一起，保持具有重叠间隔的行，并计算每个间隔之间的重叠。下面的查询在不考虑性能的情况下执行此操作：

    WITH Resources_CTE AS (
SELECT
    ResID,
    ResName,
    ResCnt,
    RT - ResCnt IntervalStart,
    RT IntervalEnd
    FROM
    (
        SELECT *, SUM(ResCnt) OVER(ORDER BY ResName ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RT
        FROM Resources
    ) q
),
Required_CTE AS (
SELECT
    ReqID,
    ReqName,
    ReqCnt,
    RT - ReqCnt IntervalStart,
    RT IntervalEnd
    FROM
    (
        SELECT *, SUM(ReqCnt) OVER(ORDER BY ReqName ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RT
        FROM [Required]
    ) q
)
SELECT req.ReqID, req.ReqName, res.ResID, res.ResName,
CASE WHEN req.IntervalEnd - res.IntervalStart < res.IntervalEnd - req.IntervalStart THEN req.IntervalEnd - res.IntervalStart ELSE res.IntervalEnd - req.IntervalStart END AssignmentCount
FROM Required_CTE req
CROSS JOIN Resources_CTE res
WHERE req.IntervalEnd >= res.IntervalStart AND req.IntervalStart <= res.IntervalEnd
ORDER BY req.ReqID, res.ResID;

    我匹配了您计算问题中运行总数的方式，但按主键列而不是按名称列排序会更典型。

    这是您的示例数据的结果集：

    在此处输入图像描述

    • 1

相关问题