日期:2014-05-17 浏览次数:20441 次
Customer : C_ID, JoinDate
Order : O_ID, C_ID, ProductID
Product : P_ID, 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.00
select 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 &n