select forum.table.* from forum_table,forum_re_table where forum_table.id=forum_re_table.id order by forum_re_table.datetime desc这样行么
楼上这种思路我也想过!但为了执行效率方面我还是选择了sql来实现。
给主题表加上时间表,当人家回复的时候,就update这个时间 然后,显示主题的时候,再实现order by 时间字段 DESC 就OK了 我在开发的论坛就是这样设计的!
select forum.table.* from forum_table where forum_table.id in(select top1 from forum_re_table where forum_table.id=froum_re_table.id) 这样可以不
select forum.table.* from forum_table where forum_table.id in(select top1 from forum_re_table where forum_table.id=froum_re_table.id ordery by update_time) 不好意思上面小写了一点
回复的贴子的re_id肯定是自增的吧?那么选出最大re_id的不就行了吗?有的数据库无法支持子查询的,所以select from (select from )这样的语句有时没法执行!
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('[forum_re_table]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [forum_re_table] CREATE TABLE [forum_re_table]([id] [char](10) NULL, [datatime] [datetime] NULL) INSERT [forum_re_table]([id], [datatime])VALUES ('1', '1991-01-01 00:00:00.000') INSERT [forum_re_table]([id], [datatime])VALUES ('2', '1992-01-01 00:00:00.000') INSERT [forum_re_table]([id], [datatime])VALUES ('3', '1990-01-01 00:00:00.000') IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('[forum_table]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [forum_table] CREATE TABLE [forum_table]([maintitle] [char](10) NULL, [id] [char](10) NULL) INSERT [forum_table]([maintitle], [id]) VALUES ('1', '1') INSERT [forum_table]([maintitle], [id]) VALUES ('2', '2') INSERT [forum_table]([maintitle], [id]) VALUES ('3', '3') SELECT forum_table.* FROM forum_table INNER JOIN forum_re_table ON forum_table.id = forum_re_table.id ORDER BY forum_re_table.datatime DESC DROP table forum_re_table DROP table forum_table /* 2 2 1 1 3 3 */
首先:强烈同意sfply(随风飘零叶)的意见,对于这种拙劣的数据库设计表示遗憾(无可救药了,前两天也有个人提出这样子的垃圾问题)。以下语句在mssql server 2000上测试通过 SELECT forum_table.*, (SELECT TOP 1 update_time FROM forum_re_table r WHERE forum_table.id = r.id ORDER BY update_time DESC) AS update_time FROM forum_table ORDER BY update_time DESC
是的,MYSQL与sql2000不同,mysql 最忌讳的是联合查询,我建议你 select forum.table.* from forum_table,forum_re_table where forum_table.id=forum_re_table.id order by forum_re_table.datetime desc 在phpmyadmin 下试试,对时间的更新,需update
然后,显示主题的时候,再实现order by 时间字段 DESC 就OK了
我在开发的论坛就是这样设计的!
where forum_table.id in(select top1 from forum_re_table where forum_table.id=froum_re_table.id)
这样可以不
where forum_table.id in(select top1 from forum_re_table where forum_table.id=froum_re_table.id ordery by update_time)
不好意思上面小写了一点
DROP TABLE [forum_re_table] CREATE TABLE [forum_re_table]([id] [char](10) NULL,
[datatime] [datetime] NULL)
INSERT [forum_re_table]([id], [datatime])VALUES ('1', '1991-01-01 00:00:00.000')
INSERT [forum_re_table]([id], [datatime])VALUES ('2', '1992-01-01 00:00:00.000')
INSERT [forum_re_table]([id], [datatime])VALUES ('3', '1990-01-01 00:00:00.000')
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('[forum_table]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [forum_table] CREATE TABLE [forum_table]([maintitle] [char](10) NULL,
[id] [char](10) NULL) INSERT [forum_table]([maintitle], [id])
VALUES ('1', '1') INSERT [forum_table]([maintitle], [id])
VALUES ('2', '2') INSERT [forum_table]([maintitle], [id])
VALUES ('3', '3')
SELECT forum_table.*
FROM forum_table INNER JOIN
forum_re_table ON forum_table.id = forum_re_table.id
ORDER BY forum_re_table.datatime DESC
DROP table forum_re_table
DROP table forum_table
/*
2 2
1 1
3 3
*/
SELECT forum_table.*,
(SELECT TOP 1 update_time
FROM forum_re_table r
WHERE forum_table.id = r.id
ORDER BY update_time DESC) AS update_time
FROM forum_table
ORDER BY update_time DESC
select forum.table.* from forum_table,forum_re_table where forum_table.id=forum_re_table.id order by forum_re_table.datetime desc
在phpmyadmin 下试试,对时间的更新,需update