# 求助一条sql语句

0

ghelement | 初学一级 | 园豆：20

0

`--创建价格表CREATE TABLE #ApplePrice (     [Name] NVARCHAR(10)    ,[Quantity] INT    ,[Amount] MONEY)GOINSERT INTO #ApplePrice (     [Name]    ,[Quantity]    ,[Amount]) SELECT '苹果',200,50 UNION ALL SELECT '苹果',500,100 UNION ALL SELECT '苹果',800,150 UNION ALL SELECT '苹果',1000,200 UNION ALL SELECT '苹果',1200,250 UNION ALL SELECT '苹果',1500,300 GO--创建折扣表CREATE TABLE #AppleRebate (     [Name] NVARCHAR(10)    ,[Quantity] INT    ,[Rebate] FLOAT)GOINSERT INTO #AppleRebate (     [Name]    ,[Quantity]    ,[Rebate])SELECT '苹果',500,0.95 UNION ALL SELECT '苹果',1000,0.90 UNION ALLSELECT '苹果',1500,0.85GO--SELECT * FROM #ApplePriceSELECT * FROM #AppleRebate--SELECT      T1.Name    ,T1.Quantity    ,T1.Amount    ,(SELECT TOP 1 Rebate         FROM #AppleRebate         WHERE #AppleRebate.Name = T1.Name            AND #AppleRebate.Quantity >= T1.Quantity         ORDER BY #AppleRebate.Quantity ASC) AS RebateFROM #ApplePrice AS T1 /*删除临时表DROP TABLE #ApplePriceDROP TABLE #AppleRebate*/--望求交流啊.~睡了.~`

dotNetDR_ | 园豆：2078 (老鸟四级) | 2011-03-16 03:14
0

create table t1
(
[name] varchar(10),
num int,
price float
)

insert into t1 values('苹果',200,50)
insert into t1 values('苹果',500,100)
insert into t1 values('苹果',800,150)
insert into t1 values('苹果',1000,200)
insert into t1 values('苹果',1200,250)
insert into t1 values('苹果',1500,300)

create table t2
(
[name] varchar(10),
num int,
discount float
)
insert into t2 values('苹果',500,0.95)
insert into t2 values('苹果',1000,0.90)
insert into t2 values('苹果',1500,0.85)

-------------------------------------
select *,
(select max(discount) from t2 where num>=t1.num)
from t1

0

--创建价格表
CREATE TABLE #ApplePrice
(
[Name] NVARCHAR(10)    ,
[Quantity] INT    ,
[Amount] MONEY
)
GO
INSERT INTO #ApplePrice (     [Name]    ,[Quantity]    ,[Amount])
SELECT '苹果',200,50
UNION ALL SELECT '苹果',500,100
UNION ALL SELECT '苹果',800,150
UNION ALL SELECT '苹果',1000,200
UNION ALL SELECT '苹果',1200,250
UNION ALL SELECT '苹果',1500,300
GO
--创建折扣表
CREATE TABLE #AppleRebate
(
[Name] NVARCHAR(10)    ,
[QuantityMin] INT    ,
[QuantityMax] INT,
[Rebate] FLOAT
)
GO
INSERT INTO #AppleRebate ([Name],[QuantityMin],[QuantityMax],[Rebate])
SELECT '苹果',0,500,0.95
UNION ALL
SELECT '苹果',501,1000,0.90
UNION ALL
SELECT '苹果',1001,1500,0.85GO

SELECT * FROM #ApplePrice
SELECT * FROM #AppleRebate
/*查询结果*/
select p.Name, p.Quantity, p.Amount,r.Rebate
from #ApplePrice p
inner join #AppleRebate r on p.Name=r.Name and p.Quantity between r.QuantityMin and r.QuantityMax
GO
/*删除临时表*/
DROP TABLE #ApplePrice
DROP TABLE #AppleRebate
GO

您需要登录以后才能回答，未注册用户请先注册