4个表的复杂联合查询，求助！

Question

有4个表，结构分别如下：
atten表结构和数据：
id uid oid
1 1 2
2 1 3
3 1 4
4 2 3
5 2 4
说明：uid为一个用户的ID，oid为他感兴趣的用户ID
user表结构和数据：
id name
1 tom
2 jone
3 lily
说明：该表为用户表，保存用户id和姓名
order表结构和数据：
id uid title time
1 1 abcd 1331554986
2 1 123123 1331555000
3 2 ashousdf 1331554900
4 2 hujhljh 1331560000
说明：该表保存用户所有的订单标题及时间
join表结构和数据：
id uid orderid time
1 2 1 1331554986
2 3 1 1331554900
3 2 2 1331555000
现在要求返回atten表中用户id（uid）为1的用户感兴趣的所有用户的用户信息，包括感兴趣用户最新的订单(order表)以及最新参与的订单(join)【此处需要返回参与的订单的标题】。

请教大侠，这样的SQL要如何写？如果不能用联合查询返回结果，该怎么处理效率高些？膜拜中。。THANKS!

Answer 1

select u.id,u.name,o.title,j.*
from atten a inner join user u on a.oid=u.id
left join (select * from `order` x where not exists (select 1 from `order` where uid=x.uid and time>x.time)) o on u.id=o.uid
left join (select * from `join` y where not exists (select 1 from `join` where uid=y.uid and time>y.time)) j on u.id=j.uid
where a.uid=1

Answer 2

很感谢楼上朋友这么晚还回复！我后面觉得这样联合查询，即使能实现但效率会很低，所以采用增加一个单独表来存放所关注用户的最新动态信息。所以上面的问题就演变成了以下的问题：
有三个表，说明如下：
atten表结构和数据：
id uid oid
1 1 2
2 1 3
3 1 4
4 2 3
5 2 4
说明：uid为一个用户的ID，oid为他感兴趣的用户ID
user表结构和数据：
id name
1 tom
2 jone
3 lily
说明：该表为用户表，保存用户id和姓名
dynamic表结构和数据：
id uid type title time
1 3 1 abcd 1331554986
2 3 2 123123 1331555000
3 2 1 ashousdf 1331554900
4 2 2 hujhljh 1331560000
5 2 1 hhljhl 1331568000
6 2 2 asdfds 1331570000
说明：该表保存用户最新生成订单和参与交易订单的信息，type=1表示是用户最新生成的订单信息，type=2表示是用户最新参与的订单信息。
要求：返回用户关注的其他用户最新生成的订单和最新参与的订单信息。
实现结果如下：（返回用户ID为1的用户所关注的其他用户最新生成和参与订单的信息）
id oid name type title         time
1  2 jone 1 hhljhl 1331568000
1  2 jone 2 asdfds 1331570000
1  3    lily 1 abcd 1331554986
1  3 lily 2 123123 1331555000

Answer 3

ps:真心感谢这个平台的一些高手，能热心的帮助需要的朋友我在其他论坛也发过帖子，基本上是没回应，而这个平台虽然很早就注册了，但最近才发觉他的作用。很感谢ACMAIN_CHM、WWWWA两位大侠！因为我主要做的是开发，SQL语句用的不算多，所以没怎么研究，，，提出的问题太肤浅，望不吝赐教，，谢谢！

Answer 4

select atten.*,user.name,dynamic.title, dynamic.time from atten left join user on atten.oid= user.id  left join dynamic on atten.oid = dynamic.uid;

Answer 5

谢谢楼上朋友的回复。。需要说明的是，这里dynamic返回的是用户最新的那条记录，，比如用户生成的订单有2个，那只返回最新的那一个。。

Answer 6

贴建表及插入记录的SQL，及要求结果出来看看

Answer 7

创建表语句
CREATE TABLE `attention` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `oid` int(11) NOT NULL COMMENT '被关注的会员id，比如joe关注，那么这里记录的user_id',
   `uid` int(11) NOT NULL COMMENT '关注别人的会员id，比如joe关注，那么这里记录joe的user_id',
   PRIMARY KEY (`id`),
 ) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COMMENT='关注'
 
CREATE TABLE `user` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(30) NOT NULL COMMENT '用户名',
 ) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COMMENT='用户基本表'
 
 CREATE TABLE `dynamic` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `uid` int(11) NOT NULL '用户ID',
   `type` tinyint(1) NOT NULL COMMENT '类型 1：发布的订单 2：参与的订单',
   `oid` int(11) NOT NULL COMMENT '订单ID',
   `otitle` varchar(30) NOT NULL COMMENT '订单标题',
   `time` int(11) NOT NULL,
 ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='用户动态表'

Answer 8

插入数据及需要结果说明：
 INSERT INTO user(`id`,`name`) VALUES ( NULL,'tom');
 INSERT INTO user(`id`,`name`) VALUES ( NULL,'jone');
 INSERT INTO user(`id`,`name`) VALUES ( NULL,'lily');
 
 INSERT INTO attention(`id`,`oid`,`uid`) VALUES ( NULL,1,2);
 INSERT INTO attention(`id`,`oid`,`uid`) VALUES ( NULL,1,3);INSERT INTO dynamic(`id`,`uid`,`type`,`oid`,`otitle`,`time`) VALUES ( NULL,2,1,2,'jone第一个发布的订单',1332503480);
INSERT INTO dynamic(`id`,`uid`,`type`,`oid`,`otitle`,`time`) VALUES ( NULL,2,1,3,'jone第二个发布的订单',1332903480);
INSERT INTO dynamic(`id`,`uid`,`type`,`oid`,`otitle`,`time`) VALUES ( NULL,2,2,4,'jone第一个参与的订单',1332503480);
INSERT INTO dynamic(`id`,`uid`,`type`,`oid`,`otitle`,`time`) VALUES ( NULL,2,2,5,'jone第二个参与的订单',1332903480);
INSERT INTO dynamic(`id`,`uid`,`type`,`oid`,`otitle`,`time`) VALUES ( NULL,3,1,6,'lily第一个发布的订单',1332803480);
INSERT INTO dynamic(`id`,`uid`,`type`,`oid`,`otitle`,`time`) VALUES ( NULL,3,2,7,'lily第一个参与的订单',1332803480);需要的结果：（ID为1的用户所关注用户的最新动态信息）
uid to_user_id(被关注用户)  to_user_name to_user_order_id to_user_order_title to_user_join_id to_user_order_title
1   2 jone 3   第二个发布的订单 4  第二个参与的订单
1   3 lily 6   第一个发布的订单 7  第一个参与的订单

Answer 9

CREATE TABLE `attention` (
   `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   `oid` int(11) NOT NULL COMMENT '被关注的会员id，比如joe关注，那么这里记录的user_id',
   `uid` int(11) NOT NULL COMMENT '关注别人的会员id，比如joe关注，那么这里记录joe的user_id' 
  ) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COMMENT='关注';
  
CREATE TABLE `user` (
   `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   `name` varchar(30) NOT NULL COMMENT '用户名'
  ) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COMMENT='用户基本表';
  
 CREATE TABLE `dynamic` (
   `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   `uid` int(11) NOT NULL COMMENT '用户ID',
   `type` tinyint(1) NOT NULL COMMENT '类型 1：发布的订单 2：参与的订单',
   `oid` int(11) NOT NULL COMMENT '订单ID',
   `otitle` varchar(30) NOT NULL COMMENT '订单标题',
   `time` int(11) NOT NULL
  ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='用户动态表'

Answer 10

INSERT INTO attention(`id`,`oid`,`uid`) VALUES ( NULL,1,2);
  INSERT INTO attention(`id`,`oid`,`uid`) VALUES ( NULL,1,3);
这的uid和oid是不是反了？？

Answer 11

select user.id as uid ,
attention.uid as to_user_id,
user.name as to_user_name ,
dynamic.oid as to_user_order_id ,
dynamic.otitle as to_user_order_title,
dynamic.type as type,
dynamic.time as time
from user 
left join attention
on user.id=attention.oid
left join dynamic
on attention.uid=dynamic.uid
where user.id = 1 
group by attention.uid,dynamic.type

Answer 12

汗不好意思，，真实表字段太多，所以只选取要用到的字段我重新整理了下，下面的建表语句跟插入语句都是没有问题的。再次抱歉与感谢！
建表语句：
CREATE TABLE `attention` (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `oid` INT(11) NOT NULL COMMENT '被关注的会员id，比如joe关注，那么这里记录的user_id',
   `uid` INT(11) NOT NULL COMMENT '关注别人的会员id，比如joe关注，那么这里记录joe的user_id',
   PRIMARY KEY (`id`)
 ) ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT='关注';
 
CREATE TABLE `user` (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(30) NOT NULL COMMENT '用户名',
   PRIMARY KEY (`id`)
 ) ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT='用户基本表';
 
CREATE TABLE `dynamic` (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `uid` INT(11) NOT NULL COMMENT '用户ID',
   `type` TINYINT(1) NOT NULL COMMENT '类型 1：发布的订单 2：参与的订单',
   `order_id` INT(11) NOT NULL COMMENT '订单ID',
   `order_title` VARCHAR(30) NOT NULL COMMENT '订单标题',
   `time` INT(11) NOT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT='用户动态表';
 
插入数据： 
INSERT INTO user(`id`,`name`) VALUES ( NULL,'tom');
INSERT INTO user(`id`,`name`) VALUES ( NULL,'jone');
INSERT INTO user(`id`,`name`) VALUES ( NULL,'lily');
 
INSERT INTO attention(`id`,`oid`,`uid`) VALUES ( NULL,2,1);
INSERT INTO attention(`id`,`oid`,`uid`) VALUES ( NULL,2,1);INSERT INTO DYNAMIC(`id`,`uid`,`type`,`order_id`,`order_title`,`time`) VALUES ( NULL,2,1,2,'jone第一个发布的订单',1332503480);
INSERT INTO DYNAMIC(`id`,`uid`,`type`,`order_id`,`order_title`,`time`) VALUES ( NULL,2,1,3,'jone第二个发布的订单',1332903480);
INSERT INTO DYNAMIC(`id`,`uid`,`type`,`order_id`,`order_title`,`time`) VALUES ( NULL,2,2,4,'jone第一个参与的订单',1332503480);
INSERT INTO DYNAMIC(`id`,`uid`,`type`,`order_id`,`order_title`,`time`) VALUES ( NULL,2,2,5,'jone第二个参与的订单',1332903480);
INSERT INTO DYNAMIC(`id`,`uid`,`type`,`order_id`,`order_title`,`time`) VALUES ( NULL,3,1,6,'lily第一个发布的订单',1332803480);
INSERT INTO DYNAMIC(`id`,`uid`,`type`,`order_id`,`order_title`,`time`) VALUES ( NULL,3,2,7,'lily第一个参与的订单',1332803480);需要的结果：
（ID为1的用户所关注用户的最新动态信息）
uid to_user_id(被关注用户)  to_user_name  to_user_order_id  to_user_order_title to_user_join_id  to_user_order_title
1 2 jone 3 第二个发布的订单   4 第二个参与的订单
1 3 lily 6 第一个发布的订单   7 第一个参与的订单

Answer 13

谢谢这位朋友。。这个语句得不到正确结果，，
我要先从attention表中获取到他感兴趣的其他用户的ID，然后根据这些ID获取user表中的用户名以及dynamic表中这些用户的动态信息（包括最新发布的订单和最新参与的订单信息）。

Answer 14

SELECT * FROM `user` a1 INNER JOIN (
SELECT uid,MAX(IF(`type`=1,a.`order_title`,'')),MAX(IF(`type`=1,a.`order_id`,'')),
MAX(IF(`type`=2,a.`order_title`,'')),MAX(IF(`type`=2,a.`order_id`,''))
 FROM  `DYNAMIC` a WHERE NOT EXISTS(SELECT 1 FROM `DYNAMIC` WHERE a.`uid`=`uid`
AND a.`type`=`type` AND a.`time`<`time`)
GROUP BY a.`uid`) b1 ON a1.`id`=b1.uid

Answer 15

非常感谢wwwwb,已经测试可以获取到正确的结果！
不知道有没有其他的方案呢？或者这样的需求有没其他优化的方式？

Answer 16

17楼的大侠还是只实现了user表跟dynamic两个表之间的查询，还需要通过attention表来获取用户信息，然后根据这个用户查找dynamic表中的数据啊感谢中！

Answer 17

select 
user.id,
user.name,
dynamic.order_id,
dynamic.order_title,
dynamic.type,
max(dynamic.time),
joinc.orderid,
max(joinc.time)
from user
left join dynamic
on user.id=dynamic.uid
left join joinc
on user.id=joinc.uid
where user.id in (select oid from attention where attention.uid=1)
group by user.id;

Answer 18

CREATE TABLE `joinc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `orderid` int(11) NOT NULL,
  `time` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;/*Data for the table `joinc` */insert  into `joinc`(`id`,`uid`,`orderid`,`time`) values (1,2,1,1331554986),(2,3,1,1331554900),(3,2,2,1331555000);join不能用，所以改成joinc了