选择 SQL 映射查询

问题描述

我使用 SQL Server 2012 并且我有这些表:

I use SQL Server 2012 and I have these tables:

表 Tb1 (ID Int, CodeID Int, Value Int)

 ID     CodeID    Value
 ----------------------
 1      1         10
 2      1         14
 3      1         5
 4      1         25
 5      2         12
 6      2         17
 7      2         4
 8      2         10
 9      2         6

Table Tb2 (CodeID Int Value Int)

CodeID    Value
---------------
1         25
2         20

我想要查询从 Tb1 获取记录的 SUM(Tb1.Value)

I want a query for get records from Tb1 that SUM(Tb1.Value) <= Tb2.Value

例如上述记录的结果是:

For example result for above records is :

Tb1.ID   Tb1.CodeID  Tb1.Value   UsedValue     Tb2.Value
--------------------------------------------------------
1         1          10          10            25
2         1          14          14            25
3         1          5           1             25
5         2          12          12            20
6         2          17          8             20

我使用游标获取上述查询.但是我的查询以低性能执行.我想要一个没有光标的查询.

I use cursor for get above query. But my query, execute with low performance.I want a query without cursor.

编辑 1UsedValue 为 Tb1.Value，直到 Tb1.Value 的总和低于 Tb2.Value.

EDIT 1UsedValue is Tb1.Value until sum of Tb1.Value was lowest than Tb2.Value.

推荐答案

试试这个:

;WITH Subtotals
AS
(
  SELECT
    T1.Id, T1.CodeId, SUM(T2.Value) SubTotal
  FROM Tb1 T1
    JOIN Tb1 T2
       ON T1.CodeId = T2.CodeId
         AND T1.Id >= T2.Id
  GROUP BY T1.Id, T1.CodeId
)
SELECT
    S.ID,
    S.CodeID,
    T1.Value,
    CASE WHEN T2.value >= S.Subtotal
         THEN T1.value
         ELSE T1.value - (S.Subtotal - T2.value)
    END UsedValue,
    T2.Value T2Value
FROM Subtotals S
   JOIN Tb2 T2
      ON S.CodeId = T2.CodeId
   JOIN Tb1 T1
      ON S.Id = T1.Id
WHERE T2.Value >= S.SubTotal - T1.Value

在 SQL FIDDLE

这篇关于选择 SQL 映射查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！