随机订购表，但有例外 [英] Order table randomly but with exceptions

问题描述

我有一张表格需要随机排序，但有些行需要粘在一起.

I have a table which I need to sort randomly, but some rows need to stick together.

比如表格是这样生成的

所有具有 CATEGORY = C 和 CODE = 101 的行需要按顺序排列(一个接一个)，但在一般顺序中处于随机位置.其他行需要随机排序.

All the rows which have the CATEGORY = C and CODE = 101 need to be in sequence(one after the other), but at random position in the general order. Others rows need to be randomly sorted.

只能通过order by"来做到这一点?

It's possible to do this only with "order by"?

推荐答案

试试这个:

DECLARE @exceptionOrder uniqueidentifier = NEWID()

SELECT ID, Category, Code
FROM yourTable
ORDER BY CASE WHEN Category = 'C' AND Code = 101 THEN @exceptionOrder ELSE NEWID() END

这将为异常行分配相同的uniqueidentifier，为其余的每一行分配一个随机的uniqueidentifier，然后按它们排序.

This will assign the same uniqueidentifier to the exception rows, a random uniqueidentifier to each of the rest of the rows, then order by them.

这篇关于随机订购表，但有例外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！