UNION ALL的排序分页问题

SQLSERVER中分页语句通常需要排序,如果同时要对UNION ALL子句排序的话就会遇到一些问题.

---

有一个很简单的SQL,它实现了按C_A排序并且分页.

  SELECT C_A,
         C_B,
         C_C
    FROM T_A
ORDER BY C_A OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

但是需求有了变化,查询的SQL变成T_A+T_B,用上了UNION ALL,依然要按C1排序并且分页,但是T_A的结果必须在T_B前面,无脑一想改成了这样…

          (
              SELECT C_A,
                     C_B,
                     C_C
                FROM T_A
            ORDER BY C_A
          )
UNION ALL (
              SELECT C_A,
                     C_B,
                     C_C
                FROM T_B
            ORDER BY C_A
          )

突然发现UNION ALL子句里面不能加ORDER BY,这…瞬间就不爽了…,一时没招百度了下,得到下面改法.

   SELECT *
     FROM (
              SELECT TOP 99.999999 PERCENT C_A,
                     C_B,
                     C_C
                FROM T_A
            ORDER BY C_A
          ) TMP1
UNION ALL 
   SELECT *
     FROM (
              SELECT TOP 99.999999 PERCENT C_A,
                     C_B,
                     C_C
                FROM T_B
            ORDER BY C_A
          ) TMP2

TOP 100 PERCENT排序还会失效,所以用了99.999999,太丧心病狂了.
紧接着一想,不对啊,我还要分页啊,分页里面还得加排序,那么在子句里面排序还有个毛用,白忙一场…想出上面方法的大神用tempdb来解决,真心接受不了.

后来…我决定这么干.

          (
            SELECT C_A,
                   C_B,
                   C_C,
                   0   AS TMP_ORDER
              FROM T_A
          )
UNION ALL (
            SELECT C_A,
                   C_B,
                   C_C,
                   1   AS TMP_ORDER
              FROM T_B
          )
 ORDER BY TMP_ORDER,
          C_A OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

我这智商也就能想出这种办法了,算是解决问题了…