按日期对父行进行排序，而子行在每个子行下方独立排序 [英] Ordering parent rows by date descending with child rows ordered independently beneath each

问题描述

这是我的表架构的人为设计版本，用于说明我的问题:

This is a contrived version of my table schema to illustrate my problem:

QuoteID，详细信息，创建日期，ModelQuoteID

QuoteID, Details, DateCreated, ModelQuoteID

其中QuoteID是主键，而ModelQuoteID是可为空的外键，返回到该表，以表示已根据另一个引号建模的引号(并且可能随后更改了其Details列等).

Where QuoteID is the primary key and ModelQuoteID is a nullable foreign key back onto this table to represent a quote which has been modelled off another quote (and may have subsequently had its Details column etc changed).

我需要返回一个由DateCreated降序排列的报价列表，但建模报价除外，该报价应位于其父报价之下，按日期排序，而其他兄弟报价中的报价则按降序排列(报价只能建模一个深度).

I need to return a list of quotes ordered by DateCreated descending with the exception of modelled quotes, which should sit beneath their parent quote, ordered by date descending within any other sibling quotes (quotes can only be modelled one level deep).

例如，如果我有以下4个引号行:

So for example if I have these 4 quote rows:

1, 'Fix the roof', '01/01/2012', null
2, 'Clean the drains', '02/02/2012', null
3, 'Fix the roof and door', '03/03/2012', 1
4, 'Fix the roof, door and window', '04/04/2012', 1
5, 'Mow the lawn', '05/05/2012', null

然后我需要按以下顺序返回结果:

Then I need to get the results back in this order:

5 - Mow the lawn
2 - Clean the drains
1 - Fix the roof
4 - -> Fix the roof, door and window
3 - -> Fix the roof and door

我还传递了搜索条件(例如，Details的关键字)，并且即使它们不包含搜索项，但其父报价却包含了它们，我也会返回建模的报价.我已经使用通用表表达式来获取原始报价，并与用于建模的报价的联接结合在一起.

I'm also passing in search criteria such as keywords for Details, and I'm returning modelled quotes even if they don't contain the search term but their parent quote does. I've got that part working using a common table expression to get the original quotes, unioned with a join for modelled ones.

这很好，但是目前我必须将建模引号重新排列为正确的代码顺序.那不是理想的，因为我的下一步是在SQL中实现分页，并且如果那时行没有正确分组，那么我将不会在当前页面中显示子级来对代码进行重新排序.一般来说，无论如何，它们都会自然地组合在一起，但并非总是如此.您今天可以为一个月前的报价创建模型报价.

That works nicely but currently I'm having to do the rearrangement of the modelled quotes into the correct order in code. That's not ideal because my next step is to implement paging in the SQL, and if the rows are not grouped properly at that time then I won't have the children present in the current page to do the re-ordering in code. Generally speaking they will be naturally grouped together anyway, but not always. You could create a model quote today for a quote from a month back.

我已经花了很多时间，任何SQL专家都可以帮忙吗?非常感谢.

I've spent quite some time on this, can any SQL gurus help? Much appreciated.

这是我的人为设计的SQL版本，适合我的人为示例:-)

Here is a contrived version of my SQL to fit my contrived example :-)

;with originals as (
select
    q.*
from
    Quote q
where
    Details like @details
)
select
    *
from
(
select
    o.*
from
    originals o

union

select
    q2.*
from
    Quote q2
join
    originals o on q2.ModelQuoteID = o.QuoteID
)
as combined

order by
    combined.CreatedDate desc

推荐答案

观看奥运会-只是略过您的帖子-好像您想控制每个级别(根目录和一个级别)的排序，并确保数据返回时，子级直接位于其父级的正下方(因此您可以分页数据...).我们一直在这样做.您可以向每个内部查询添加 order by ，并创建 sort 列.我设计了一个稍有不同的示例，您应该可以轻松地将其应用于您的情况.我对根的升序和第一级的降序进行了排序，以说明如何控制每个部分.

Watching the Olympics -- just skimmed your post -- looks like you want to control the sort at each level (root and one level in), and make sure the data is returned with the children directly beneath its parent (so you can page the data...). We do this all the time. You can add an order by to each inner query and create a sort column. I contrived a slightly different example that should be easy for you to apply to your circumstance. I sorted the root ascending and level one descending just to illustrate how you can control each part.

declare @tbl table (id int, parent int, name varchar(10))

insert into @tbl (id, parent, name)
values (1, null, 'def'), (2, 1, 'this'), (3, 1, 'is'), (4, 1, 'a'), (5, 1, 'test'),
       (6, null, 'abc'), (7, 6, 'this'), (8, 6, 'is'), (9, 6, 'another'), (10, 6, 'test')

;with cte (id, parent, name, sort) as (
  select id, parent, name, cast(right('0000' + cast(row_number() over (order by name) as varchar(4)), 4) as varchar(1024))
  from   @tbl
  where  parent is null

  union all

  select t.id, t.parent, t.name, cast(cte.sort + right('0000' + cast(row_number() over (order by t.name desc) as varchar(4)), 4) as varchar(1024))
  from   @tbl t inner join cte on t.parent = cte.id
)
select * from cte
order by sort

这将产生以下结果:

id    parent    name     sort
----  --------  -------  ----------
6     NULL      abc      0001
7     6         this     00010001
10    6         test     00010002
8     6         is       00010003
9     6         another  00010004
1     NULL      def      0002
2     1         this     00020001
5     1         test     00020002
3     1         is       00020003
4     1         a        00020004

您可以看到根节点升序排列，内部节点降序排列.

You can see that the root nodes are sorted ascending and the inner nodes are sorted descending.

这篇关于按日期对父行进行排序，而子行在每个子行下方独立排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！