请教一个数据库问题(在线等)
给定一张名字变化表namechange 如下:
time oldname newname
1:00 jack peter (1点,名字由jack变为peter,下同)
2:00 peter john
3:00 john mike
4:00 mike alex
要实现的是查询名字存在的时段
查mike
结果为:
name start time end time
mike 3:00 4:00
我是这样实现的:
select t2.newname as name, t2.time as [start time], t1.time as [end time]
from namechange t1, namechange t2
where t2.newname=mike
and t1.oldname=mike
查mike是没有问题,但是查一头一尾的jack和alex就出问题了,
因为不能同时满足 and 前后的条件,所以就会不显示。
而我理想化的状态是,查jack会这样:
name start time end time
jake 0 1:00 (将没有头的设为0)
查alex:
name start time end time
alex 4:00 24:00 (将没有尾的设为24)
问一下各位,要怎么改。(不能对namechange表做改动) 先谢谢了。
[解决办法]
SELECT B.oldname,B.TIME AS starttime,'24:00' AS ENDtime FROM TTH A RIGHT JOIN TTH B ON A.oldname=B.newname
WHERE A.oldname IS NULL
UNION
SELECT A.oldname,'0:00' AS starttime,A.TIME AS endtime FROM TTH A LEFT JOIN TTH B ON A.oldname=B.newname
WHERE B.oldname IS NULL
[解决办法]
1> select * from t_siegebaoniu;2> gotime oldname newname----------- ---------- ---------- 1 jack peter 2 peter john 3 john mike 4 mike alex(4 rows affected)1> select isnull(t1.time,0),isnull(t2.time,24)2> from3> (select min(time) as time from t_siegebaoniu where newname='jack') t1,4> (select min(time) as time from t_siegebaoniu where oldname='jack') t2;5> go----------- ----------- 0 1(1 rows affected)1> select isnull(t1.time,0),isnull(t2.time,24)2> from3> (select min(time) as time from t_siegebaoniu where newname='alex') t1,4> (select min(time) as time from t_siegebaoniu where oldname='alex') t2;5> go----------- ----------- 4 24(1 rows affected)1>