我的表可能如下所示：
| Email | ID | Order_date | Total |
| ----------------- | --------- | ------------ | ------------ |
|customerA@gmail.com| 1 | 01-01-2019 | 500 |
|customerB@gmail.com| 2 | 02-01-2019 | 1000 |
|customerC@gmail.com| 3 | 03-01-2019 | 1000 |
|customerD@gmail.com| 4 | 04-01-2019 | 2000 |
|customerA@gmail.com| 5 | 05-01-2019 | 3000 |
|customerB@gmail.com| 6 | 06-01-2019 | 500 |
|customerC@gmail.com| 7 | 03-01-2019 | 1000 |
|customerA@gmail.com| 8 | 05-01-2019 | 3000 |
|customerB@gmail.com| 9 | 09-01-2019 | 2000 |
|customerA@gmail.com| 10 | 10-01-2019 | 4000 |
|customerB@gmail.com| 11 | 02-01-2019 | 1000 |
|customerA@gmail.com| 12 | 12-01-2019 | 2000 |
我想添加一個帶有訂單數量的輔助列，但沒有任何額外的分組。我夢想中的桌子看起來像這樣：
| Email | ID | Order_date | Total | Number of orders |
| ----------------- | --------- | ------------ | ------------ | ---------------- |
|customerA@gmail.com| 1 | 01-01-2019 | 500 | 5 |
|customerB@gmail.com| 2 | 02-01-2019 | 1000 | 4 |
|customerC@gmail.com| 3 | 03-01-2019 | 1000 | 2 |
|customerD@gmail.com| 4 | 04-01-2019 | 2000 | 1 |
|customerA@gmail.com| 5 | 05-01-2019 | 3000 | 5 |
|customerB@gmail.com| 6 | 06-01-2019 | 500 | 4 |
|customerC@gmail.com| 7 | 03-01-2019 | 1000 | 2 |
|customerA@gmail.com| 8 | 05-01-2019 | 3000 | 5 |
|customerB@gmail.com| 9 | 09-01-2019 | 2000 | 4 |
|customerA@gmail.com| 10 | 10-01-2019 | 4000 | 5 |
|customerB@gmail.com| 11 | 02-01-2019 | 1000 | 4 |
|customerA@gmail.com| 12 | 12-01-2019 | 2000 | 5 |
正如您可能猜到的那樣，我想根據購物訂單的數量執行進一步的計算（如總和或平均值）。我的 SQL 查詢可能如下所示：
SELECT AVG(Total)
FROM
(SELECT ...
...
...
WHERE ...
...
...
GROUP BY ...
HAVING COUNT(Number_of_orders > 1) AND COUNT(Number_of_orders < 5) x
或類似的東西。只是為了給你一個基本的想法，為什么我認為我需要輔助列。
我會認為這很容易，但我已經花了很多時間在上面，所以任何形式的建議或幫助將不勝感激！
uj5u.com熱心網友回復：
假設“電子郵件”是您要按訂單數分組的列，這應該會為您提供所需的表格。
SELECT T.Email, T.ID, T.Date, T.Total, TT.Count
FROM Table T
INNER JOIN (
SELECT Email, Count(Email) as Count
FROM Table
GROUP BY Email) TT
ON T.Email = TT.Email
uj5u.com熱心網友回復：
如果您的資料庫引擎支持partitioning：
select * , count(*) over (partition by Email) as OrderCount
from tablename
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/344762.html