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

m1m5dgzv 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(274)

我有一张这样的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

谢谢，谢谢你的帮助！

sql sql-server Date pivot tsql

来源：https://stackoverflow.com/questions/62023465/how-to-transpose-table-and-group-the-results-in

2条答案

按热度按时间

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

赞(0）回复(0）举报 2021-07-26

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<>在这里摆弄

赞(0）回复(0）举报 2021-07-26

我来回答

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

2条答案

相关问题

热门标签

最新问答