sql—如何转换表并将结果分组?

m1m5dgzv  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(258)

我有一张这样的table:

datetime                   datacenter   machines
---------------------------------------------------------
2020-05-13 12:00:00.000    DC01         500
2020-05-13 12:00:00.000    DC02         100  
2020-04-10 13:00:00.000    DC01         510
2020-04-10 13:00:00.000    DC02         120
2020-03-1 14:00:00.000     DC01         530
2020-03-1 14:00:00.000     DC02         140

datetime列是一个 Datetime2 类型,其余为 VARCHAR . 我需要创建一个新视图,该视图将按日期时间对条目进行分组,并从具有 dc01 以及 dc02 数据中心,并将机器的数量放在相应的行中,基本上是交换和合并数据。
在源表中,每个datetime总是有2个条目,每个数据中心有一个条目,合并后datetime应该是唯一的。下面是对结果的说明 view : resulting_view ```
datetime dc01_machines dc02_machines

2020-05-13 12:00:00.000 500 100
2020-04-10 13:00:00.000 510 120
2020-03-1 14:00:00.000 530 140

我花了一段时间试图想出解决办法。在我看来,我有一个解决方案,那就是做2个单独的选择,每个数据中心一个,用union组合它们,然后只按datetime对它们进行分组,但我确信这很糟糕,它甚至没有运行,这是一个错误 `invalid syntax near GROUP` . 尝试如下:

(SELECT t1.datetime
,t1.machines as dc01_machines
,'' as dc02_machines
FROM table1 t1
WHERE datacenter = 'DC01')
UNION
(SELECT t1.datetime
,'' as dc01_machines
,t1.machines as dc02_machines
FROM table1 t1
WHERE datacenter = 'DC02')
GROUP BY datetime

谢谢,谢谢你的帮助!
vyswwuz2

vyswwuz21#

我建议使用条件聚合。
与特定供应商不同 pivot 在语法上,这是一种可移植的方法(即在大多数数据库上都可以使用),它的性能通常至少与特定于供应商的实现一样好,甚至更好。
它也更灵活-这对您的特定用例没有影响,但在更复杂的情况下,也就是说 pivot 无法容纳(例如:列出dc01中id为510或更大的机器,以及dc02中id为120或更少的机器)。

select 
    datetime,
    max(case when datacenter = 'DC01' then machines end) dc01_machines,
    max(case when datacenter = 'DC02' then machines end) dc02_machines
from mytable
group by datetime
order by datetime
nfs0ujit

nfs0ujit2#

CREATE TABLE yourtable ([datetime] datetime, datacenter varchar(10),machines INT)
insert into yourtable ([datetime],datacenter,machines) VALUES 
('2020-05-13 12:00:00.000' ,   'DC01'   ,  500 ),
('2020-05-13 12:00:00.000' ,   'DC02'   ,     100  ),
('2020-04-10 13:00:00.000',    'DC01'    ,     510),
('2020-04-10 13:00:00.000',    'DC02'    ,     120),
('2020-03-1 14:00:00.000',     'DC01'   ,      530),
('2020-03-1 14:00:00.000',     'DC02'   ,      140)
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.datacenter) 
            FROM yourtable c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [datetime], ' + @cols + ' from 
            (
                select [datetime],datacenter,machines
                from yourtable
           ) x
            pivot 
            (
                 max(machines)
                for datacenter in (' + @cols + ')
            ) p '

execute(@query)
datetime                | DC01 | DC02
:---------------------- | ---: | ---:
2020-03-01 14:00:00.000 |  530 |  140
2020-04-10 13:00:00.000 |  510 |  120
2020-05-13 12:00:00.000 |  500 |  100

db<>在这里摆弄

相关问题