我在 SQLite 中有一個銷售表：
| 購買日期 | 單位_已售出 | 顧客ID |
|---|---|---|
| 15 | 1 | 1 |
| 17 | 1 | 1 |
| 30 | 3 | 1 |
我想獲得unit_solds每個客戶在他們購買的第一個日期和最后一個日期的總數。我的查詢是：
select customer_id,
sum(units_sold) total_units_sold
from sales
group by customer_id
having purchase_date = min(purchase_date)
or purchase_date = max(purchase_date)
我期待這樣的結果：
| 顧客ID | total_units_sold |
|---|---|
| 1 | 4 |
但我得到了：
| 顧客ID | total_units_sold |
|---|---|
| 1 | 5 |
我想知道為什么這個解決方案不起作用。
uj5u.com熱心網友回復：
陳述句順序不正確 注意：ifying 陳述句在編譯后執行。
需要獲取部分查詢的結果 比如我根據每個客戶安排知道日期的第一行以及日期的最后一行（通過降序獲取第一行）然后執行組陳述
示例已完成
select customer_id,sum(units_sold) from (
select customer_id, units_sold,purchase_date,
ROW_NUMBER() over(partition by customer_id order by purchase_date) As RowDatefirst,
ROW_NUMBER() over(partition by customer_id order by purchase_date desc)As RowDatelast
from sales
) t where t.RowDatefirst = 1 or t.RowDatelast=1
group by customer_id
uj5u.com熱心網友回復：
嘗試這個：
SELECT a.customer_id, SUM(a.units_sold) as total_units_sold
FROM sales a
INNER JOIN (
SELECT customer_id, MIN(purchase_date) as _first ,MAX(purchase_date) as _last
FROM sales
GROUP BY customer_id
) b ON a.customer_id = b.customer_id AND
(a.purchase_date = b._first OR a.purchase_date = b._last)
GROUP BY a.customer_id
http://sqlfiddle.com/#!7/0a4a4/7
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/360098.html