改行很久了，今天需要一个SQL语句，竟然不会写了，请大家帮忙！(300分)

流浪者 · 2008-10-20

表的结构如下：
ID mingcheng shuliang shijian
1 番茄 150 2008-10-1
2 土豆 200 2008-10-1
3 黄瓜 102 2008-10-1
4 番茄 145 2008-10-2
5 黄瓜 150 2008-10-2
6 土豆 98 2008-10-2
.
.
.
我现在需要按两天之间数量变化来排序，得到的结果就是
排名名称变化
1 黄瓜 43
2 番茄 -5
3 土豆 -102
.
.
.
请问这条SQL语句应该怎么写？
谢谢

struts · 2008-10-21

感觉一条sql写不来。

struts · 2008-10-21

日期，是不是定死了，定死了倒是好写

ouyangwenlin · 2008-11-04

分级汇总

ouyangwenlin · 2008-11-04

SELECT CASE WHEN (GROUPING(mingcheng) = 1) then
'ALL'
else
ISNULL(mingcheng, 'UNKNOWN')
END AS mingcheng,
SUM(shuliang) AS bianhuaSum
FROM 表名
GROUP BY Item WITH CUBE
GO

jiaa · 2008-11-06

select mingcheng,sl1-sl2
from
(
select mingcheng ,sum(sl1) as sl1,sum(sl2) as sl2
from
(
select mingcheng,case when shijian=max(shijian) then
shuliang else
0 end as sl1,case when shijian<>max(shijian) then
shuliang else
0 end as sl2
from xxxxxx
)
group by mingcheng
)

bestdoctor88 · 2008-11-07

我测试过了，没有问题
select * from
(
select sum(case when riqi='2008-09-01' then
-1 * shuliang when riqi='2008-09-02' then
shuliang end )as chayi from t_test group by mingcheng
) as mm
order by chayi

幸福人生1 · 2008-11-12

这是Oracle的写法，测试过了，正确！
select
a.mingcheng,(nvl(b.shuliang,0)-nvl(a.shuliang,0)) ChaYi,
a.ShiJian,
to_char(a.shijian,'YYYY-MM-DD')||' VS '||to_char(nvl(b.shijian,a.SHIJIAN+1),'YYYY-MM-DD') 对比时间
from VgtabChg a left join VgtabChg b
on a.MingCheng=b.MingCheng and a.shijian+1=b.ShiJian
order by a.ShiJian,(nvl(b.shuliang,0)-nvl(a.shuliang,0)) Desc

zhuzhi129 · 2008-11-18

都是牛人

改行很久了，今天需要一个SQL语句，竟然不会写了，请大家帮忙！(300分)

流浪者

Unregistered / Unconfirmed

struts

Unregistered / Unconfirmed

struts

Unregistered / Unconfirmed

ouyangwenlin

Unregistered / Unconfirmed

ouyangwenlin

Unregistered / Unconfirmed

jiaa

Unregistered / Unconfirmed

bestdoctor88

Unregistered / Unconfirmed

幸福人生1

Unregistered / Unconfirmed

zhuzhi129

Unregistered / Unconfirmed

Similar threads