select TableA.goodsid,TableA.price,TableB.timewritten from TableB,TableA TableB.seqid=TableA.seqid group by TableA.goodsid,TableA.price,TableB.timewritten 可不可以在产生的分组中进行排序和只显示每个小组的第一个记录呢,谢谢,
解决方案 »
- 紧急求助 回闪sql
- 请大家帮忙 ,小弟初学,要写个触发器
- 急!两oracle字符集不一致如何实现信息互访
- oracle scheduler运行不了
- oracle10g 如何能查出用户访问数据库的历史记录
- 今天终于学会了配置Oracle for SQL Sever的Transparent Gateway了,真高兴!!散分!!
- Oracle的使用问题
- 安装oracle第三张盘的时候,出现OracleOra90Agent出错,错误是259:没有可用的数据,请教?
- 妹妹有问题啊!快来帮忙!!
- 求一个简单的存储过程,保存一个大文本,加分加分。
- 怎样随机生成 常用汉字字符串?
- update不了,这语句要怎么写啊
select distinct TableA.goodsid,TableA.price,TableB.timewritten from TableB,TableA TableB.seqid=TableA.seqid 难道是想要这个结果吗
1 40000 5.7000 2006-12-30 17:29:26
2 40000 5.7000 2007-1-17 8:51:31
3 40000 5.8000 2007-3-7 13:35:48
4 40000 5.8500 2006-11-22 8:13:33
5 39000 0.3300 2006-12-7 14:32:52
6 39000 0.3300 2007-1-24 12:58:21
7 39000 0.3300 2007-3-12 19:27:51
8 39000 0.3300 2007-4-20 7:45:42
9 39000 0.3300 2007-5-12 8:11:06
10 39000 0.3300 2007-6-7 7:52:54
11 39100 1.7000 2007-1-6 8:58:49
12 39100 1.7000 2007-2-6 17:21:54
13 39100 1.7000 2007-5-23 17:38:49
GOODSID PRICE MAX(T_BILL_ABSTRACT.TIMEWRITTE
1 40000 5.7000 2006-12-30 17:29:26
2 40000 5.7000 2007-1-17 8:51:31
3 40000 5.8000 2007-3-7 13:35:48
4 40000 5.8500 2006-11-22 8:13:33
5 39000 0.3300 2006-12-7 14:32:52
6 39000 0.3300 2007-1-24 12:58:21
7 39000 0.3300 2007-3-12 19:27:51
8 39000 0.3300 2007-4-20 7:45:42
9 39000 0.3300 2007-5-12 8:11:06
10 39000 0.3300 2007-6-7 7:52:54
11 39100 1.7000 2007-1-6 8:58:49我要的结果是这样的
GOODSID PRICE MAX(T_BILL_ABSTRACT.TIMEWRITTE
3 40000 5.8000 2007-3-7 13:35:48
10 39000 0.3300 2007-6-7 7:52:54
11 39100 1.7000 2007-1-6 8:58:49
GOODSID MAX(T_BILL_GOODS_SERIAL.PRICE) MAX(T_BILL_ABSTRACT.TIMEWRITTE
1 40000 5.7 2006-12-30 17:29:26
2 40000 5.7 2007-1-17 8:51:31
3 40000 5.8 2007-3-7 13:35:48
4 40000 5.85 2006-11-22 8:13:33
5 39000 0.33 2006-12-7 14:32:52
6 39000 0.33 2007-1-24 12:58:21
7 39000 0.33 2007-3-12 19:27:51
8 39000 0.33 2007-4-20 7:45:42
9 39000 0.33 2007-5-12 8:11:06
10 39000 0.33 2007-6-7 7:52:54
11 39100 1.7 2007-1-6 8:58:49
12 39100 1.7 2007-2-6 17:21:54
13 39100 1.7 2007-5-23 17:38:49
14 39100 1.75 2007-3-11 15:18:35
我要的是如下结果:
GOODSID PRICE price TIMEWRITTE
1 40000 5.8000 2007-3-7 13:35:48
2 39000 0.3300 2007-6-7 7:52:54
3 39100 1.7 2007-5-23 17:38:49
1 40000 5.8000 2007-3-7 13:35:48
2 39000 0.3300 2007-6-7 7:52:54
3 39100 1.7 2007-5-23 17:38:49
GOODSID PRICE TIMEWRITTE
1 40000 5.8000 2007-3-7 13:35:48
2 39000 0.3300 2007-6-7 7:52:54
3 39100 1.7 2007-5-23 17:38:49
from
(
select t.*,row_number() over(partition by goodsid order by timewritten desc) rn
from
(
select TableA.goodsid,TableA.price,TableB.timewritten from TableB,TableA
where TableB.seqid=TableA.seqid
)t
)tt
where tt.rn=1