sql - How to make the latest article always go to the top in noticeboard
问题描述
I made a Answered bulletin board like this
id parent_id name depth_fullname
1 0 1depth 1 1depth 1
14 1 2depth 1-2 1depth 1 > 2depth 1-4 *Latest
4 1 2depth 1-1 1depth 1 > 2depth 1-1
10 4 3depth 1-1 1depth 1 > 2depth 1-1 > 3depth 1-1
11 4 3depth 1-2 1depth 1 > 2depth 1-1 > 3depth 1-2
12 4 3depth 1-3 1depth 1 > 2depth 1-1 > 3depth 1-3
5 1 2depth 1-2 1depth 1 > 2depth 1-2
6 1 2depth 1-3 1depth 1 > 2depth 1-3
2 0 1depth 2 1depth 2
7 2 2depth 2-1 1depth 2 > 2depth 2-1
8 2 2depth 2-2 1depth 2 > 2depth 2-2
9 2 2depth 2-3 1depth 2 > 2depth 2-3
3 0 1depth 3 1depth 3
13 3 2depth 1-1 1depth 3 > 2depth 1-1
And when data is inserted, it is placed under the parent.
Like This
id parent_id name depth_fullname
1 0 1depth 1 1depth 1
14 1 2depth 1-2 1depth 1 > 2depth 1-4
4 1 2depth 1-1 1depth 1 > 2depth 1-1
10 4 3depth 1-1 1depth 1 > 2depth 1-1 > 3depth 1-1
11 4 3depth 1-2 1depth 1 > 2depth 1-1 > 3depth 1-2
12 4 3depth 1-3 1depth 1 > 2depth 1-1 > 3depth 1-3
5 1 2depth 1-2 1depth 1 > 2depth 1-2
6 1 2depth 1-3 1depth 1 > 2depth 1-3
15 6 3depth 1-1 1depth 1 > 2depth 1-3 > 3depth 1-1 *Latest
2 0 1depth 2 1depth 2
7 2 2depth 2-1 1depth 2 > 2depth 2-1
8 2 2depth 2-2 1depth 2 > 2depth 2-2
9 2 2depth 2-3 1depth 2 > 2depth 2-3
3 0 1depth 3 1depth 3
13 3 2depth 1-1 1depth 3 > 2depth 1-1
if leaved a comment, I want to post the latest post with the parent post. Like this
id parent_id name depth_fullname
1 0 1depth 1 1depth 1
6 1 2depth 1-3 1depth 1 > 2depth 1-3
15 6 3depth 1-1 1depth 1 > 2depth 1-3 > 3depth 1-1
14 1 2depth 1-2 1depth 1 > 2depth 1-4
4 1 2depth 1-1 1depth 1 > 2depth 1-1
12 4 3depth 1-3 1depth 1 > 2depth 1-1 > 3depth 1-3
11 4 3depth 1-2 1depth 1 > 2depth 1-1 > 3depth 1-2
10 4 3depth 1-1 1depth 1 > 2depth 1-1 > 3depth 1-1
5 1 2depth 1-2 1depth 1 > 2depth 1-2
3 0 1depth 3 1depth 3
13 3 2depth 1-1 1depth 3 > 2depth 1-1
2 0 1depth 2 1depth 2
9 2 2depth 2-3 1depth 2 > 2depth 2-3
8 2 2depth 2-2 1depth 2 > 2depth 2-2
7 2 2depth 2-1 1depth 2 > 2depth 2-1
And Result
This is my query
Create Query
create table tree_table(
id int not null,
parent_id int not null,
name nvarchar(30) not null
);
Select Query I used a recursive function
If do it in the following way, the latest articles are not always displayed.
WITH tree_query AS (
SELECT
id ,
parent_id ,
name ,
convert(varchar(255), id) sort,
convert(varchar(255), name) depth_fullname
FROM tree_table
WHERE
parent_id = 0
UNION ALL
SELECT
B.id ,
B.parent_id ,
B.name ,
convert(varchar(255),
convert(nvarchar,C.sort) + ' > ' + convert(varchar(255), B.id)) sort ,
convert(varchar(255),
convert(nvarchar,C.depth_fullname) + ' > ' + convert(varchar(255), B.name)) depth_fullname
FROM tree_table B,
tree_query C
WHERE
B.parent_id = C.id )
SELECT
id,
parent_id,
name,
depth_fullname
FROM tree_query
ORDER BY sort
How should I change the query? Thanks for your answer!!
EDIT
@Sander
Thank you for your reply. But I have a few questions. In the query you sent, the comments are not sorted, so I leave a question like this.
your query produces this unwanted result
sort_number id parent_id name
-------------------- ----------- ----------- ------------------------------
1 1 0 1Title
1 4 1 ㄴRE 1Title 1-1
1 5 1 ㄴRE 1Title 1-2
1 6 1 ㄴRE 1Title 1-3
1 14 1 ㄴRE 1Title 1-4
1 15 6 ㄴRE 3Title 1-3-1
1 10 4 ㄴRE 1Title 1-1-1
1 11 4 ㄴRE 1Title 1-1-2
1 12 4 ㄴRE 1Title 1-1-3
2 3 0 3Title
2 13 3 ㄴRE 3Title 1-1
3 2 0 2Title
3 7 2 ㄴRE 2Title 1-1
3 8 2 ㄴRE 2Title 1-2
3 9 2 ㄴRE 2Title 1-3
I want result like this
sort_number id parent_id name
-------------------- ----------- ----------- ------------------------------
1 1 0 1Title
1 6 1 ㄴRE 1Title 1-3
1 15 6 ㄴRE 3Title 1-3-1
1 14 1 ㄴRE 1Title 1-4
1 4 1 ㄴRE 1Title 1-1
1 12 4 ㄴRE 1Title 1-1-3
1 11 4 ㄴRE 1Title 1-1-2
1 10 4 ㄴRE 1Title 1-1-1
1 5 1 ㄴRE 1Title 1-2
2 3 0 3Title
2 13 3 ㄴRE 3Title 1-1
3 2 0 2Title
3 9 2 ㄴRE 2Title 1-3
3 8 2 ㄴRE 2Title 1-2
3 7 2 ㄴRE 2Title 1-1
Thanks for your answer
解决方案
@Sander 我有一个问题
insert into tree_table (id, parent_id, name) values
(1, 0, '1Title'),
(2, 0, '2Title'),
(3, 0, '3Title'),
(4, 1, ' ㄴRE 1Title 1-1'),
(5, 1, ' ㄴRE 1Title 1-2'),
(6, 1, ' ㄴRE 1Title 1-3'),
(7, 2, ' ㄴRE 2Title 1-1'),
(8, 2, ' ㄴRE 2Title 1-2'),
(9, 2, ' ㄴRE 2Title 1-3'),
(10, 4, ' ㄴRE 1Title 1-1-1'),
(11, 4, ' ㄴRE 1Title 1-1-2'),
(12, 4, ' ㄴRE 1Title 1-1-3'),
(13, 3, ' ㄴRE 3Title 1-1'),
(14, 1, ' ㄴRE 1Title 1-4'),
(15, 6, ' ㄴRE 3Title 1-3-1');
然后结果
sort_number id parent_id name
-------------------- ----------- ----------- ------------------------------
1 1 0 1Title
1 4 1 ㄴRE 1Title 1-1
1 5 1 ㄴRE 1Title 1-2
1 6 1 ㄴRE 1Title 1-3
1 14 1 ㄴRE 1Title 1-4
1 15 6 ㄴRE 3Title 1-3-1
1 10 4 ㄴRE 1Title 1-1-1
1 11 4 ㄴRE 1Title 1-1-2
1 12 4 ㄴRE 1Title 1-1-3
2 3 0 3Title
2 13 3 ㄴRE 3Title 1-1
3 2 0 2Title
3 7 2 ㄴRE 2Title 1-1
3 8 2 ㄴRE 2Title 1-2
3 9 2 ㄴRE 2Title 1-3
但我想要这样的结果
sort_number id parent_id name
-------------------- ----------- ----------- ------------------------------
1 1 0 1Title
1 6 1 ㄴRE 1Title 1-3
1 15 6 ㄴRE 3Title 1-3-1
1 14 1 ㄴRE 1Title 1-4
1 4 1 ㄴRE 1Title 1-1
1 12 4 ㄴRE 1Title 1-1-3
1 11 4 ㄴRE 1Title 1-1-2
1 10 4 ㄴRE 1Title 1-1-1
1 5 1 ㄴRE 1Title 1-2
2 3 0 3Title
2 13 3 ㄴRE 3Title 1-1
3 2 0 2Title
3 9 2 ㄴRE 2Title 1-3
3 8 2 ㄴRE 2Title 1-2
3 7 2 ㄴRE 2Title 1-1
有办法吗？
推荐阅读
- mysql - 如何编写这样的查询？
- angular - Angular HTTP - 订阅提供未解决的变量
- sql - 如何使枢轴超过一列值sql
- apache - REWRITE RULE 反应奇怪
- kubernetes - 如何使用 Minikube 将应用程序部署到 Kubernetes？
- javascript - 我使用 agoraIO web app sdk 创建了该应用，但无法加入视频通话
- javascript - 嵌套函数 (Javascript)
- python-3.x - 是否可以使用 python 在 chrome 上打开选项卡或其他内容？
- python - Raspberry：GPIOs+Buttons - 相同的代码，不同的效果
- python - Networkx 中是否已经实现了返回路径长度和路径的算法？