我有 2 個表 (mysql)
- tbl_products
- tbl_counting
我在 tbl_counting 表中收集了 3 個用戶的“機架”和“庫存”資訊。我想用這種條件（如結果）用 tbl_counting 資料更新“tbl_products.rack”和“tbl_products.stock”：
- 每個“機架”和“庫存”資訊必須由至少 3 個用戶收集
- tbl_products.rack" & "tbl_products.stock" 必須以最高頻率（至少 2 次）的 "Rack" 或 "Stock" 資訊更新
- 如果“機架”或“庫存”資訊的重復次數少于 2 次，請使用“未知”指定欄位。（如結果）
謝謝
1. tbl_products (更新前)
| ID | 產品名稱 | 架子 | 股票 |
|---|---|---|---|
| 1 | 蘋果 | ||
| 2 | 橘子 | ||
| 3 | 草莓 |
2. tbl_counting
| 產品編號 | 用戶身份 | 架子 | 股票 |
|---|---|---|---|
| 1 | 1 | A-1-1 | 20 |
| 1 | 2 | A-1-1 | 10 |
| 1 | 3 | B-1-1 | 20 |
| 2 | 1 | C-1-1 | 10 |
| 2 | 2 | D-2-1 | 30 |
| 2 | 3 | A-3-1 | 30 |
| 3 | 1 | X-3-1 | 25 |
| 3 | 2 | X-1-1 | 10 |
| 3 | 3 | X-3-1 | 25 |
結果：tbl_products（更新后）
| ID | 產品名稱 | 架子 | 股票 |
|---|---|---|---|
| 1 | 蘋果 | A-1-1 | 20 |
| 2 | 橘子 | 未知 | 30 |
| 3 | 草莓 | X-3-1 | 25 |
select
tbl_counting.product_id,
tbl_counting.rack,
MAX(tbl_counting.stock),
count(*) as freq
from tbl_counting
group by tbl_counting.product_id
having count(*) =(select max(freq)
from (select product_id,count(*) as freq
from tbl_counting group by product_id) tbl_counting)
uj5u.com熱心網友回復：
你可以檢查這個
SELECT
product_id p,
shelf,
floor,
line,
stock
FROM
user_product
GROUP BY
product_id,
stock,
floor,
line,
shelf
HAVING
stock = ( SELECT stock FROM user_product WHERE product_id = p GROUP BY stock ORDER BY count(*) DESC LIMIT 1 ) AND
floor = ( SELECT floor FROM user_product WHERE product_id = p GROUP BY floor ORDER BY count(*) DESC LIMIT 1 ) AND
line = ( SELECT line FROM user_product WHERE product_id = p GROUP BY line ORDER BY count(*) DESC LIMIT 1 ) AND
shelf = ( SELECT shelf FROM user_product WHERE product_id = p GROUP BY shelf ORDER BY count(*) DESC LIMIT 1 );
uj5u.com熱心網友回復：
這是一種可能的解決方案。前兩個 cte 為您提供按產品劃分的用戶和不同機架的計數，它們在下一個 cte 中用于過濾滿足給定條件的記錄。
UPDATE tbl_products P
JOIN (
WITH uses_count AS
(
SELECT product_id,
COUNT(DISTINCT user_id) AS num_users
FROM tbl_counting
GROUP BY product_id
)
, rack_count AS
(
SELECT product_id,
rack,
COUNT(*) AS freq
FROM tbl_counting
GROUP BY product_id, rack
)
,rank_result AS
(
SELECT T.product_id,
T.rack,
T.stock,
U.num_users,
R.freq,
ROW_NUMBER() OVER(PARTITION BY T.product_id ORDER BY R.freq DESC, T.stock DESC) AS RN
FROM tbl_counting T
LEFT JOIN uses_count U
ON T.product_id = U.product_id
AND CASE WHEN U.num_users >= 3 THEN TRUE ELSE FALSE END
LEFT JOIN rack_count R
ON T.product_id = R.product_id
AND CASE WHEN R.freq >= 2 THEN TRUE ELSE FALSE END
)
SELECT product_id,
stock,
CASE WHEN R.freq IS NULL THEN 'Unknown' ELSE R.rack END AS rack
FROM rank_result
WHERE RN = 1) R
ON P.Id = R.product_id
SET P.stock = R.stock,
P.rack = R.rack
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/387414.html