我有两个表Qut1
和Qut10
。
Qut1
有一个报价行项目列表
Id LineNum Price
1 0 10
1 1 20
1 2 100
Qut10
具有小计应出现的行
Id AfterLineNum
1 1
1 2
有人知道我如何创建脚本来为我提供需要相加以获得小计的行号?
即(1, 1.1 (即放置在 qut1 第 1 行之后但第 2 行之前(, 30(1, 2.1, 100(
下面是将每个行项目映射到其相应的分类汇总的查询。它JOIN
这两个表,并使用具有NOT EXIST
条件的相关子查询来确保选取相关的小计记录。
SELECT
t1.LineNum,
t1.Price,
t2.AfterLineNum
FROM
Qut1 t1
INNER JOIN Qut10 t2
ON t2.AfterLineNum >= t1.LineNum
AND NOT EXISTS (
SELECT 1
FROM Qut10
WHERE AfterLineNum >= LineNum AND AfterLineNum < t2.AfterLineNum
)
ORDER BY
t2.AfterLineNum ,
t1.LineNum
您没有标记RDBMS,但这是标准SQL,应该适用于大多数平台。
此数据库小提琴演示包含您的示例数据,结果为:
| LineNum | Price | AfterLineNum |
| ------- | ----- | ------------ |
| 0 | 10 | 1 |
| 1 | 20 | 1 |
| 2 | 100 | 2 |
如果您希望实际计算小计,则可以打开聚合:
SELECT
t2.AfterLineNum,
SUM(t1.Price) subtotal
FROM
Qut1 t1
INNER JOIN Qut10 t2
ON t2.AfterLineNum >= t1.LineNum
AND NOT EXISTS (
SELECT 1
FROM Qut10
WHERE AfterLineNum >= LineNum AND AfterLineNum < t2.AfterLineNum
)
GROUP BY t2.AfterLineNum
ORDER BY t2.AfterLineNum
DB小提琴 :
| AfterLineNum | subtotal |
| ------------ | -------- |
| 1 | 30 |
| 2 | 100 |
这是工作版本
SELECT
t1."LineNum",
t1."Price",
t2."AftLineNum"
FROM
Qut1 t1
INNER JOIN Qut10 t2
ON ( t2."AftLineNum" >= t1."LineNum" -1
AND NOT EXISTS (
SELECT 1
FROM Qut10 T00
WHERE T00."AftLineNum" >= T1."LineNum" -1 AND T00."AftLineNum" < t2."AftLineNum" AND T00."DocEntry" = T1."DocEntry"
)
AND T1."DocEntry" = T2."DocEntry" )
WHERE T1."DocEntry" = 49036
ORDER BY
t2."AftLineNum" ,
t1."LineNum"