谢谢你的回复,可能是我没说清楚,你看下这段SQL(是错的,但是想要这个意思)select * from table order by `time` desc group by `a` order by `b` desc;即:通过 a 进行分组后得到的相应记录是最后发布的那条记录,并对分出的组按 b 降序排列。
你分组就不能 select * 啊,所以问题不是在 order by ,而是对group by的使用有问题
TO cqq(脑电波) 不好意思啊,我太懒了,所以用了个*代替字段,你可以假设字段
通过 a 进行分组后得到的相应记录是最后发布的那条记录,并对分出的组按 b 降序排列。---------------------------------------------------------what data do you want? the recent records or the ordered groups? if you want both,use two sentences otherwise: select a,max(time) from table group by `a` //get the recent records group by a select a,b,time from table group by `a` order by `b` desc //get the ordered groups
举个例子: table:ID A B C time 1 a b c 01:00:00 2 e f g 02:00:00 3 h i j 03:00:00 4 k l c 04:00:00我使用select ID,C,time from table group by C 之后将会显示:ID C time 1 c 01:00:00 2 g 02:00:00 3 j 03:00:00请注意ID为1的那条记录,他的时间是 01:00:00,但是我想要最近的那条记录,也就是表中ID为 4 的那条记录,采用这种方法如何才能显示呢?
select ID,C,time from table group by C ----------------------------------------- change to:select ID,C,max(time) from table group by C
使用MAX没反应啊,看这句: $result=mysql_query("select username,title,oldprice,price,p_price,loginname,max(thetime) from information where p_type='$p_type' group by loginname order by p_price desc");和不使用MAX的查询结果是一样的,没有实现我想要的效果啊? thetime使用的是DATETIME类型,存入格式是:2006-06-01 00:00:00,是不是问题出在这里?
oracle用不起啊 到现在为止还没见到哪个空间提供这个数据库的另把问题在顶下: 使用MAX没反应啊,看这句: $result=mysql_query("select username,title,oldprice,price,p_price,loginname,max(thetime) from information where p_type='$p_type' group by loginname order by p_price desc");和不使用MAX的查询结果是一样的,没有实现我想要的效果啊? thetime使用的是DATETIME类型,存入格式是:2006-06-01 00:00:00,是不是问题出在这里?
CREATE TABLE `m` ( `id` int(11) NOT NULL auto_increment, `a` char(1) NOT NULL default '', `b` char(1) NOT NULL default '', `c` char(1) NOT NULL default '', `tt` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;-- -- Dumping data for table `m` -- INSERT INTO `m` VALUES (1, 'a', 'b', 'c', '2006-06-03 01:00:00'); INSERT INTO `m` VALUES (2, 'e', 'f', 'g', '2006-06-03 02:00:00'); INSERT INTO `m` VALUES (3, 'h', 'i', 'j', '2006-06-03 03:00:00'); INSERT INTO `m` VALUES (4, 'k', 'l', 'c', '2006-06-03 04:00:00'); select * from m where (c, tt) in (select c,max(tt) from m group by c); id a b c tt 2 e f g 2006-06-03 02:00:00 3 h i j 2006-06-03 03:00:00 4 k l c 2006-06-03 04:00:00
刚才说错了,我现在已经解决了问题,SQL代码如下: select username,title,oldprice,price,p_price from information where (loginname,thetime) in (select loginname,max(thetime) from information where p_type='$p_type' group by loginname )order by p_price desc;我现在可以实现我想要的效果了,但是我看不懂这句SQL,哪位能解释下吗???
不好意思啊,我太懒了,所以用了个*代替字段,你可以假设字段
if you want both,use two sentences
otherwise:
select a,max(time) from table group by `a` //get the recent records group by a
select a,b,time from table group by `a` order by `b` desc //get the ordered groups
table:ID A B C time
1 a b c 01:00:00
2 e f g 02:00:00
3 h i j 03:00:00
4 k l c 04:00:00我使用select ID,C,time from table group by C 之后将会显示:ID C time
1 c 01:00:00
2 g 02:00:00
3 j 03:00:00请注意ID为1的那条记录,他的时间是 01:00:00,但是我想要最近的那条记录,也就是表中ID为 4 的那条记录,采用这种方法如何才能显示呢?
change to:select ID,C,max(time) from table group by C
$result=mysql_query("select username,title,oldprice,price,p_price,loginname,max(thetime) from information where p_type='$p_type' group by loginname order by p_price desc");和不使用MAX的查询结果是一样的,没有实现我想要的效果啊?
thetime使用的是DATETIME类型,存入格式是:2006-06-01 00:00:00,是不是问题出在这里?
到现在为止还没见到哪个空间提供这个数据库的另把问题在顶下:
使用MAX没反应啊,看这句:
$result=mysql_query("select username,title,oldprice,price,p_price,loginname,max(thetime) from information where p_type='$p_type' group by loginname order by p_price desc");和不使用MAX的查询结果是一样的,没有实现我想要的效果啊?
thetime使用的是DATETIME类型,存入格式是:2006-06-01 00:00:00,是不是问题出在这里?
CREATE TABLE `m` (
`id` int(11) NOT NULL auto_increment,
`a` char(1) NOT NULL default '',
`b` char(1) NOT NULL default '',
`c` char(1) NOT NULL default '',
`tt` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;--
-- Dumping data for table `m`
-- INSERT INTO `m` VALUES (1, 'a', 'b', 'c', '2006-06-03 01:00:00');
INSERT INTO `m` VALUES (2, 'e', 'f', 'g', '2006-06-03 02:00:00');
INSERT INTO `m` VALUES (3, 'h', 'i', 'j', '2006-06-03 03:00:00');
INSERT INTO `m` VALUES (4, 'k', 'l', 'c', '2006-06-03 04:00:00');
select * from m where (c, tt) in
(select c,max(tt) from m group by c); id a b c tt
2 e f g 2006-06-03 02:00:00
3 h i j 2006-06-03 03:00:00
4 k l c 2006-06-03 04:00:00
select username,title,oldprice,price,p_price from information where (loginname,thetime) in (select loginname,max(thetime) from information where p_type='$p_type' group by loginname )order by p_price desc;我现在可以实现我想要的效果了,但是我看不懂这句SQL,哪位能解释下吗???