问题1: 按回帖时间最新的帖子排序....这个SQL该怎么写? ----------------------------------------- select a.* from 帖子表 a left join 回复表 b on a.帖子ID = b.所属那篇帖子ID group by a. --字段列表 order by max(b.回帖时间) desc
问题2: 按回帖数最多的帖子排序...这个SQL该怎么写? ----------------------------------------- select a.* from 帖子表 a left join 回复表 b on a.帖子ID = b.所属那篇帖子ID group by a. --字段列表 order by count(b.回帖ID) desc
select a.帖子ID,a.帖子标题 from 帖子表 a left join 回复表 b group by a.帖子ID ,a.帖子标题 order by max(回帖时间) desc
问题2: 按回帖数最多的帖子排序...这个SQL该怎么写?select a.帖子ID,a.帖子标题 from 帖子表 a left join 回复表 b group by a.帖子ID ,a.帖子标题 order by count(*) desc
1、select * from 回复表 A inner join 帖子表 A on A.帖子ID=B.帖子ID order by order by A.回帖时间 desc2、select 帖子标题,count(*) from 回复表 A inner join 帖子表 A on A.帖子ID=B.帖子ID group by B.所属那篇帖子ID order by count(*) desc
--建立测试环境 Create table 帖子表 (帖子ID Int IDENTITY(1,1), 帖子标题 Nvarchar(50) )Create table 回复表 (回帖ID Int IDENTITY(1,1), 回帖标题 Nvarchar(50), 所属那篇帖子ID Int, 回帖时间 DateTime ) GO --插入数据 Insert 帖子表 Values(N'AAAAAAAA') Insert 帖子表 Values(N'BBBBBBBB')Insert 回复表 Values(N'CCCCCCCC', 2, '2005-4-4') Insert 回复表 Values(N'DDDDDDDD', 2, '2005-4-6') Insert 回复表 Values(N'EEEEEEEE', 2, '2005-4-8') Insert 回复表 Values(N'FFFFFFFF', 1, '2005-4-2') Insert 回复表 Values(N'GGGGGGGG', 1, '2005-4-1') GO --测试 Select A.* from 帖子表 A Inner Join (Select 所属那篇帖子ID,Max(回帖时间) As 回帖时间 from 回复表 Group By 所属那篇帖子ID) B On A.帖子ID=B.所属那篇帖子ID Order By B.回帖时间 Desc Select A.* from 帖子表 A Inner Join (Select 所属那篇帖子ID,Count(回帖ID) As 回帖数 from 回复表 Group By 所属那篇帖子ID) B On A.帖子ID=B.所属那篇帖子ID Order By B.回帖数 Desc--删除测试环境 Drop table 帖子表 Drop table 回复表 --结果 /* 帖子ID 帖子标题 2 BBBBBBBB 1 AAAAAAAA帖子ID 帖子标题 2 BBBBBBBB 1 AAAAAAAA*/
select 贴子表 .* ,回复表.* from 贴子表 join cross 回复表 on 贴子表 .id= ,回复表.id order by 回复表.回贴时间select 贴子表.*,回复表.* FROM
select 贴子表 .* ,回复表.* from 贴子表 join cross 回复表 on 贴子表 .id= ,回复表.id order by 回复表.回贴时间select 贴子表.*,回复表.*,count(*)sa回复总数 from 贴子表 join cross 回复表 on 贴子表 .id= ,回复表.id group by 贴子表 .id order by 回复总数 不好意思网吧机器不好用,第二个查询语句中的第一行count(*) as 回复总数应为count(贴子表.id) as 回复总数
--准备测试数据 Create table main ( id int primary key, title varchar(100) not null ) Go Create table reply ( id int primary key, retitle varchar(100) not null, m_id int not null FOREIGN KEY REFERENCES main(id) on UPDATE CASCADE on delete cascade, retime datetime not null ) Go insert into main select 1,'AAAAAAAA' union select 2,'BBBBBBBB' Go insert into reply select 1,'CCCCCCCC',2,'2005-4-4' union select 2,'DDDDDDDD',2,'2005-4-6' union select 3,'EEEEEEEE',2,'2005-4-8' union select 4,'FFFFFFFF',1,'2005-4-2' union select 5,'GGGGGGGG',1,'2005-4-1' Go--测试SQL --按时间倒序 SELECT c.id, c.title, MAX(B.retime) lastreplytime FROM main A INNER JOIN reply B ON A.id = B.m_id INNER JOIN main c ON A.id = c.id GROUP BY A.id, c.id, c.title order by MAX(B.retime) desc--按回帖数倒序 SELECT c.id, c.title, count(B.id) replycount FROM main A INNER JOIN reply B ON A.id = B.m_id INNER JOIN main c ON A.id = c.id GROUP BY A.id, c.id, c.title order by count(B.id) desc --删除表 drop table reply,main
id title lastreplytime ----------- ---------- ------------------------------------------------------ 2 BBBBBBBB 2005-04-08 00:00:00.000 1 AAAAAAAA 2005-04-02 00:00:00.000 id title replycount ----------- ---------- ----------- 2 BBBBBBBB 3 1 AAAAAAAA 2如果你理解了原理,那么再加个论坛表来查询也是一样的
麻烦给写条SQL语句出来好吗? 小弟我比较笨,一时还理解不了#24
--建立测试环境 Create table 论坛表 (论坛ID Int IDENTITY(1,1), 论坛名称 Nvarchar(50) )Create table 帖子表 (帖子ID Int IDENTITY(1,1), 帖子标题 Nvarchar(50), 所属那个论坛ID Int )Create table 回复表 (回帖ID Int IDENTITY(1,1), 回帖标题 Nvarchar(50), 所属那篇帖子ID Int, 回帖时间 DateTime ) GO --插入数据 Insert 论坛表 Values(N'PPP') Insert 论坛表 Values(N'VVV')Insert 帖子表 Values(N'AAAAAAAA',2) Insert 帖子表 Values(N'BBBBBBBB',1)Insert 回复表 Values(N'CCCCCCCC', 2, '2005-4-4') Insert 回复表 Values(N'DDDDDDDD', 2, '2005-4-6') Insert 回复表 Values(N'EEEEEEEE', 2, '2005-4-8') Insert 回复表 Values(N'FFFFFFFF', 1, '2005-4-2') Insert 回复表 Values(N'GGGGGGGG', 1, '2005-4-1') GO --测试 Select A.*,B.帖子ID,B.帖子标题 from 论坛表 A Inner Join 帖子表 B On A.论坛ID = B.所属那个论坛ID Inner Join (Select 所属那篇帖子ID,Max(回帖时间) As 回帖时间 from 回复表 Group By 所属那篇帖子ID) C On B.帖子ID=C.所属那篇帖子ID Order By C.回帖时间 Desc Select A.*,B.帖子ID,B.帖子标题 from 论坛表 A Inner Join 帖子表 B On A.论坛ID = B.所属那个论坛ID Inner Join (Select 所属那篇帖子ID,Count(回帖ID) As 回帖数 from 回复表 Group By 所属那篇帖子ID) C On B.帖子ID=C.所属那篇帖子ID Order By C.回帖数 Desc--删除测试环境 Drop table 论坛表 Drop table 帖子表 Drop table 回复表 --结果 /* 论坛ID 论坛名称 帖子ID 帖子标题 1 PPP 2 BBBBBBBB 2 VVV 1 AAAAAAAA论坛ID 论坛名称 帖子ID 帖子标题 1 PPP 2 BBBBBBBB 2 VVV 1 AAAAAAAA */
两条SQL分别是Select A.*,B.帖子ID,B.帖子标题 from 论坛表 A Inner Join 帖子表 B On A.论坛ID = B.所属那个论坛ID Inner Join (Select 所属那篇帖子ID,Max(回帖时间) As 回帖时间 from 回复表 Group By 所属那篇帖子ID) C On B.帖子ID=C.所属那篇帖子ID Order By C.回帖时间 Desc Select A.*,B.帖子ID,B.帖子标题 from 论坛表 A Inner Join 帖子表 B On A.论坛ID = B.所属那个论坛ID Inner Join (Select 所属那篇帖子ID,Count(回帖ID) As 回帖数 from 回复表 Group By 所属那篇帖子ID) C On B.帖子ID=C.所属那篇帖子ID Order By C.回帖数 Desc
同意一天到晚游泳的鱼Select A.*,B.帖子ID,B.帖子标题 from 论坛表 A Inner Join 帖子表 B On A.论坛ID = B.所属那个论坛ID Inner Join (Select 所属那篇帖子ID,Max(回帖时间) As 回帖时间 from 回复表 Group By 所属那篇帖子ID) C On B.帖子ID=C.所属那篇帖子ID Order By C.回帖时间 Desc Select A.*,B.帖子ID,B.帖子标题 from 论坛表 A Inner Join 帖子表 B On A.论坛ID = B.所属那个论坛ID Inner Join (Select 所属那篇帖子ID,Count(回帖ID) As 回帖数 from 回复表 Group By 所属那篇帖子ID) C On B.帖子ID=C.所属那篇帖子ID Order By C.回帖数 Desc
就是要回帖时间最新的那篇主贴的内容..能把SQL写出来吗?
按回帖时间最新的帖子排序....这个SQL该怎么写?
-----------------------------------------
select
a.*
from
帖子表 a
left join
回复表 b
on
a.帖子ID = b.所属那篇帖子ID
group by
a. --字段列表
order by
max(b.回帖时间) desc
问题2:
按回帖数最多的帖子排序...这个SQL该怎么写?
-----------------------------------------
select
a.*
from
帖子表 a
left join
回复表 b
on
a.帖子ID = b.所属那篇帖子ID
group by
a. --字段列表
order by
count(b.回帖ID) desc
from 帖子表 a
left join 回复表 b
group by a.帖子ID ,a.帖子标题
order by max(回帖时间) desc
问题2:
按回帖数最多的帖子排序...这个SQL该怎么写?select a.帖子ID,a.帖子标题
from 帖子表 a
left join 回复表 b
group by a.帖子ID ,a.帖子标题
order by count(*) desc
order by order by A.回帖时间 desc2、select 帖子标题,count(*) from 回复表 A inner join 帖子表 A on A.帖子ID=B.帖子ID
group by B.所属那篇帖子ID order by count(*) desc
--建立测试环境
Create table 帖子表
(帖子ID Int IDENTITY(1,1),
帖子标题 Nvarchar(50)
)Create table 回复表
(回帖ID Int IDENTITY(1,1),
回帖标题 Nvarchar(50),
所属那篇帖子ID Int,
回帖时间 DateTime
)
GO
--插入数据
Insert 帖子表 Values(N'AAAAAAAA')
Insert 帖子表 Values(N'BBBBBBBB')Insert 回复表 Values(N'CCCCCCCC', 2, '2005-4-4')
Insert 回复表 Values(N'DDDDDDDD', 2, '2005-4-6')
Insert 回复表 Values(N'EEEEEEEE', 2, '2005-4-8')
Insert 回复表 Values(N'FFFFFFFF', 1, '2005-4-2')
Insert 回复表 Values(N'GGGGGGGG', 1, '2005-4-1')
GO
--测试
Select A.* from 帖子表 A Inner Join (Select 所属那篇帖子ID,Max(回帖时间) As 回帖时间 from 回复表 Group By 所属那篇帖子ID) B On A.帖子ID=B.所属那篇帖子ID Order By B.回帖时间 Desc
Select A.* from 帖子表 A Inner Join (Select 所属那篇帖子ID,Count(回帖ID) As 回帖数 from 回复表 Group By 所属那篇帖子ID) B On A.帖子ID=B.所属那篇帖子ID Order By B.回帖数 Desc--删除测试环境
Drop table 帖子表
Drop table 回复表
--结果
/*
帖子ID 帖子标题
2 BBBBBBBB
1 AAAAAAAA帖子ID 帖子标题
2 BBBBBBBB
1 AAAAAAAA*/
from 贴子表 join cross 回复表 on 贴子表 .id= ,回复表.id
order by 回复表.回贴时间select 贴子表.*,回复表.*
FROM
from 贴子表 join cross 回复表 on 贴子表 .id= ,回复表.id
order by 回复表.回贴时间select 贴子表.*,回复表.*,count(*)sa回复总数
from 贴子表 join cross 回复表 on 贴子表 .id= ,回复表.id
group by 贴子表 .id
order by 回复总数
不好意思网吧机器不好用,第二个查询语句中的第一行count(*) as 回复总数应为count(贴子表.id) as 回复总数
是这样的,我想求属于这个论坛下的所有帖子的排序?????论坛表
---------------------------
论坛ID 论坛名称 1 PPP
2 VVV
------------------------------帖子表
---------------------------
帖子ID 帖子标题 所属那个论坛ID
1 AAAAAAAA 2
2 BBBBBBBB 1
----------------------------回复表
---------------------------
回帖ID 回帖标题 所属那篇帖子ID 回帖时间
1 CCCCCCCC 2 2005-4-4
2 DDDDDDDD 2 2005-4-6
3 EEEEEEEE 2 2005-4-8
4 FFFFFFFF 1 2005-4-2
5 GGGGGGGG 1 2005-4-1-------------------------------------------------
问题1:
按回帖时间最新的帖子排序....这个SQL该怎么写?问题2:
按回帖数最多的帖子排序...这个SQL该怎么写?
Create table main (
id int primary key,
title varchar(100) not null
)
Go
Create table reply (
id int primary key,
retitle varchar(100) not null,
m_id int not null FOREIGN KEY REFERENCES main(id) on UPDATE CASCADE on delete cascade,
retime datetime not null
)
Go
insert into main
select 1,'AAAAAAAA' union
select 2,'BBBBBBBB'
Go
insert into reply
select 1,'CCCCCCCC',2,'2005-4-4' union
select 2,'DDDDDDDD',2,'2005-4-6' union
select 3,'EEEEEEEE',2,'2005-4-8' union
select 4,'FFFFFFFF',1,'2005-4-2' union
select 5,'GGGGGGGG',1,'2005-4-1'
Go--测试SQL
--按时间倒序
SELECT c.id, c.title, MAX(B.retime) lastreplytime
FROM main A INNER JOIN
reply B ON A.id = B.m_id INNER JOIN
main c ON A.id = c.id
GROUP BY A.id, c.id, c.title order by MAX(B.retime) desc--按回帖数倒序
SELECT c.id, c.title, count(B.id) replycount
FROM main A INNER JOIN
reply B ON A.id = B.m_id INNER JOIN
main c ON A.id = c.id
GROUP BY A.id, c.id, c.title order by count(B.id) desc
--删除表
drop table reply,main
----------- ---------- ------------------------------------------------------
2 BBBBBBBB 2005-04-08 00:00:00.000
1 AAAAAAAA 2005-04-02 00:00:00.000
id title replycount
----------- ---------- -----------
2 BBBBBBBB 3
1 AAAAAAAA 2如果你理解了原理,那么再加个论坛表来查询也是一样的
小弟我比较笨,一时还理解不了#24
Create table 论坛表
(论坛ID Int IDENTITY(1,1),
论坛名称 Nvarchar(50)
)Create table 帖子表
(帖子ID Int IDENTITY(1,1),
帖子标题 Nvarchar(50),
所属那个论坛ID Int
)Create table 回复表
(回帖ID Int IDENTITY(1,1),
回帖标题 Nvarchar(50),
所属那篇帖子ID Int,
回帖时间 DateTime
)
GO
--插入数据
Insert 论坛表 Values(N'PPP')
Insert 论坛表 Values(N'VVV')Insert 帖子表 Values(N'AAAAAAAA',2)
Insert 帖子表 Values(N'BBBBBBBB',1)Insert 回复表 Values(N'CCCCCCCC', 2, '2005-4-4')
Insert 回复表 Values(N'DDDDDDDD', 2, '2005-4-6')
Insert 回复表 Values(N'EEEEEEEE', 2, '2005-4-8')
Insert 回复表 Values(N'FFFFFFFF', 1, '2005-4-2')
Insert 回复表 Values(N'GGGGGGGG', 1, '2005-4-1')
GO
--测试
Select A.*,B.帖子ID,B.帖子标题 from 论坛表 A Inner Join 帖子表 B On A.论坛ID = B.所属那个论坛ID Inner Join (Select 所属那篇帖子ID,Max(回帖时间) As 回帖时间 from 回复表 Group By 所属那篇帖子ID) C On B.帖子ID=C.所属那篇帖子ID Order By C.回帖时间 Desc
Select A.*,B.帖子ID,B.帖子标题 from 论坛表 A Inner Join 帖子表 B On A.论坛ID = B.所属那个论坛ID Inner Join (Select 所属那篇帖子ID,Count(回帖ID) As 回帖数 from 回复表 Group By 所属那篇帖子ID) C On B.帖子ID=C.所属那篇帖子ID Order By C.回帖数 Desc--删除测试环境
Drop table 论坛表
Drop table 帖子表
Drop table 回复表
--结果
/*
论坛ID 论坛名称 帖子ID 帖子标题
1 PPP 2 BBBBBBBB
2 VVV 1 AAAAAAAA论坛ID 论坛名称 帖子ID 帖子标题
1 PPP 2 BBBBBBBB
2 VVV 1 AAAAAAAA
*/
Select A.*,B.帖子ID,B.帖子标题 from 论坛表 A Inner Join 帖子表 B On A.论坛ID = B.所属那个论坛ID Inner Join (Select 所属那篇帖子ID,Count(回帖ID) As 回帖数 from 回复表 Group By 所属那篇帖子ID) C On B.帖子ID=C.所属那篇帖子ID Order By C.回帖数 Desc
Select A.*,B.帖子ID,B.帖子标题 from 论坛表 A Inner Join 帖子表 B On A.论坛ID = B.所属那个论坛ID Inner Join (Select 所属那篇帖子ID,Count(回帖ID) As 回帖数 from 回复表 Group By 所属那篇帖子ID) C On B.帖子ID=C.所属那篇帖子ID Order By C.回帖数 Desc