T-SQL ORDER BY一列和mixing另一列

Question

我真的需要你的帮助。我必须从SQL表中获取一些特定顺序的数据。所有数据都应该按KeyID按降序排序，但如果在第二列中有一些数据相同的行，则必须用另一行分隔它们。

例如，我们有这样的表：

KeyID    UserID
1         15
2         17
3         19
4         19
5         15
6         17
7         17

在结果表中，它应该是

KeyID    UserID
7         17
5         15
6         17
4         19
2         17
3         19
1         15

有没有办法做到这一点？

Answer 1

这不会产生您想要的类型，但它应该扩展keyid：

order by row_number() over (partition by userId order by keyid desc),
         keyid desc

Answer 2

这应该适用于最多有2个连续记录共享相同UserID的简单情况

SELECT KeyID, UserID
FROM (
   SELECT KeyID, UserID,
          rn, COUNT(*) OVER (PARTITION BY UserID, grp ORDER BY KeyID DESC) AS rnk
   FROM (
      SELECT KeyID, UserID,
             ROW_NUMBER() OVER (ORDER BY KeyID DESC) AS rn,
             ROW_NUMBER() OVER (ORDER BY KeyID DESC) - 
             ROW_NUMBER() OVER (PARTITION BY UserID 
                                ORDER BY KeyID DESC) AS grp
      FROM mytable) AS t) AS x
ORDER BY rn + rnk, KeyID 

Answer 3

下面的查询运行良好，符合您的预期结果。

WITH TEMP AS(
SELECT  *,
        ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY KEYID DESC)ROW,
        ROW_NUMBER()OVER(ORDER BY KEYID DESC)ROW1 
FROM    USERS
        )

SELECT  KEYID,USERID 
FROM    (
        SELECT KEYID,USERID,ROW1,ROW FROM TEMP
        WHERE ROW<>2
        UNION 
        SELECT KEYID,USERID,ROW1+1 ROW1, ROW FROM TEMP
        WHERE ROW=2
        )B
ORDER BY ROW1 ASC

下面是我的输出：

​