A表
id userid jin wei
1 user001 20 20
2 user002 33 34
3 user003 33 35
4 user001 34 45
5 user002 33 34
B表
id userid 如果B表中有A表的某条数据,则分组取出(B中ID+1后)A表的内容.1.如果B表中记录为
ID userid
1 user001
2 user002
3 user003
查询出来的记录为:
4 user001 34 45
5 user002 33 342.如果B表为空则查询出来的记录为
ID userid
1 user001
2 user002
3 user003
id userid jin wei
1 user001 20 20
2 user002 33 34
3 user003 33 35
4 user001 34 45
5 user002 33 34
B表
id userid 如果B表中有A表的某条数据,则分组取出(B中ID+1后)A表的内容.1.如果B表中记录为
ID userid
1 user001
2 user002
3 user003
查询出来的记录为:
4 user001 34 45
5 user002 33 342.如果B表为空则查询出来的记录为
ID userid
1 user001
2 user002
3 user003
where A.userid=B.userid
and A.ID=B.ID+1
如果B表中没有A表中的某条记录,则取得A表中ID最小的哪条记录.
ID userid
1 user001
4 user004那a中记录应该是什么啊?
我试写了一下,如果B表中没有记录,则查询出来的结果
select aa.userid,decode(bb.id,'','',bb.id,bb.id) as id from (select distinct(nurseid) from a ) aa,b bb where aa.usreid=b.userid(+)
--------------------------------------------
userid id
user001
user002由于user001 ,user002都为空则取得A表中user001,user002用户ID最小的哪条,因为user001,user002在A表中有多条.如果查询出来id不为空,则取得A表中id刚好大于此ID的哪条记录...
还是没看明白,问一下。如果B表中有A表中的一组数据,是指什么?
是这样吗b.id=a.id and b.userid=a.userid,还是只有b.userid=a.userid则取出A表中ID刚好大于B中的哪一条userid的记录。
如果没有怎么办?
如果A表中没有了,则不取!原理是这样的,多个GPS中端不停在向数据库发数据,又个中端能过浏览器观看GPS中端当前位置,所以建了两张表A表负责发数据,B表主要是第个中端的数据,当浏览器要看数据时,首先把A表中的数据通过分组取得一组最小的拷贝到B到,同时记下浏览器中端的IP,再把满足本机IP的B表数据读出来显示在自已的中端,这样B表中如果有了属于自己IP的记录,再去A表中读大于B表中的记录,就这样一直重复的读下去.
where exists
(select 1 from test1 a, test2 b
where a.userid = b.userid(+)
and (b.id < a.id or b.userid is null)
group by a.userid
having min(a.id) = c.id
)
order by id测试数据如下:SQL> select * from test1 a; ID USERID JIN WEI
---------- ---------- ---------- ----------
1 user001 20 20
2 user002 33 33
3 user003 33 45
4 user001 34 34
7 user001 0 0
5 user002 33 35
8 user002 0 0
9 user004 0 08 rows selectedSQL> select * from test2 b; ID USERID
---------- ----------
3 user003
1 user001
2 user002select * from test1 c
where exists
(select 1 from test1 a, test2 b
where a.userid = b.userid(+)
and (b.id < a.id or b.userid is null)
group by a.userid
having min(a.id) = c.id
)
order by idSQL>
9 / ID USERID JIN WEI
---------- ---------- ---------- ----------
4 user001 34 34
5 user002 33 35
9 user004 0 0
---------- ----------
3 user003
1 user001
2 user002
4 user001
5 user002
9 user004select * from test1 c
where exists
(select 1 from test1 a, test2 b
where a.userid = b.userid(+)
and (b.id < a.id or b.userid is null)
group by a.userid
having min(a.id) = c.id
)
order by idSQL>
9 / ID USERID JIN WEI
---------- ---------- ---------- ----------
4 user001 34 34
5 user002 33 35
9 user004 0 0
为何老是停留在第二组数据上,好象不对呀
select * from test1 c
where exists
(select * from test1 a, (select max(id) id,userid from test2 group by userid) b
where a.userid = b.userid(+)
and (b.id < a.id or b.userid is null)
group by a.userid
having min(a.id) = c.id
)
order by idSQL>
10 / ID USERID JIN WEI
---------- ---------- ---------- ----------
7 user001 0 0
8 user002 0 0
from a,(select max(id) id,userid from b group by userid) t
where a.userid=t.userid(+) and a.id>nvl(t.id,0) group by a.userid