選單性能報告的SQLServer存盤程序

我在我的SQL資料庫，即四個表MenuItems，Categories，Invoices和InvoiceDetails。現在我想要的是顯示特定日期的選單性能報告，即特定日期的每個選單項的總數量和總金額。它在 where 子句中顯示沒有日期的所需結果，但排除具有空值的選單項。

這是我的存盤程序：

CREATE PROCEDURE spGetMenuPerformanceByDay 
    @Date date, 
    @Terminal int
AS
BEGIN
    SELECT
        M.Name, 
        ISNULL(SUM(D.Amount), 0) AS Amount,
        ISNULL(SUM(D.Qty), 0) AS Qty
    FROM
        MenuItems AS M 
    JOIN
        Categories AS C ON C.Id = M.CategoryId  
    LEFT JOIN 
        InvoiceDetails AS D ON M.Id = D.ItemId 
    LEFT JOIN 
        Invoices I ON I.Id = d.InvoiceId
    WHERE
        @Terminal IN (I.TerminalId, C.TerminalId) 
        AND CONVERT(date, I.Time) = @Date 
        OR NULL IN (Amount, Qty) 
    GROUP BY
        M.Name, M.Id, D.ItemId
    ORDER BY
        (Qty) DESC
END

此存盤程序在 where 子句中添加 Date 時回傳的結果：

我想要的結果是但不要在 where 子句中添加 Date ：

uj5u.com熱心網友回復：

這是我對你的目標的破解。注意變化。我發現 Category 表中對 TerminalId 的參考非常可疑 - 我懷疑這是一個模型缺陷。沿著這些思路，我注意到 TerminalId 應該有一個指向終端丟失表的外鍵。所以我忽略了這一點。

有了這個，對 Category 的參考現在就無關緊要了。所以這也被洗掉了。我還更改了程式名稱，因為我發現對“day”的參考具有誤導性。由于零售（尤其是食品服務）銷售額在一周中的每一天都不斷變化，因此很有可能會以“天”為基礎評估“選單性能”。因此，讓我們不要誤導任何認為這就是該程式所做的事情。

為簡單起見，我洗掉了 ISNULL 的用法。如果需要，可以將其添加回來，但結果集的使用者通常可以更好地處理這些事情。我將 ORDER BY 子句留作存根供您重新評估（您需要重新評估）。

那么這是如何作業的呢？只需直接在 CTE 中計算總和，然后從選單項外部連接到 CTE 總和，即可獲得所有選單項以及指定日期的相關性能資訊。

CREATE PROCEDURE dbo.GetMenuPerformanceByDate
    @Date date, 
    @Terminal int
AS
BEGIN 
    with sales as (
       select det.ItemId, SUM(det.Amount) as amt, SUM(det.Qty) as qty 
         from dbo.Invoices as inv
        inner join dbo.InvoiceDetails as det 
           on inv.Id = det.InvoiceId
       where cast(inv.Time as date) = @Date 
         and inv.TerminalId = @Terminal 
       group by det.ItemId 
    )
    select menu.name, sales.amt, sales.qty 
      from dbo.MenuItems as menu
      left join sales 
        on menu.Id = sles.ItemId 
    order by ...
    ;
END;

最后一點。這個過濾器：

cast(inv.Time as date) = @Date 

通常不是過濾日期時間列的好方法。使用包含的下限和排他的上限要好得多，例如：

inv.Time >= @date and inv.Time < dateadd(day, 1, @date)

對于這個原因。

我的最后一點 - MenuItems 存在潛在缺陷。據推測，“名稱”是唯一的。多行具有相同名稱的可能性很小，但“不太可能”不是限制。如果您根據名稱生成行并且名稱不是唯一的，則您的結果將難以解釋。

uj5u.com熱心網友回復：

如果您不在 WHERE 子句中放置發票標準怎么辦？

樣本資料

create table Categories (
 Id int primary key, 
 Name varchar(30) not null,
 TerminalId int not null
);

create table MenuItems (
 Id int identity(21,1) primary key, 
 Name varchar(30) not null,
 CategoryId int not null, 
 foreign key (CategoryId) references Categories(Id)
);

create table Invoices (
 Id int identity(31,1) primary key,
 TerminalId int not null, 
 ItemId int not null, 
 Time datetime, 
 foreign key (ItemId) references MenuItems(Id)
);

create table InvoiceDetails (
 InvoiceDetailId int identity(41,1) primary key, 
 InvoiceId int,
 Amount decimal(10,2), 
 Qty int, 
 foreign key (InvoiceId) references Invoices(Id)
);

insert into Categories (Id, Name, TerminalId) values
(1,'KOFTA', 1), 
(2,'SOUP', 1), 
(3,'CHICKEN', 1), 
(4,'BURGER', 1);

insert into MenuItems (CategoryId, Name) values
(1,'KOFTA ANDA'), 
(2,'HOT N SOUR SOUP'), 
(3,'CHICKEN CHOWMEIN'), 
(3,'CHICKEN KORMA'), 
(3,'CRISPY CHICKEN'), 
(4,'MEXICAN BURGER');

insert into Invoices (ItemId, TerminalId, Time)
select itm.Id, cat.TerminalId, GetDate() as Time
from MenuItems itm
join Categories cat on cat.Id = itm.CategoryId
where itm.Name in (       
'KOFTA ANDA', 
'HOT N SOUR SOUP', 
'CHICKEN CHOWMEIN', 
'CHICKEN KORMA'
);

insert into InvoiceDetails (InvoiceId, Amount, Qty) values
(31, 1950, 3),
(32, 550, 1),
(33, 250, 1),
(34, 850, 1);

詢問

DECLARE @TerminalId INT = 1;
DECLARE @Date DATE = GetDate();

SELECT
 V.[Date], 
 C.Name AS Category, 
 M.Name AS MenuItemName, 
 ISNULL(SUM(D.Amount), 0) AS Amount,
 ISNULL(SUM(D.Qty), 0) AS Qty
FROM Categories AS C 
CROSS JOIN (SELECT @Date AS [Date], @TerminalId AS TerminalId) V
JOIN MenuItems AS M 
ON M.CategoryId = C.Id
LEFT JOIN Invoices I
  ON I.ItemId = M.Id
 AND I.TerminalId = V.TerminalId
 AND CAST(I.Time AS DATE) = V.[Date]
LEFT JOIN InvoiceDetails AS D 
  ON D.InvoiceId = I.Id
WHERE C.TerminalId = V.TerminalId
GROUP BY V.[Date], C.Id, M.Id, C.Name, M.Name
ORDER BY SUM(D.Qty) DESC

關于db<>fiddle 的演示在這里

標籤：sql sql-server 表现 存储过程

上一篇：SQL：在特定列上共享多個值的行

下一篇：SQL查詢以查找通過路線行駛的巴士