g******3
发帖数: 115 | 1 网上流传一组“精妙SQL语句”，里边有这样一个，
说明：显示文章、提交人和最后回复时间
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate)
adddate from table where table.title=a.title) b
可是我在mysql里执行之后，报以下错误：
1054 - Unknown column 'a.title' in 'where clause'
我觉得如果让我写的话，我会写成这样：
select title,username,max(adddate) as adddate from table group by title
不明白网上流产版为什么要写成那样，是错误的吧，不然为何我执行不能通过 |
j*****n
发帖数: 1781 | 2 your query will cause an error says "username is not in the group by list"..
. |
g******3
发帖数: 115 | 3 没报错，但发现选出来的数据不对。。。
我再看看。。。
mysql> select * from testpost;
+-------+----------+------------+
| title | username | adddate |
+-------+----------+------------+
| post1 | jx | 2009-01-01 |
| post1 | zj | 2009-01-02 |
| post2 | zj | 2009-01-02 |
+-------+----------+------------+
3 rows in set (0.00 sec)
mysql> select title,username,max(adddate) as adddate from testpost group by
titl
e;
+-------+----------+------------+
| title | username | adddate |
+-------+----------+-------
【在 j*****n 的大作中提到】
: your query will cause an error says "username is not in the group by list"..
: .
|
g******3
发帖数: 115 | 4 这个执行结果是对的
mysql> select * from testpost a where a.adddate = (select max(adddate) from
test
post b where a.title = b.title) ;
+-------+----------+------------+
| title | username | adddate |
+-------+----------+------------+
| post1 | zj | 2009-01-02 |
| post2 | zj | 2009-01-02 |
+-------+----------+------------+
2 rows in set (0.00 sec) |
g******3
发帖数: 115 | 5 装了oracle或sql server的xdjms能不能给我验证一下这个sql有没有我mysql下报的错
误，
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate)
adddate from table where table.title=a.title) b
另外请高人指点，我这个sql有什么不足之处，
select * from testpost a where a.adddate = (select max(adddate) from
testpost b where a.title = b.title) |
B*****g
发帖数: 34098 | 6 mysql suck
..
【在 j*****n 的大作中提到】
: your query will cause an error says "username is not in the group by list"..
: .
|
