一个较难的SQL查询

[ltolll]

实际上是要生成一个视图
表
客户ID 消费种类 消费金额
01 时装 100
01 皮草 1000
02 时装 210
03 时装 2200
03 皮草 3000

要求:根据客户的"消费种类"和"消费金额"计算出"回报金额"
计算方法:
时装消费:
(当 100<=时装消费金额<1000 时) 时装回报金额 = 消费金额 * 0.1
(当 1000<=时装消费金额时) 时装回报金额 = 消费金额 * 0.2
皮草消费:
(当 1000<=时装消费金额<2000 时) 皮草回报金额 = 消费金额 * 0.2
(当 2000<=时装消费金额时) 皮草回报金额 = 消费金额 * 0.3

则可查出
客户ID 时装回报金额 皮草回报金额 回报金额
01 10 200 210
02 21 0 21
03 440 900 1340

[wing_er]

游标 + CASE

[yanlixin4csdn]

SELECT 'Price Category' =
CASE
WHEN (100<=m)and(m<1000) THEN cast(m*0.1 as varchar(10))

END
from
(select 客户ID,sum(消费金额) as m from table1
group by 客户ID) b

不知你是不是这个意思

[jinyadong]

SELECT A.客户ID,A.时装回报金额,B.皮草回报金额,(A.时装回报金额+B.皮草回报金额) AS 回报金额
FORM
(
SELECT 客户ID,
(CASE
WHEN (SUM(消费金额)>=100 AND SUM(消费金额)<1000)
THEN SUM(消费金额)* 0.1
WHEN SUM(消费金额)>=1000 THEN SUM(消费金额)* 0.2 END)
AS 时装回报金额
FROM
表
WHERE
消费种类 = '时装'
GROUP BY 客户ID
) AS A
INNER JOIN ON
(
SELECT 客户ID,
(CASE
WHEN (SUM(消费金额)>=1000 AND SUM(消费金额)<2000)
THEN SUM(消费金额)* 0.2
WHEN SUM(消费金额)>=2000 THEN SUM(消费金额)* 0.3 END)
AS 皮草回报金额
FROM
表
WHERE
消费种类 = '皮草'
GROUP BY 客户ID
) AS B
WHERE A.客户ID=B.客户ID

[JasonHeung]

Create table test (客户ID char(10),消费种类 char(10),消费金额 int)
go
insert test values('01','时装',100)
insert test values('01','皮草',1000)
insert test values('02','时装',210)
insert test values('03','时装',2200)
insert test values('03','皮草',3000)

--想变成

--姓名 语文 数学 英语
--张三 80 86 75
--李四 78 85 78

Create table test2 (客户ID char(10),消费种类 char(10),回报金额 int)
insert into test2 select 客户ID,消费种类,消费金额 * (case 消费种类
when '时装' then (case when 消费金额 < 100 then 0.0 when 消费金额 < 1000 then 0.1 else 0.2 end)
when '皮草' then (case when 消费金额 < 1000 then 0.0 when 消费金额 < 2000 then 0.2 else 0.3 end)

end) as 回报金额 from test

declare @sql varchar(8000)
select @sql = 'select 客户ID'
select @sql = @sql + ',sum(case 消费种类 when '''+消费种类+''' then 回报金额 else 0 end) ['+消费种类+']' from (select distinct 消费种类 from test2) as a
select @sql = @sql+' from test2 group by 客户ID'
--select @sql
exec(@sql)


drop table test2
drop table test

横向求和，我也不知道怎么做：）

[Toti]

可以用SQL-SERVER的函数来实现

[conan19771130]

楼上说得够多了，你是sql还是oracle啊

[xxj]

表的定义：
Create Table FEE(
CUSTOMERID CHAR(2) NULL,
T CHAR(1) NULL,
FEE Float NULL
)
如果是在MS SQL SERVER:
select CUSTOMERID,T,Sum(X) Summary
from
(select CUSTOMERID,T,FEE,
Decode(T,'1', (CASE WHEN FEE>=100 AND FEE<1000 THEN FEE * 0.1 WHEN FEE>100 THEN FEE*0.2 ELSE 0 END), '2',(CASE WHEN FEE>=1000 AND FEE<2000 THEN FEE*0.2 WHEN FEE>=2000 THEN FEE*0.3 ELSE 0 END )) X
from Fee) K
group by CUSTOMERID,T With Cube

[xxj]

In Oracle 9I:
select CUSTOMERID,T,Sum(X) Summary
from
(select CUSTOMERID,T,FEE, Decode(T,'1', (CASE WHEN FEE>=100 AND FEE<1000 THEN FEE * 0.1 WHEN FEE>100 THEN FEE*0.2 ELSE 0 END), '2',(CASE WHEN FEE>=1000 AND FEE<2000 THEN FEE*0.2 WHEN FEE>=2000 THEN FEE*0.3 ELSE 0 END )) X from Fee) K
group by CUBE(CUSTOMERID,T)

[chenzhanyiczy]

jinyadong(刀耐特)写得对

[jinjazz]

--建立测试环境
Create Table 表(客户ID varchar(10),消费种类 varchar(10),消费金额 int)
--插入数据
insert into 表
select '01','时装','100' union
select '01','皮草','1000' union
select '02','时装','210' union
select '03','时装','2200' union
select '03','皮草','3000'
--select * from 表
--测试语句

select * ,回报金额=时装回报金额+皮草回报金额
from
(select 客户ID,
时装回报金额=sum(isnull(case when 消费种类='时装' then
case when 消费金额 between 100 and 1000 then 消费金额*0.1
else 消费金额*0.2
end
end,0)),
皮草回报金额=sum(isnull(case when 消费种类='皮草' then
case when 消费金额 between 1000 and 2000 then 消费金额*0.2
else 消费金额*0.3
end
end,0))
from 表
group by 客户ID)a


--删除测试环境
Drop Table 表



/*----测试结果

客户ID 时装回报金额 草回报金额 回报金额
--------------------------------- ----------------------------------------
01 10.0 200.0 210.0
02 21.0 .0 21.0
03 440.0 900.0 1340.0
*/

[ltolll]

首先谢谢大家.
TO: yanlixin4csdn(闫力昕) ,不能根据"消费种类"计算
TO: xxj(弹指一挥间) 对不起,忘了说明,数据库是SQL
TO:jinyadong(刀耐特), jinjazz(近身剪（充电中...）) 正确
jinjazz(近身剪（充电中...）) 的方法可将"回报金额"为0的也查到,更好些.

[ccghghgj]

关注。。。

===============================
7:18 2005-8-26

[超叔csdn]

jinyadong(刀耐特)写的很好

[luqixing]

我SQL没有学好，学习……