MySQL社区
标题:
三表联合查询,求解(急)
[打印本页]
作者:
lishuming26
时间:
2016-7-22 20:27
标题:
三表联合查询,求解(急)
我想请教个问题:有两个数据库d1和d2,d1里有一个数据表table1,有两个字段,id,name。d2里有两个表:table2和table3,table2有三个字段,rid,ip,time。table3有两个字段rid,time。其中table1.id=table2.rid=table3.rid,三个是一样的,关联的。table2.time和table3.time是不一样的 。我想查询table2数据表中的三个字段,其中限定条件是table2.time>table3.time,同时联合查询出table1里的name,如果哪位大侠知道告诉我下,谢谢
作者:
lishuming26
时间:
2016-7-22 20:48
不知道说清楚没:
select rid,ip,time from d2.table2;
+------------+----------------+-----------------------------+
| rid | ip | time |
+------------+----------------+-----------------------------+
| 4294000000 | 101.201.17.57 | 1469184038 |
+------------+----------------+-----------------------------+
| 4294000001 | 101.201.17.100 | 1469184066 |
+------------+----------------+-----------------------------+
| 4294000002 | 101.201.17.100 | 1469184022 |
+------------+----------------+-----------------------------+
select rid,time from d2.table3;
+------------+-----------------------------+
| rid | time |
+------------+-----------------------------+
| 4294000000 | 1469184044 |
+------------+----------------+------------+
| 4294000000 | 1469184033 |
+------------+----------------+------------+
| 4294000001 | 1469184055 |
+------------+----------------+------------+
| 4294000001 | 1469184088 |
+------------+----------------+------------+
| 4294000002 | 1469184035 |
+------------+----------------+------------+
select id,name from d1.table1;
+------------+-----------------------------+
| id | name |
+------------+-----------------------------+
| 4294000000 | lucy |
+------------+----------------+------------+
| 4294000001 | mary |
+------------+----------------+------------+
我想得到的结果是:table2.time>table3.time,time是时间戳,同时将table1里的name也捎上。
想要的结果是:
+------------+----------------+-----------------------------+----------------
| rid | ip | time | name |
+------------+----------------+-----------------------------+----------------
| 4294000000 | 101.201.17.57 | 1469184038 | lucy |
+------------+----------------+-----------------------------+----------------
| 4294000001 | 101.201.17.100 | 1469184066 | mary |
+------------+----------------+-----------------------------+----------------
作者:
lishuming26
时间:
2016-7-22 21:37
忘了说了,对于账号来说,只取最近的时间,就是加上limit
作者:
lishuming26
时间:
2016-7-23 13:57
select a.role_id,a.user_ip,c.sm_name from tbllog_login a,tbllog_quit b,csdb.tbl_Avatar c where a.role_id = b.role_id=c.id and max(b.login_time) <(select max(happend_time) from tbllog_login);
谁能帮我修正一下这个语句,谢谢
作者:
yflower
时间:
2016-8-1 11:27
select t2.rid,t2.ip,t2.time,t1.name
from d2.table2 t2 inner join d2.table3 on t2.rid=t3.rid
inner join d1.table1 t1 on t2.rid=t1.id
where t2.time>t3.time;
参考自:
mysql join语法
http://www.data.5helpyou.com/article209.html
欢迎光临 MySQL社区 (http://www.mysqlpub.com/)
Powered by Discuz! X3.2