select cl.id,classname,ContentName,ContentInfo,[DateTime],sumid from contents c ,class cl,(select contentid ,count(*)as sumid from comments group by contentid) co where c.classid=c.ID and cl.id=co.contentid order by [DateTime]
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('class') IS NOT NULL DROP TABLE class GO CREATE TABLE class(ID int,classname varchar(10)) go insert into class select 1 , '生活' union all select 2 , '旅行' union all select 3 , '程序' goIF OBJECT_ID('contents') IS NOT NULL DROP TABLE contents GO CREATE TABLE contents(ID int,classid int,ContentName varchar(40),ContentInfo varchar(40),[DateTime] datetime) go insert into contents select 1 , 2 , '测试1' , '测试1测试1测试1' , '2009-7-2 15:32:00' union all select 2 , 1 , '测试2' , '测试2测试2测试2' , '2009-7-12 12:32:00' go IF OBJECT_ID('Comments') IS NOT NULL DROP TABLE Comments GO CREATE TABLE Comments(ID int,contentid int,ComAuthor varchar(40),ComName varchar(40),ComInfo varchar(40),[DateTime] datetime) go insert into Comments select 1 , 1 , '张三', '木木木', '测试' , '2009-7-2 16:32:00' union all select 2 , 2 , '张二', '娺双双', '测试' , '2009-7-12 16:32:00' union all select 3 , 1 , '张四', '在又又', '测试', '2009-7-3 16:32:00' go select cl.id,classname,ContentName,ContentInfo,[DateTime],sumid from contents c ,class cl,(select contentid ,count(*)as sumid from comments group by contentid) co where c.classid=cl.ID and c.id=co.contentid order by [DateTime] desc /*------------ id classname ContentName ContentInfo DateTime sumid ----------- ---------- ---------------------------------------- ---------------------------------------- ----------------------- ----------- 1 生活 测试2 测试2测试2测试2 2009-07-12 12:32:00.000 1 2 旅行 测试1 测试1测试1测试1 2009-07-02 15:32:00.000 2(2 行受影响)-------*/
呵呵谢谢你的提示,我弄出来了select c.ID, c.classid,cl.ClassName,c.ContentName,c.ContentInfo,[c.DateTime],(select count(*) from Comments where Comments.ContentID = c.ID) as comCount from [Contents] as c,class as cl where c.classid = cl.ID order by [c.DateTime] DESC
from contents c ,class cl,(select contentid ,count(*)as sumid from comments group by contentid) co
where c.classid=c.ID and cl.id=co.contentid
order by [DateTime]
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('class') IS NOT NULL
DROP TABLE class
GO
CREATE TABLE class(ID int,classname varchar(10))
go
insert into class
select
1 , '生活' union all select
2 , '旅行' union all select
3 , '程序'
goIF OBJECT_ID('contents') IS NOT NULL
DROP TABLE contents
GO
CREATE TABLE contents(ID int,classid int,ContentName varchar(40),ContentInfo varchar(40),[DateTime] datetime)
go
insert into contents
select
1 , 2 , '测试1' , '测试1测试1测试1' , '2009-7-2 15:32:00' union all select
2 , 1 , '测试2' , '测试2测试2测试2' , '2009-7-12 12:32:00'
go
IF OBJECT_ID('Comments') IS NOT NULL
DROP TABLE Comments
GO
CREATE TABLE Comments(ID int,contentid int,ComAuthor varchar(40),ComName varchar(40),ComInfo varchar(40),[DateTime] datetime)
go
insert into Comments
select
1 , 1 , '张三', '木木木', '测试' , '2009-7-2 16:32:00' union all select
2 , 2 , '张二', '娺双双', '测试' , '2009-7-12 16:32:00' union all select
3 , 1 , '张四', '在又又', '测试', '2009-7-3 16:32:00'
go
select cl.id,classname,ContentName,ContentInfo,[DateTime],sumid
from contents c ,class cl,(select contentid ,count(*)as sumid from comments group by contentid) co
where c.classid=cl.ID and c.id=co.contentid
order by [DateTime] desc
/*------------
id classname ContentName ContentInfo DateTime sumid
----------- ---------- ---------------------------------------- ---------------------------------------- ----------------------- -----------
1 生活 测试2 测试2测试2测试2 2009-07-12 12:32:00.000 1
2 旅行 测试1 测试1测试1测试1 2009-07-02 15:32:00.000 2(2 行受影响)-------*/
from [Contents] as c,class as cl
where c.classid = cl.ID
order by [c.DateTime] DESC