sql-server - SQL Server Pivots...我可以显示一系列订单类型的最后五个订单吗

Question

我有下表:

create table myorders(ordertype char(1), orderdate datetime, orderid int)

该表包含以下数据:

insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 20:04:48:287', 11082360)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 20:02:40:407', 40087130)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 20:02:07:277', 7990558)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:58:46:097', 8225181)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:58:39:740', 40087129)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:57:33:063', 8225235)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:56:17:207', 8225233)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:54:50:630', 8225232)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:48:07:300', 11082337)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:47:49:997', 40087128)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:46:40:667', 40087127)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:45:32:550', 8225231)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:45:11:203', 11082326)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:44:57:990', 8225230)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:43:52:953', 40087126)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:43:20:853', 8225229)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:41:33:740', 11082319)
insert into myorders(ordertype, orderdate, orderid) values('C', '2013-02-14 19:41:19:853', 8225228)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:40:33:127', 40087125)
insert into myorders(ordertype, orderdate, orderid) values('P', '2013-02-14 19:40:25:537', 40087124)

数据如下所示:

OrderType  OrderDate                    OrderId
--------   -----------------------      ---------
P      2013-02-14 20:04:48.287  11082360
P      2013-02-14 20:02:40.407  40087130
P      2013-02-14 20:02:07.277  7990558
C      2013-02-14 19:58:46.097  8225181
P      2013-02-14 19:58:39.740  40087129
C      2013-02-14 19:57:33.063  8225235
C      2013-02-14 19:56:17.207  8225233
C      2013-02-14 19:54:50.630  8225232
P      2013-02-14 19:48:07.300  11082337
P      2013-02-14 19:47:49.997  40087128
P      2013-02-14 19:46:40.667  40087127
C      2013-02-14 19:45:32.550  8225231
P      2013-02-14 19:45:11.203  11082326
C      2013-02-14 19:44:57.990  8225230
P      2013-02-14 19:43:52.953  40087126
C          2013-02-14 19:43:20.853  8225229
P      2013-02-14 19:41:33.740  11082319
C      2013-02-14 19:41:19.853  8225228
P      2013-02-14 19:40:33.127  40087125
P      2013-02-14 19:40:25.537  40087124

我想旋转数据，使其看起来像这样:

C_LastFiveOrders  C_OrderDate                  P_LastFiveOrders P_OrderDate
----------------  -----------------------      ---------------- -----------------------
8225181           2013-02-14 19:58:46.097      11082360         2013-02-14 20:04:48.287
8225235           2013-02-14 19:57:33.063      40087130         2013-02-14 20:02:40.407
8225233           2013-02-14 19:56:17.207      7990558          2013-02-14 20:02:07.277
8225232           2013-02-14 19:54:50.630      40087129         2013-02-14 19:58:39.740
8225231           2013-02-14 19:45:32.550      11082337         2013-02-14 19:48:07.300

请注意，订单是按订单日期降序排列的。

我希望能够添加其他订单类型。我还希望能够灵活地查看最后 x 个订单。在示例中，我查看的是最后五个订单。我希望能够查看最近 10 或 20 个订单。

Answer 1

可以通过多种不同的方式来执行此操作。

使用 CASE 聚合:您可以将聚合函数与 CASE 表达式结合使用:

select 
  max(case when ordertype = 'c' then orderid end) C_LASTFIVEORDERS,
  max(case when ordertype = 'c' then orderdate end) C_ORDERDATE,
  max(case when ordertype = 'p' then orderid end) P_LASTFIVEORDERS,
  max(case when ordertype = 'p' then orderdate end) P_ORDERDATE
from
(
  select orderid, ordertype, orderdate, rn
  from
  (
    select orderid, ordertype, orderdate,
      row_number() over(partition by ordertype
                        order by orderdate desc) rn
    from myorders
  ) src
  where rn <=5
) s
group by rn

参见SQL Fiddle with Demo .

多次联接:您可以多次联接表:

;with cte as
(
  select orderid, ordertype, orderdate,
      row_number() over(partition by ordertype
                        order by orderdate desc) rn
  from myorders
)
select 
  c1.orderid C_LASTFIVEORDERS,
  c1.orderdate C_ORDERDATE,
  c2.orderid P_LASTFIVEORDERS,
  c2.orderdate P_ORDERDATE
from cte c1
left join cte c2
  on c1.rn = c2.rn
  and c2.ordertype = 'P'
where c1.rn <=5
  and c1.ordertype = 'C'

参见SQL Fiddle with Demo

静态枢轴:

最后，您可以同时应用 UNPIVOT和 PIVOT函数获取结果:

select C_LastFiveOrders, C_orderdate,
  P_LastFiveOrders, P_orderdate
from
(
  select rn,
    case 
      when col = 'orderid' then ordertype+'_LastFiveOrders'
      else ordertype+'_'+col end col_name,
    value
  from
  (
    select ordertype,
      convert(varchar(50), orderdate, 121) orderdate,
      cast(orderid as varchar(50)) orderid,
      row_number() over(partition by ordertype 
                        order by orderdate desc) rn
    from myorders 
  )src
  unpivot
  (
    value
    for col in (orderdate, orderid)
  ) un
  where rn <= 5
) s
pivot
(
  max(value)
  for col_name in (C_LastFiveOrders, C_orderdate,
                   P_LastFiveOrders, P_orderdate) 
) piv

参见SQL Fiddle with Demo .

动态枢轴:

如果您的 OrderType 值未知，那么您可以使用动态 SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @colsName AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ordertype +c.col) 
                      from myorders
                      cross apply 
                      (
                        select '_LastFiveOrders' col
                        union all
                        select '_OrderDate'
                      ) c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
  = 'SELECT ' + @cols + ' 
     from 
     (
      select rn,
        case 
          when col = ''orderid'' then ordertype+''_LastFiveOrders''
          else ordertype+''_''+col end col_name,
        value
      from
      (
        select ordertype,
          convert(varchar(50), orderdate, 121) orderdate,
          cast(orderid as varchar(50)) orderid,
          row_number() over(partition by ordertype 
                            order by orderdate desc) rn
        from myorders 
      )src
      unpivot
      (
        value
        for col in (orderdate, orderid)
      ) un
      where rn <= 5
     ) s
     pivot 
     (
       max(value)
       for col_name in (' + @cols + ')
     ) p 
     '

execute(@query)

参见SQL Fiddle with Demo

所有查询都会给出结果:

| C_LASTFIVEORDERS |             C_ORDERDATE | P_LASTFIVEORDERS |             P_ORDERDATE |
-------------------------------------------------------------------------------------------
|          8225181 | 2013-02-14 19:58:46.097 |         11082360 | 2013-02-14 20:04:48.287 |
|          8225235 | 2013-02-14 19:57:33.063 |         40087130 | 2013-02-14 20:02:40.407 |
|          8225233 | 2013-02-14 19:56:17.207 |          7990558 | 2013-02-14 20:02:07.277 |
|          8225232 | 2013-02-14 19:54:50.630 |         40087129 | 2013-02-14 19:58:39.740 |
|          8225231 | 2013-02-14 19:45:32.550 |         11082337 | 2013-02-14 19:48:07.300 |