我有2張桌子。產品表包含我的 skus，其中包含以下列：id、name、sku、image
我的另一個表是我的倉庫_in_and_out，它有：id、date_time、sku_id、inbound_outbound、adjustment。date_time 和 sku_id 在一起是唯一的，所以我每天每個 sku 只有 1 行。我的 inbound_outbound 和調整也是一個整數陣列。
我想為每個 sku 的日期范圍內的每一天獲取 inbound_outbound 并且還想獲得每個 sku 的所有行的所有 inbound_outbound 的總和
我想出了這樣的事情：
SELECT P.ID ,SKU,
MAX(CASE
WHEN DATE_TIME = '12/31/2021' THEN WARE.INBOUND_OUTBOUND
ELSE NULL
END) AS INBOUND_OUTBOUND_1,
MAX(CASE
WHEN DATE_TIME = '12/31/2021' THEN WARE.ADJUSTMENT
ELSE NULL
END) AS ADJUSTMENT_1,
MAX(CASE
WHEN DATE_TIME = '12/30/2021' THEN WARE.INBOUND_OUTBOUND
ELSE NULL
END) AS INBOUND_OUTBOUND_2,
MAX(CASE
WHEN DATE_TIME = '12/30/2021' THEN WARE.ADJUSTMENT
ELSE NULL
END) AS ADJUSTMENT_2
FROM PRODUCTS AS P
LEFT JOIN WAREHOUSE_IN_AND_OUT AS WARE ON WARE.SKU_ID = P.ID
WHERE P.STORE_ID IN ('1',
'2',
'3',
'4',
'5')
GROUP BY P.ID, SKU
ORDER BY SKU
我在 json 中的結果是：
{
id: '1',
sku: 'ABC',
inbound_outbound_1: null,
adjustment_1: null,
inbound_outbound_2: [
4960, 4960, 4960, 4960, 4960, 4960,
4960, 4960, 4960, 4960, 4960, 4960,
4960, 4960, 4960, 4960, 4960, 4960,
4960, 4960, 4960, 4960, 4960, 4960,
4960, 4960, 4960, 4960, 4960, 4960,
4960, 4960, 4960, 4960, 4960, 4960,
5000, 4960
],
adjustment_2: null
}
當我想將我的總數添加到這個 sql 時，我得到 2 個相同的行：
SELECT P.ID ,SKU,
MAX(CASE
WHEN DATE_TIME = '12/31/2021' THEN WARE.INBOUND_OUTBOUND
ELSE NULL
END) AS INBOUND_OUTBOUND_1,
MAX(CASE
WHEN DATE_TIME = '12/31/2021' THEN WARE.ADJUSTMENT
ELSE NULL
END) AS ADJUSTMENT_1,
MAX(CASE
WHEN DATE_TIME = '12/30/2021' THEN WARE.INBOUND_OUTBOUND
ELSE NULL
END) AS INBOUND_OUTBOUND_2,
MAX(CASE
WHEN DATE_TIME = '12/30/2021' THEN WARE.ADJUSTMENT
ELSE NULL
END) AS ADJUSTMENT_2,
COALESCE(
(SELECT SUM(I)
FROM UNNEST(WARE.INBOUND_OUTBOUND) AS I),0)::INTEGER AS TOTAL
FROM PRODUCTS AS P
LEFT JOIN WAREHOUSE_IN_AND_OUT AS WARE ON WARE.SKU_ID = P.ID
WHERE P.STORE_ID IN ('1',
'2',
'3',
'4',
'5')
GROUP BY P.ID, SKU, INBOUND_OUTBOUND
ORDER BY SKU
我的結果是：
[
{
id: '1',
sku: 'ABC',
total: -4,
inbound_outbound_1: [ 52, -56 ],
adjustment_1: null,
inbound_outbound_2: null,
adjustment_2: null
},
{
id: '1',
sku: 'ABC',
total: 188520,
inbound_outbound_1: null,
adjustment_1: null,
inbound_outbound_2: [
4960, 4960, 4960, 4960, 4960, 4960,
4960, 4960, 4960, 4960, 4960, 4960,
4960, 4960, 4960, 4960, 4960, 4960,
4960, 4960, 4960, 4960, 4960, 4960,
4960, 4960, 4960, 4960, 4960, 4960,
4960, 4960, 4960, 4960, 4960, 4960,
5000, 4960
],
adjustment_2: null
}
]
但我只想為每個 sku 獲取一行。我的預期結果是：
[
{
id: '1',
sku: 'ABC',
total: 188516,
inbound_outbound_1: [ 52, -56 ],
adjustment_1: null,
inbound_outbound_2: [
4960, 4960, 4960, 4960, 4960, 4960,
4960, 4960, 4960, 4960, 4960, 4960,
4960, 4960, 4960, 4960, 4960, 4960,
4960, 4960, 4960, 4960, 4960, 4960,
4960, 4960, 4960, 4960, 4960, 4960,
4960, 4960, 4960, 4960, 4960, 4960,
5000, 4960
],
adjustment_2: null
}
]
感謝 Jon 我在dbfiddle 中實作了我的模式
I want to have 6 and 8 result in one result if thats possible. I don't want to have many results for each sku like in 7 parag. I want to have total for each sku
uj5u.com熱心網友回復：
我會在您的 SELECT 中使用橫向連接而不是子查詢...
SELECT P.ID, P.SKU,
MAX(CASE
WHEN DATE_TIME = '12/31/2021' THEN WARE.INBOUND_OUTBOUND
ELSE NULL
END) AS INBOUND_OUTBOUND_1,
MAX(CASE
WHEN DATE_TIME = '12/31/2021' THEN WARE.ADJUSTMENT
ELSE NULL
END) AS ADJUSTMENT_1,
MAX(CASE
WHEN DATE_TIME = '12/30/2021' THEN WARE.INBOUND_OUTBOUND
ELSE NULL
END) AS INBOUND_OUTBOUND_2,
MAX(CASE
WHEN DATE_TIME = '12/30/2021' THEN WARE.ADJUSTMENT
ELSE NULL
END) AS ADJUSTMENT_2,
MAX(CASE
WHEN DATE_TIME = '12/29/2021' THEN WARE.INBOUND_OUTBOUND
ELSE NULL
END) AS INBOUND_OUTBOUND_3,
MAX(CASE
WHEN DATE_TIME = '12/29/2021' THEN WARE.ADJUSTMENT
ELSE NULL
END) AS ADJUSTMENT_3,
COALESCE(SUM(I.SUM_I),0)::INTEGER AS TOTAL
FROM PRODUCTS AS P
LEFT JOIN WAREHOUSE_IN_AND_OUT AS WARE ON WARE.SKU_ID = P.ID
CROSS JOIN LATERAL (SELECT SUM(I) SUM_I FROM UNNEST(WARE.INBOUND_OUTBOUND) I) I
GROUP BY P.ID, SKU
ORDER BY SKU
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=9f94ecd3f1c38ab01eca06699c980424
uj5u.com熱心網友回復：
雖然@MatBailie 提供了一個非常好的解決方案，但我想我會添加一個直接/簡單組合兩個陳述句的示例。這在許多其他情況下也很有幫助。
小提琴，兩種解決方案
WITH cte1 AS (
SELECT P.ID, P.SKU
, MAX(CASE
WHEN DATE_TIME = '12/31/2021' THEN WARE.INBOUND_OUTBOUND
ELSE NULL
END
) AS INBOUND_OUTBOUND_1
, MAX(CASE
WHEN DATE_TIME = '12/31/2021' THEN WARE.ADJUSTMENT
ELSE NULL
END
) AS ADJUSTMENT_1
, MAX(CASE
WHEN DATE_TIME = '12/30/2021' THEN WARE.INBOUND_OUTBOUND
ELSE NULL
END
) AS INBOUND_OUTBOUND_2
, MAX(CASE
WHEN DATE_TIME = '12/30/2021' THEN WARE.ADJUSTMENT
ELSE NULL
END
) AS ADJUSTMENT_2
, MAX(CASE
WHEN DATE_TIME = '12/29/2021' THEN WARE.INBOUND_OUTBOUND
ELSE NULL
END
) AS INBOUND_OUTBOUND_3
, MAX(CASE
WHEN DATE_TIME = '12/29/2021' THEN WARE.ADJUSTMENT
ELSE NULL
END
) AS ADJUSTMENT_3
FROM PRODUCTS AS P
LEFT JOIN WAREHOUSE_IN_AND_OUT AS WARE ON WARE.SKU_ID = P.ID
GROUP BY P.ID, SKU
ORDER BY SKU
)
, cte2 AS (
SELECT sku, SUM(total) as total
FROM (
SELECT P.SKU
, COALESCE(
(SELECT SUM(I)
FROM UNNEST(WARE.INBOUND_OUTBOUND) AS I
)
, 0
)::INTEGER AS TOTAL
FROM PRODUCTS AS P
LEFT JOIN WAREHOUSE_IN_AND_OUT AS WARE ON WARE.SKU_ID = P.ID
ORDER BY SKU
) AS test
GROUP BY SKU
)
SELECT cte1.*, cte2.total
FROM cte1
JOIN cte2
ON cte1.SKU = cte2.SKU
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/400106.html
標籤：sql postgresql