题目
解答
SUM(t1.销售数量*t1.销售单价) --SUM(销售数量*销售单价) 也正确
t1.商品号 = t2.商品号
year(t1.销售时间) --year(销售时间) 也正确
t2.类别 = @lb
解析
建表
if not exists(SELECT name FROM SYS.DATABASES WHERE name = 'SALES')
CREATE DATABASE SALES
USE SALES
if exists(SELECT name FROM sys.objects WHERE name = 'SELL')
DROP TABLE SELL
if exists(SELECT name FROM sys.objects WHERE name = 'COMMODITY')
DROP TABLE COMMODITY
CREATE TABLE COMMODITY
(
NO INT PRIMARY KEY,
NAME CHAR(10) NOT NULL UNIQUE,
CATEGORY CHAR(10) NOT NULL,
COST FLOAT NOT NULL
)
CREATE TABLE SELL
(
NO INT FOREIGN KEY REFERENCES COMMODITY(NO),
TIME DATETIME NOT NULL,
QUANTITY INT NOT NULL,
PRICE FLOAT NOT NULL,
PROFIT FLOAT
)
插入值
INSERT INTO COMMODITY
VALUES
(1,'冰红茶','饮料', 1.5),
(2,'方便面','食品',0.5),
(3,'矿泉水','饮料',0.8)
创建触发器
练习上一次所学的触发器知识,在题目表格之外,新增一列此次交易利润。通过创建触发器,提供购买的商品号与数量,计算此次交易的利润并填入表格。
注意:此表与题目表格区别在此
并且,通过阅读文章SQL Server 触发器批量数据行处理,做到了批量处理交易数据。
计算商品利润触发器
USE SALES
IF EXISTS(SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[COUNTPROFIT]') AND OBJECTPROPERTY(ID, N'ISTRIGGER') = 1)
DROP TRIGGER COUNTPROFIT
GO
--批量插入触发
CREATE TRIGGER COUNTPROFIT
ON SELL
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO SELL
SELECT NO,TIME,QUANTITY,PRICE,(QUANTITY*(SELECT COST
FROM COMMODITY C
WHERE C.NO = I.NO)) AS PROFIT
FROM inserted I
END;
插入数据测试
INSERT INTO SELL
VALUES
--商品号,日期,数量,价格,获利(由触发器计算)
(1,GETDATE(),2,3, null ),
(2,GETDATE(),2,1, null ),
(1,GETDATE(),2,3, null ),
(3,GETDATE(),2,1, null )
存储过程(完整答案)
USE SALES
IF EXISTS(SELECT name FROM SYS.objects WHERE NAME ='p_TotalProfit')
DROP PROC p_TotalProfit
GO
CREATE PROC p_TotalProfit
@lb char(10)
AS
SELECT NAME AS 商品名, (SELECT SUM(QUANTITY*PRICE)
FROM SELL t1
WHERE t1.NO=t2.NO and year(t1.time)=year(GETDATE())) AS 销售总金额
FROM COMMODITY t2
WHERE t2.CATEGORY = @lb
GO
测试结果