这句SQL查询怎么写？

edear

数据库里面2张表：
t1表

id userID
1 3
2 3
3 4

t2表

id t1_id timestamp
1 1       1001
2 2       1001
3 1       1002
4 2       1002

我的SQL语句：

1. SELECT a.id as a_id, b.timestamp, b.id AS b_id
   
2. FROM t1 a
   
3. LEFT JOIN t2 b ON ( a.id = b.t1_id )
   
4. WHERE a.userID =3
   
5. GROUP BY a.id
   
6. ORDER BY b.timestamp DESC

复制代码

查询结果：

a_id        timestamp         b_id
1        1001        1
2        1001        2

但是我要的是t2表的第3、4行，ORDER BY b.timestamp DESC 这段不起作用，怎么解决，先谢了？

qiqibian

发到CSDN吧

edear

qiqibian 发表于 2012-2-13 00:19
发到CSDN吧

据说那里会被盗号

edear

--
-- 表的结构 `t1`
--

CREATE TABLE `t1` (
  `id` int(10) NOT NULL auto_increment,
  `userID` int(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- 导出表中的数据 `t1`
--

INSERT INTO `t1` VALUES(1, 3);
INSERT INTO `t1` VALUES(3, 4);
INSERT INTO `t1` VALUES(2, 3);

--
-- 表的结构 `t2`
--
CREATE TABLE `t2` (
  `id` int(10) NOT NULL auto_increment,
  `t1_id` int(10) NOT NULL,
  `timestamp` int(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- 导出表中的数据 `t2`
--

INSERT INTO `t2` VALUES(1, 1, 1001);
INSERT INTO `t2` VALUES(2, 2, 1001);
INSERT INTO `t2` VALUES(3, 1, 1002);
INSERT INTO `t2` VALUES(4, 2, 1002);

0x59

edear 发表于 2012-2-13 00:20
据说那里会被盗号

请多看看SQL手册再来提问

wdlth

1. 
   
2. SELECT a.id as a_id, b.timestamp, b.id AS b_id
   
3. FROM t1 a
   
4. LEFT JOIN t2 b ON ( a.id = b.t1_id AND b.id > 2)
   
5. WHERE a.userID =3
   
6. GROUP BY a.id
   
7. ORDER BY b.timestamp DESC
   

复制代码

或

1. 
   
2. SELECT a.id as a_id, b.timestamp, b.id AS b_id
   
3. FROM t1 a
   
4. LEFT JOIN t2 b ON ( a.id = b.t1_id )
   
5. WHERE a.userID =3 AND b.id > 2
   
6. GROUP BY a.id
   
7. ORDER BY b.timestamp DESC
   

复制代码

很简单，限制b.id的范围

1. 
   
2. 1        1002        3
   
3. 2        1002        4
   

复制代码

qun

技术帝快上