目前有三个表:
Customer : C_ID, JoinDate
Order : O_ID, C_ID, ProductID
Product : P_ID, UnitPrice我现在想在客户下单的时候自动检查客户的JoinDate,检查客户是不是已经加入了超过30天,如果是的话,那么在下单的时候,在UnitPrice上面打八折。这样的存储过程应该如何实现啊?
Customer : C_ID, JoinDate
Order : O_ID, C_ID, ProductID
Product : P_ID, UnitPrice我现在想在客户下单的时候自动检查客户的JoinDate,检查客户是不是已经加入了超过30天,如果是的话,那么在下单的时候,在UnitPrice上面打八折。这样的存储过程应该如何实现啊?
-- 作者:Gaojier
-- 应用:打折优惠统计
-- 日期:2012-11-21
--**************************************
--创建测试表
CREATE TABLE Customer(C_ID VARCHAR(4),JoinDate DATETIME)
CREATE TABLE [Order](O_ID VARCHAR(10),C_ID VARCHAR(4),ProductID Varchar(4))
CREATE TABLE Product(P_ID VARCHAR(4),UnitPrice Decimal(18,2))--插入测试数据
INSERT Customer
SELECT '0001','2010-10-10 00:00:000' UNION ALL
SELECT '0002','2011-10-10 00:00:000' UNION ALL
SELECT '0003','2012-08-10 00:00:000' UNION ALL
SELECT '0004','2010-11-12 00:00:000' UNION ALL
SELECT '0005','2012-11-20 00:00:000' UNION ALL
SELECT '0006','2012-11-10 00:00:000' insert [Order]
select '0001','0001','0001' union all
select '0002','0002','0001' union all
select '0003','0003','0001' union all
select '0004','0004','0001' union all
select '0005','0005','0001' union all
select '0006','0006','0001' union all
select '0007','0005','0002' union all
select '0008','0004','0002' union all
select '0009','0003','0002' union all
select '0010','0002','0002' union all
select '0011','0001','0002' union all
select '0012','0006','0003' INSERT Product
select '0001',18.00 union all
select '0002',25.00 union all
select '0003',23.00select T1.O_ID,T1.C_ID,
(CASE WHEN DATEDIFF(d,T2.JoinDate,getdate())>30 THEN T3.UnitPrice*0.8 ELSE T3.UnitPrice END) AS Price
from [Order] T1 join
Customer T2 ON T1.C_ID=T2.C_ID join
Product T3 on T1.ProductID=T3.P_ID DROP TABLE Customer
DROP TABLE [Order]
DROP TABLE Product
(6 行受影响)(12 行受影响)(3 行受影响)
O_ID C_ID Price
---------- ---- ---------------------------------------
0001 0001 14.400
0002 0002 14.400
0003 0003 14.400
0004 0004 14.400
0005 0005 18.000
0006 0006 18.000
0007 0005 25.000
0008 0004 20.000
0009 0003 20.000
0010 0002 20.000
0011 0001 20.000
0012 0006 23.000(12 行受影响)
-- 作者:Gaojier
-- 应用:按记录数分段显示数据,且补充不足数据
-- 日期:2012-12-01
--**************************************
CREATE TABLE Tab(id int,name varchar(10))
INSERT Tab
SELECT 1,'a' UNION ALL
SELECT 2,'a' UNION ALL
SELECT 3,'a' UNION ALL
SELECT 4,'a' UNION ALL
SELECT 5,'a' UNION ALL
SELECT 6,'a' UNION ALL
SELECT 7,'a' UNION ALL
SELECT 8,'a' UNION ALL
SELECT 9,'a' UNION ALL
SELECT 10,'a' UNION ALL
SELECT 11,'a' UNION ALL
SELECT 12,'a' UNION ALL
SELECT 13,'a' UNION ALL
SELECT 14,'a' UNION ALL
SELECT 15,'a' UNION ALL
SELECT 16,'a' UNION ALL
SELECT 17,'a' UNION ALL
SELECT 18,'a'
--取得表中最大ID号,需要保证ID不断裂
DECLARE @maxid INT
SELECT @maxid=MAX(id) FROM Tab
--创建临时表,保存必须获取的最终结果的行数
IF OBJECT_ID('#TEMP') IS NOT NULL
BEGIN
DROP TABLE #Temp
END
CREATE TABLE #Temp(id INT)
--计算获取的记录数,并存入临时表
DECLARE @id INT
SET @id=1
SET @maxid=(@maxid/5+1)*5
WHILE @id<=@maxid
BEGIN
INSERT #temp
SELECT @id
SET @id=@id+1
END
--得到结果
SELECT T2.id,T2.name,((T1.id-1)/5)+1
FROM #temp T1 LEFT JOIN
Tab T2 ON T1.id=T2.id
--删除测试环境
DROP TABLE tab
DROP TABLE #Temp
id name
----------- ---------- -----------
1 a 1
2 a 1
3 a 1
4 a 1
5 a 1
6 a 2
7 a 2
8 a 2
9 a 2
10 a 2
11 a 3
12 a 3
13 a 3
14 a 3
15 a 3
16 a 4
17 a 4
18 a 4
NULL NULL 4
NULL NULL 4