如何在T联合查询中按结果集排序

Question

我想在我的上一个sql查询中使用order子句，并且我有3个以上的联合查询。我不想对前2个联合查询进行排序，但是我想在我的上一个sql语句中使用order子句。当前，获取错误

如果语句包含UNION、INTERSECT运算符，则

按项排序必须出现在“选择”列表中。

select 'Total Number of pat' Name, convert(varchar(20), count(id)) Number from table2 where id = 5
union
select 'Total Number of Doc' Name, convert(varchar(20), count(id)) Number from table3
union
select x.usertype, count(distinct userid) cnt
from [dbo].table1 t
cross apply (values (
    case when t.userid like '%[0-9][0-9[0-9]' then 'transition' else 'non transition' end,
    t.userid
)) x(usertype, userid)
where t.date >= dateadd(day,-7, getdate())
group by x.usertype
order by usertype desc

Answer 1

order by正在对这些联合的结果进行排序，但是您可以引入一个orderIndex列来实现正确的排序。

在这里，样本：

我尝试在下面的代码中构建示例数据。

    create table table1(
        userid varchar(100),
        usertype varchar(100),
        date date
    )

    insert into table1(userid, date) values ('Einsmayr', '2020-10-27')
    insert into table1(userid, date) values ('Eins123', '2020-10-27')
    insert into table1(userid, date) values ('Einschmid', '2020-10-27')
    insert into table1(userid, date) values ('Einshuber', '2020-10-27')
    insert into table1(userid, date) values ('Einsreitmayr', '2020-10-27')

    create table table2 (
        Name varchar(100),
        id int
    )
 
    insert into table2(Name, id) values('Zweirich', 5)
    insert into table2(Name, id) values('Zweifel', 6)
 
    create table table3 (
        Name varchar(100),
        id int
    )
 
    insert into table3(Name, id) values('Dreisinger', 17)
    insert into table3(Name, id) values('Dreibert', 18)

这允许进行以下查询：

    select usertype, Number
    from (
        select 'Total Number of pat' usertype, convert(varchar(20), count(id)) Number, 1 orderIndex from table2 where id = 5
        union
        select 'Total Number of Doc' Name, convert(varchar(20), count(id)) Number, 2 orderIndex from table3
        union
        select usertype, count(distinct userid) Number, 3 orderIndex
        from (
            select userid,  case when userid like '%[0-9][0-9[0-9]' then 'transition' else 'non transition' end usertype
            from table1
            where date >= dateadd(day,-7, getdate())
        ) x
        group by x.usertype
    ) y
    order by y.orderIndex, y.usertype

在这里找到解决方案：https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ac396c48f5dbcb4a53ad40fac70e9236