我有下表：
| 城市 | 狀態 | 序號 | 日期 | 截止日期 |
|---|---|---|---|---|
| 紐約 | 紐約 | 111 | 2022/11/05 | 2022/11/06 |
| 洛杉磯 | 加州 | 222 | 2022/11/01 | 2022/10/01 |
| 標清 | 加州 | 333 | 2022/05/05 | 2022/11/06 |
| 洛杉磯 | 加州 | 444 | 2022/11/01 | 2022/05/01 |
我需要計算截止日期前的訂單數量除以每個州和城市的訂單數量：
(SELECT state, city ,count(*)
FROM orders
WHERE date <= deadlineDate
group by state, city) /
(SELECT state, city ,count(*)
FROM orders
group by state, city)
我試過了：
SELECT (
SELECT state, city ,count(*)
FROM orders
WHERE serviceDate <= limitDate
group by state, city
)/
(
SELECT state, city ,count(*)
FROM orders
group by state, city
)
FROM orders
但是我得到了錯誤：
子查詢必須只回傳一列
uj5u.com熱心網友回復：
嘗試以下操作：
SELECT state, city,
COUNT(*) FILTER (WHERE date <= deadlineDate)*1.0 / COUNT(*) AS result
FROM orders
GROUP BY state, city
查看演示。
uj5u.com熱心網友回復：
將表作為兩個子查詢表加入并在選擇中進行數學運算
SELECT A.COL1/B.COL1 AS MY_RATIO_COL
FROM
(SELECT COL1 FROM MY_TABLE WHERE [BLA BLA BLA]) A
JOIN
(SELECT COL1 FROM MY_TABLE WHERE [yata yata]) B
ON A.KEYCOL1 = B.KEYCOL1
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/528926.html