我需要這個問題的答案

我需要得到每個客戶下訂單的天數？

表名 -T_Order

OrderId    OrderDate    MenuItemId  Quantity    CustomerId
   1       2021-01-02    1            2            1
   2       2021-01-02    3            1            1
   3       2021-01-02    5            1            1
   4       2021-01-15    1            3            3
   5       2021-01-15    3            2            3
   6       2021-01-20    2            1            4
   7       2021-01-26    4            5            5
   8       2021-01-26    5            5            5
   9       2021-02-02    1            2            1
   10      2021-02-02    3            2            1
   11      2021-02-15    2            1            3
   12      2021-02-15    3            1            3
   13      2021-02-25    2            1            4
   14      2021-02-25    3            1            4
   15      2021-02-28    1            2            5
   16      2021-03-06    2            1            1
   17      2021-03-06    3            1            1
   18      2021-03-10    4            3            3
   19      2021-03-10    5            3            3
   20      2021-03-18    2            1            1
   21      2021-03-21    4            1            3
   22      2021-03-21    5            1            3

表名 - T_Menu

MenuId  Items             Price
1       Pizza             2500.00
2       Pasta             1000.00
3       Garlic Bread      800.00
4       Burgers           900.00
5       Cheesecake        500.00

表名 - T-Customer

CustomerId  FirstName   
1             Steve 
3             Jon   
4             Lisa  
5             Robert    


Note: If the total bill is more than Rs.3000 they offer a 5% discount for each customer. Further if the order placed by a loyalty card holder 10% discount will be given for the total bill.

我還需要以下問題的答案。

1. what is the most purchased item for each customer?
2. When was the first order date for each customer?
3. Find jim's average income in March?
4. John wants to see a report on every price change for menu items. How you can fulfill this requirement?

您可以看到不正確結果的原因是COUNT(o.OrderDate)返回o.OrderDate不為空的行數。當您正在尋找不同的值時。您可以使用下面的程式碼，但我認為應該有一種更簡單的方法（使用其中一個視窗函式）

with CTE AS 
(
SELECT Distinct c.FirstName as FirstName, 
               o.OrderDate as date,
               c.CustomerId
FROM T_Orders o
INNER JOIN T_Customer c
ON c.CustomerId = o.CustomerId
)
select max(FirstName), count(OrderDate)
from CTE 
GROUP BY c.CustomerId

您遇到的問題是您多次使用相同的CustomerId順序OrderDate，因此該COUNT()功能將包括重複日期。

您可以簡單地使用DISTINCT聚合COUNT()方法中的關鍵字來最小化更改現有查詢，如下所示：

SELECT MAX(c.FirstName) as FirstName, COUNT(DISTINCT o.OrderDate) as Days
FROM T_Orders o
INNER JOIN T_Customer c
   ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerId

此外，您不必在該FirstName欄位上使用聚合函式，因為它在您現有的CustomerId分組中總是相同的值（即每個CustomerId總是有 1 個 distinct FirstName）。相反，您可以將其添加到您的GROUP BY子句中，並且它不應更改結果的輸出，如下所示：

SELECT c.FirstName, COUNT(DISTINCT o.OrderDate) as Days
FROM T_Orders o
INNER JOIN T_Customer c
   ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerId, c.FirstName

引用自：https://dba.stackexchange.com/questions/301385