?? reportmedicine_v.sql
字號:
USE Hospital
GO
CREATE VIEW ReportMedicine_v
AS
SELECT m.MedId AS 藥品編號, m.MedName AS 藥品名稱,
(CASE WHEN m.Flag = 1 THEN '中藥' ELSE '西藥' END) AS 類別,
b1.TypeName AS 分類, b.TypeName AS 單位, m.BuyPrice AS 進貨價格,
m.SalePrice AS 銷售價格, m.Total AS 庫存數量, p.RegDate AS 銷售日期,
(CASE WHEN p.PNum >= 0 THEN p.PNum ELSE 0 END) AS 銷售數量,
(CASE WHEN p.PNum >= 0 THEN p.PSum ELSE 0 END) AS 銷售總額,
(CASE WHEN p.PNum >= 0 THEN (p.PSum - m.BuyPrice * p.PNum) ELSE 0 END)
AS 純利潤
FROM Medicine m, BaseType b, BaseType b1,
(SELECT ps.ItemId, ps.RegId, LEFT(r.RegDate,7) AS RegDate, SUM(ps.PNum) AS PNum,
SUM(ps.Price * ps.PNum) AS PSum
FROM PayItems ps, Registration r
WHERE ps.PType = 1 AND ps.RegId = r.RegId
GROUP BY ps.ItemId, ps.RegId, LEFT(r.RegDate,7)) p
WHERE m.MedId = p.ItemId AND m.UnitId = b.Id AND m.TypeId = b1.Id
GO
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -