SQL Server Display rows in given sequence SQL

v8wbuo2f 于 2023-06-28 发布在其他

关注(0)|答案(1)|浏览(88)

I'm trying to display rows in given sequence.

I have a table that has parent-child relation in itself. Table looks like this:

| ID1(Id)  | ID2(ParentID)| Date  
| -------- | -------- |--------
| 1        | 2        |06/21/13
| 2        | NULL     |06/21/13
| 3        | 2        |06/20/13
| 4        | 5        |06/28/13
| 6        | NULL     |06/07/13
| 5        | NULL     |06/09/13
...

There is relation between ID1 and ID2 where parent is ID2 and child is ID1. Parent has its parent always NULL and there may exist Parent that is not linked to anything like ID1. I want to display rows like so:

|  Col 1   |  Col 2   | Date
|          |    2     |06/21/13
|    1     |    2     |06/21/13
|    3     |    2     |06/20/13
|          |    5     |06/09/13
|    4     |    5     |06/28/13
|          |    6     |06/07/13

So first I need to have displayed Parent and then if there is something linked to Parent then in next rows childer needs to be displayed. All of this needs to be ordered by date. I know how to order by date but I have trouble thinking of solution to displaying correct order of Parent-Child.

EDIT. Order by date in descending order

sql-server

来源：https://stackoverflow.com/questions/76559851/display-rows-in-given-sequence-sql

1条答案

按热度按时间

elcex8rz1#

I don't understand how date is relevant here since it is not part of your output. But this will return the rows you stated you want in the order you want them.

--EDIT--

Your sample data and desired output changed. Here is the new version.

declare @Something table
(
    ID1 int
    , ID2 int
    , [Date] date
)
insert @Something values
(1, 2, '06/21/13')
, (2, NULL, '06/21/13')
, (3, 2, '06/20/13')
, (4, 5, '06/28/13')
, (6, NULL, '06/07/13')
, (5, NULL, '06/09/13')

select s2.ID1
    , s2.ID2
    , s.Date
from @Something s
join @Something s2 on s2.ID2 = s.ID1

union all

select s.ID2 --yes ID1 and ID2 are reveresed on purpose here because what was in the first column in needed in the second column
    , s.ID1
    , s.Date
from @Something s
where s.ID2 is null
order by [Date] desc
    , ID2
    , ID1

赞(0）回复(0）举报 2023-06-28

我来回答

SQL Server Display rows in given sequence SQL

1条答案

相关问题

热门标签

最新问答