如何在 WHERE 子句中聚合從 FROM 子句引用子查詢

到目前為止，我收到了以下查詢：

SELECT q1.ID, Content, Volume, CustomerID, runtot
FROM (
 SELECT ID, Content, Volume, CustomerID,
   SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS runtot,
   ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID) AS rnum
 FROM multiqueue
 WHERE PublishedTS IS NULL
) AS q1
JOIN customers AS c1 ON q1.CustomerID=customers.ID
WHERE
 runtot < 2000 * c1.Priority / (SELECT SUM(Priority) FROM c1)
 OR rnum <= 1

它抱怨附近的語法錯誤SELECT SUM(Priority) FROM c1：既不c1，也不q1表可以在那裡引用。

因此，以下內容也不適用於計算總和：

(SELECT SUM(Priority) FROM customers WHERE ID IN q1.CustomerID)

我想要實現的是customers.Priority超過僅在 中選擇的客戶的總和q1。

如果可能的話，你能幫我解決這個問題嗎？

數據庫是 MariaDB v10.4.13（您也可以假設是最新版本，甚至是 MySQL）。

更新：以下也不起作用

SELECT q1.ID, Content, Volume, CustomerID, runtot
FROM (
 SELECT ID, Content, Volume, CustomerID,
   SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS runtot,
   ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID) AS rnum
 FROM multiqueue
 WHERE PublishedTS IS NULL
) AS q1
JOIN customers AS c1 ON q1.CustomerID=customers.ID
WHERE
 runtot < 2000 * c1.Priority / (SUM(Priority) OVER (PARTITION BY q1.CustomerID))
 OR rnum <= 1

MariaDB 以error 4015: Window function is allowed only in SELECT list and ORDER BY clause.

UPDATE2：表創建和人口

CREATE TABLE `multiqueue` (
   `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
   `CustomerID` BIGINT(20) NOT NULL,
   `Volume` INT(11) NOT NULL,
   `Content` MEDIUMTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
   `PublishedTS` DATETIME NULL DEFAULT NULL,
   PRIMARY KEY (`ID`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

CREATE TABLE `customers` (
   `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
   `Priority` DOUBLE NOT NULL DEFAULT '1000',
   PRIMARY KEY (`ID`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

INSERT INTO multiqueue VALUES
 (1, 1, 100, 'Content1', NULL),
 (2, 1, 200, 'Content2', NULL),
 (3, 1, 300, 'Content3', NULL),
 (4, 1, 400, 'Content4', NULL),
 (5, 1, 500, 'Content5', NULL),
 (6, 2, 100, 'Content6', NULL),
 (7, 2, 200, 'Content7', NULL),
 (8, 2, 300, 'Content8', NULL),
 (9, 2, 400, 'Content9', NULL),
 (10, 2, 500, 'Content10', NULL),
 (11, 1, 600, 'Content11', NULL)

INSERT INTO customers VALUES
 (1, 1000),
 (2, 500),
 (3, 100000)

我希望該查詢為客戶 #1 產生的交易量大約是客戶 #2 的兩倍，並且兩個客戶的總交易量約為 2000。

UPDATE3：對於上述輸入，輸出可以是：

ID    | Content   | Volume | CustomerID | runtot
------------------------------------------------
1     | Content1  | 100    | 1          | 100
2     | Content2  | 200    | 1          | 300
3     | Content3  | 300    | 1          | 600
4     | Content4  | 400    | 1          | 1000
6     | Content6  | 100    | 2          | 100
7     | Content7  | 200    | 2          | 300
8     | Content8  | 300    | 2          | 600

正如我們所看到的，該查詢必須為兩個客戶選擇了 1600 的總交易量，大致保持 1000:500 的比例，同時在為每個客戶至少選擇一行時盡可能保持總交易量低於 2000。

您自己的解決方案可能不是最理想的，但我不確定是否有一種方法可以優雅和/或有效地解決問題而至少沒有一些冗餘。我設法避免customers多次撞到桌子，但我仍然不得不參考multiqueue兩次。這是我最終得到的查詢：

SELECT
 q.ID,
 q.Content,
 q.Volume,
 q.CustomerID,
 q.runtot
FROM
 (
   SELECT
     ID, Content, Volume, CustomerID,
     SUM(Volume)  OVER (PARTITION BY CustomerID ORDER BY ID ASC) AS runtot,
     ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID ASC) AS rnum
   FROM
     multiqueue
   WHERE
     PublishedTS IS NULL
 ) AS q
 INNER JOIN
 (
   SELECT
     ci.ID, ci.Priority,
     SUM(ci.Priority) OVER () AS TotalPriority
   FROM
     customers AS ci
   WHERE
     EXISTS
     (
       SELECT * FROM multiqueue AS qi
       WHERE qi.PublishedTS IS NULL AND qi.CustomerID = ci.ID
     )
 ) AS c ON q.CustomerID = c.ID
WHERE
 q.runtot < 2000 * c.Priority / c.TotalPriority
 OR q.rnum <= 1
;

基本上，連接customers被替換為嵌套選擇的連接，customers其中包含SUM(Priority)計算和EXISTS檢查。該檢查確保僅multiqueue返回其中代表的客戶，因此，Priority僅計算這些客戶的總數。

我希望其餘的邏輯與您自己的查詢相同。

您可以在db<>fiddle.uk的這個展示中測試我的解決方案。

SELECT q1.ID, Content, Volume, CustomerID, runtot
FROM (
  SELECT ID, Content, Volume, CustomerID,
    SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS runtot,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID) AS rnum
  FROM multiqueue
  WHERE PublishedTS IS NULL
) AS q1
JOIN customers AS c1 ON q1.CustomerID=c1.ID
WHERE
  runtot < 2000 * Priority / (SELECT SUM(Priority) FROM customers WHERE ID = CustomerID)
  OR rnum <= 1

身份證 | 內容 | 體積 | 客戶ID | 跑步機
-: | :-------- | -----: | ---------: | -----:
 1 | 內容1 | 100 | 1 | 100
 2 | 內容2 | 200 | 1 | 300
 3 | 內容3 | 300 | 1 | 600
 4 | 內容4 | 400 | 1 | 1000
 5 | 內容5 | 500 | 1 | 1500
 6 | 內容6 | 100 | 2 | 100
 7 | 內容7 | 200 | 2 | 300
 8 | 內容8 | 300 | 2 | 600
 9 | 內容9 | 400 | 2 | 1000
10 | 內容10 | 500 | 2 | 1500

SELECT q1.ID, Content, Volume, CustomerID, runtot
FROM (
  SELECT ID, Content, Volume, CustomerID,
    SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS runtot,
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID) AS rnum
  FROM multiqueue
  WHERE PublishedTS IS NULL
) AS q1
JOIN customers AS c1 ON q1.CustomerID=c1.ID
WHERE
  runtot < 2000 * Priority / (SELECT SUM(Priority) FROM customers)
  OR rnum <= 1

身份證 | 內容 | 體積 | 客戶ID | 跑步機
-: | :------- | -----: | ---------: | -----:
 1 | 內容1 | 100 | 1 | 100
 2 | 內容2 | 200 | 1 | 300
 3 | 內容3 | 300 | 1 | 600
 4 | 內容4 | 400 | 1 | 1000
 6 | 內容6 | 100 | 2 | 100
 7 | 內容7 | 200 | 2 | 300
 8 | 內容8 | 300 | 2 | 600

db<>在這裡擺弄

根據您想要的資訊，您可以為每個 customerID(GROUP BY) 選擇 max(ID)

引用自：https://dba.stackexchange.com/questions/269603