又一个复杂的SQL问题，请求大家帮助。

[jxlmz]

table1
meetnum meettype
1-101 标准会议室_15人
1-102 标准会议室_10人
1-103 标准会议室_10人
1-104 标准会议室_10人
1-105 视频会议室_20人
2-204 标准会议室_10人
2-205 标准会议室_10人
2-206 标准会议室_10人


table2
id mtmeetnum destineday fromhour fromminute tohour tominute
1095107100000000013 1-101 2010-01-21 09 10 10 10
1095107100000000015 1-101 2010-01-21 11 10 12 10
1095107100000000017 1-101 2010-01-21 13 10 14 10
1095107100000000014 1-105 2010-01-25 13 00 17 00
1095107100000000016 1-105 2010-01-21 13 00 17 00

要查出的结果 条件 where table2.destineday='2010-01-21' order by meetnum,fromhour,fromminute desc
注意: 重复1-101的地方不显示方法见例1：
meetnum destineday fromhour fromminute tohour tominute
1-101 2010-01-21 09 10 10 10
2010-01-21 11 10 12 10
2010-01-21 13 10 14 10
1-102
1-103
1-104
1-105 2010-01-21 13 00 17 00
2-204
2-205
2-206
------------------------------------
例1：
SELECT DECODE(RN, 1, mtmeetnum), destineday,staffname,fromhour,FROMMINUTE
FROM (SELECT t.mtmeetnum,
t.destineday,
t.staffname,
fromhour,
FROMMINUTE,
ROW_NUMBER() OVER(PARTITION BY t.mtmeetnum ORDER BY MTMEETNUM ,fromhour,FROMMINUTE DESC) RN
FROM use_mtmeetmessage t
WHERE t.isdelete = 'F'
ORDER BY T.MTMEETNUM ,t.fromhour,T.FROMMINUTE DESC);

[ojuju10]

用left join

[crazylaa]

jf

[jxlmz]

解决了，
select decode(nn,1,bbb.meetnum),
bbb.*
from
(select t.meetnum, '2010-01-21'aaa,m.*,ROW_NUMBER() over (partition by t.meetnum order by t.meetnum,m.fromhour,m.fromminute desc)nn
from use_mtmeeting t
left join use_mtmeetmessage m on t.meetnum||'2010-01-21' =
m.mtmeetnum||m.destineday
order by t.meetnum)bbb

给大家参考

[todayandtomorrow]

left join 是问题得要点哦

[tangren]

学习一下

[xuhua7788]

楼主不错,...呵呵

[xiaocao_2005]

我猜这个是页面显示效果吧?
meetnum destineday fromhour fromminute tohour tominute
1-101 2010-01-21 09 10 10 10
2010-01-21 11 10 12 10
2010-01-21 13 10 14 10
1-102
1-103
1-104
1-105 2010-01-21 13 00 17 00
2-204
2-205
2-206
如果是的话,在数据库里面处理这样的逻辑不划算,逻辑运算不是数据库的强项.即使按照lz的做法实现了,code的效率和可读性都稍微有点打折.
数据库就负责返回对meetnum 排序的结果,然后在前台去判断后面重复的meetnum 是否显示就可以了.
如果猜测错了,lz请不要见怪,就当没说....