我有一张视图如下.vw_jiagecx 价格表.里面记录每一段的价格变动.比如spdm为0001c22 在2008-1-17价格(je5)为180元.
2008-2-30为220元.
http://p15.freep.cn/p.aspx?u=v20_p15_p_0805211124371674_0.jpg我想取某个商品最后的价格.就是说比如当商品为0001c22时.他有三个时间的变价.我要选择最后一次的变价价格(je5)
2008-2-30为220元.
http://p15.freep.cn/p.aspx?u=v20_p15_p_0805211124371674_0.jpg我想取某个商品最后的价格.就是说比如当商品为0001c22时.他有三个时间的变价.我要选择最后一次的变价价格(je5)
要不你就做一个翻译,比如:
spdm=商品代码
khdm=客户代码
...select * from vw_jiagecx as a
where rq_l=(select min(rq_l) as rq_l from vw_jiagecx where spdm=a.spdm)
chaxun.SQL.Add('where spdm like ''%'+hh+'%''');
chaxun.sql.Add('and khdm like ''%'+kh+'%''');
chaxun.SQL.add('group by khdm,spdm,je5') ;我写的是这个.但是好像出来的不是最后一次调价的.而是调过价的都出来了.
我用语句查出来的结果就是这样.我只想要日期最后的商品代码的价格.......order by 好像无法跟group by 放到一起.请问怎么改进>/?
我用的语句是:
select khdm ,spdm ,je5 ,max(rq_1) from vw_jiagecx
where spdm='3029W46' and khdm='D11023'
group by khdm,spdm,je5
from vw_jiagecx
where khdm||时间
in(
select khdm||time
from(
select khdm, max(时间) as time
from vw_jiagecx
group by khdm))
from vw_jiagecx
where khdm||rq_1
in(
select khdm||time
from(
select khdm, max(rq_1) as time
from vw_jiagecx
group by khdm))
'|' 附近有语法错误。
服务器: 消息 102,级别 15,状态 1,行 5
'|' 附近有语法错误。
服务器: 消息 102,级别 15,状态 1,行 9
')' 附近有语法错误。nb95463034 这个似乎是oracle里面的语法. sql 里面好像不行哦.
.....我sql也比较菜
nb95463034 你的句子我在sql server里面改成下面的样试.但是还有个.
服务器: 消息 102,级别 15,状态 1,行 9
')' 附近有语法错误。select *
from vw_jiagecx
where khdm|rq_1
in(
select khdm|time
from(
select khdm, max(rq_1) as time
from vw_jiagecx
group by khdm)))
谢谢大家的帮忙.真的非常感谢
from vw_jiagecx a,(select spdm,max(rq_1) as rq_1 from vw_jiagecx group by spdm) b
where a.spdm=b.spdm
and a.rq_1 = b.rq_1
and a.khdm like '%somebody%'
and a.spdm like '%something%'相对客户+商品调价:select spdm as 商店代码,khdm as 商品代码,je5 as 价格,rq_1 as 启用日期
from vw_jiagecx a,(select khdm,spdm,max(rq_1) as rq_1 from vw_jiagecx group by khdm,spdm) b
where a.spdm=b.spdm
and a.khdm=b.khdm
and a.rq_1 = b.rq_1
and a.khdm like '%somebody%'
and a.spdm like '%something%'
select …
[/Quote]服务器: 消息 209,级别 16,状态 1,行 1
列名 'spdm' 不明确。
服务器: 消息 209,级别 16,状态 1,行 1
列名 'khdm' 不明确。
服务器: 消息 209,级别 16,状态 1,行 1
列名 'rq_1' 不明确。是不是因为sql和oracle 的写法不一样>?能说明一下吗?后面的b我不太明白是什么意思.谢谢.
from vw_jiagecx as a,(select khdm,spdm,max(rq_1) as rq_1 from vw_jiagecx group by khdm,spdm) as b
where a.spdm=b.spdm
and a.khdm=b.khdm
and a.rq_1 = b.rq_1
and a.khdm like '%D11023%'
and a.spdm like '%3029W46%'
呵呵.我改了一下.我来试试看先.谢谢了
select a.spdm as 商店代码,a.khdm as 商品代码,a.je5 as 价格,a.rq_1 as 启用日期
from vw_jiagecx a,(select spdm,max(rq_1) as rq_1 from vw_jiagecx group by spdm) b
where a.spdm=b.spdm
and a.rq_1 = b.rq_1
and a.khdm like '%somebody%'
and a.spdm like '%something%'相对客户+商品调价:
select a.spdm as 商店代码,a.khdm as 商品代码,a.je5 as 价格,a.rq_1 as 启用日期
from vw_jiagecx a,(select khdm,spdm,max(rq_1) as rq_1 from vw_jiagecx group by khdm,spdm) b
where a.spdm=b.spdm
and a.khdm=b.khdm
and a.rq_1 = b.rq_1
and a.khdm like '%somebody%'
and a.spdm like '%something%'