為什麼我的 PIVOT 查詢使用不包含 NULL 的數據集生成 NULL 結果？
馬特
我有一個設備數據庫，其中包含有關設備的數據，包括名稱、位置以及一些數字和字符串屬性。我正在嘗試執行查詢，僅將具有數值的那些屬性拉入數據透視表。
當我平淡地查詢數據時，數據看起來不錯。但是當我PIVOT在同一個數據集上運行查詢時，所有結果都是NULL.
設置：
/* database setup and data insert */
USE [master] ;
GO
IF EXISTS ( SELECT * FROM sys.databases WHERE [name] = N'DeviceDatabase' )
BEGIN
ALTER DATABASE [DeviceDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
DROP DATABASE [DeviceDatabase] ;
END
CREATE DATABASE [DeviceDatabase] ;
GO
USE [DeviceDatabase] ;
GO
CREATE TABLE dbo.Devices
(
[id] INT NOT NULL IDENTITY (1,1)
CONSTRAINT [PK_Devices] PRIMARY KEY CLUSTERED
, [device_name] VARCHAR(50) NOT NULL
, [device_display_name] VARCHAR(50) NOT NULL
, [device_location] VARCHAR(50) NULL
) ;
CREATE TABLE dbo.DeviceGroups
(
[id] INT NOT NULL IDENTITY (1,1)
CONSTRAINT [PK_DeviceGroups] PRIMARY KEY CLUSTERED
, [device_group_name] VARCHAR(50) NOT NULL
) ;
CREATE TABLE dbo.DeviceDeviceGroups
(
[id] INT NOT NULL IDENTITY (1,1)
CONSTRAINT [PK_DeviceDeviceGroups] PRIMARY KEY CLUSTERED
, [device_id] INT NOT NULL
CONSTRAINT [FK_DeviceDeviceGroups_Devices]
FOREIGN KEY REFERENCES dbo.Devices ( [id] )
, [group_id] INT NOT NULL
CONSTRAINT [FK_DeviceDeviceGroups_DeviceGroups]
FOREIGN KEY REFERENCES dbo.DeviceGroups ( [id] )
) ;
CREATE TABLE dbo.DeviceAttributes
(
[id] INT NOT NULL IDENTITY (1,1)
CONSTRAINT [PK_DeviceAttributes] PRIMARY KEY CLUSTERED
, [device_id] INT NOT NULL
CONSTRAINT [FK_DeviceAttributes_Devices]
FOREIGN KEY REFERENCES dbo.Devices ( [id] )
, [attribute_name] VARCHAR(50) NOT NULL
, [attribute_value] VARCHAR(50) NULL
) ;
GO
INSERT
INTO dbo.Devices
( [device_name], [device_display_name], [device_location] )
VALUES ( 'dev001', 'Device 1', 'Location A' )
, ( 'dev002', 'Device 2', 'Location A' )
, ( 'dev003', 'Device 3', 'Location B' )
, ( 'dev004', 'Device 4', 'Location B' ) ;
INSERT
INTO dbo.DeviceGroups
( [device_group_name] )
VALUES ( 'Group 1A' )
, ( 'Group 1B' ) ;
INSERT
INTO dbo.DeviceDeviceGroups
( [device_id], [group_id] )
VALUES ( 1, 1 )
, ( 2, 1 )
, ( 3, 1 )
, ( 4, 1 ) ;
INSERT
INTO dbo.DeviceAttributes
( [device_id], [attribute_name], [attribute_value] )
VALUES ( 1, 'attrib #1', '0.10' )
, ( 1, 'attrib #2', '0.02' )
, ( 1, 'attrib #3', '0.07' )
, ( 1, 'attrib #4', '0.02' )
, ( 2, 'attrib #1', '0.16' )
, ( 2, 'attrib #2', '0.05' )
, ( 2, 'attrib #3', '0.12' )
, ( 2, 'attrib #4', '0.04' )
, ( 3, 'attrib #1', '0.15' )
, ( 3, 'attrib #2', '0.05' )
, ( 3, 'attrib #3', '0.07' )
, ( 3, 'attrib #4', '0.06' )
, ( 4, 'attrib #1', '0.10' )
, ( 4, 'attrib #2', '0.03' )
, ( 4, 'attrib #3', '0.07' )
, ( 4, 'attrib #4', '0.03' ) ;
GO
令人討厭的是，該attribute_value列是字符串而不是數字，但並非所有屬性本質上都是數字。（這是供應商的架構。）
當我對錶執行平面查詢（使用 CTE）時，我得到了一整套屬性值。
詢問：
/* flat query */
USE [DeviceDatabase] ;
GO
DECLARE @PrinterGroup AS VARCHAR(50) ;
SET @PrinterGroup = 'Group 1A' ;
WITH cte_GroupedDevices AS
(
SELECT d.[id] AS [device_id]
, d.[device_name]
, d.[device_display_name]
, d.[device_location]
, dg.[device_group_name]
FROM dbo.Devices AS d
INNER JOIN dbo.DeviceDeviceGroups AS ddg
ON d.[id] = ddg.[device_id]
INNER JOIN dbo.DeviceGroups AS dg
ON ddg.[group_id] = dg.[id]
WHERE dg.[device_group_name] = @PrinterGroup
)
, cte_AttributedDevices AS
(
SELECT gd.[device_name]
, gd.[device_display_name]
, gd.[device_group_name]
, gd.[device_location]
, da.[attribute_name]
, CAST ( da.[attribute_value] AS DECIMAL (5,2) ) AS [attribute_value]
FROM cte_GroupedDevices AS gd
INNER JOIN dbo.DeviceAttributes AS da
ON gd.[device_id] = da.[device_id]
WHERE da.[attribute_name] IN
(
'attrib #1'
, 'attrib #2'
, 'attrib #3'
, 'attrib #4'
)
)
SELECT [device_display_name]
, [device_group_name]
, [device_location]
, [attribute_name]
, [attribute_value]
FROM cte_AttributedDevices
ORDER BY [device_name] ASC, [attribute_name] ASC ;
結果：
device_display_name | device_group_name | device_location | attribute_name | attribute_value
---------------------------------------------------------------------------------------
Device 1 Group 1A Location A attrib #1 0.10
Device 1 Group 1A Location A attrib #2 0.02
Device 1 Group 1A Location A attrib #3 0.07
Device 1 Group 1A Location A attrib #4 0.02
Device 2 Group 1A Location A attrib #1 0.16
Device 2 Group 1A Location A attrib #2 0.05
Device 2 Group 1A Location A attrib #3 0.12
Device 2 Group 1A Location A attrib #4 0.04
Device 3 Group 1A Location B attrib #1 0.15
Device 3 Group 1A Location B attrib #2 0.05
Device 3 Group 1A Location B attrib #3 0.07
Device 3 Group 1A Location B attrib #4 0.06
Device 4 Group 1A Location B attrib #1 0.10
Device 4 Group 1A Location B attrib #2 0.03
Device 4 Group 1A Location B attrib #3 0.07
Device 4 Group 1A Location B attrib #4 0.03
但是，當我使用相同的 CTE 基礎針對相同的數據集執行PIVOT查詢（旋轉attribute_name列）時，所有屬性值都為 NULL。
詢問：
/* pivot query */
USE [DeviceDatabase] ;
GO
DECLARE @PrinterGroup AS VARCHAR(50) ;
SET @PrinterGroup = 'Group 1A' ;
WITH cte_GroupedDevices AS
(
SELECT d.[id] AS [device_id]
, d.[device_name]
, d.[device_display_name]
, d.[device_location]
, dg.[device_group_name]
FROM dbo.Devices AS d
INNER JOIN dbo.DeviceDeviceGroups AS ddg
ON d.[id] = ddg.[device_id]
INNER JOIN dbo.DeviceGroups AS dg
ON ddg.[group_id] = dg.[id]
WHERE dg.[device_group_name] = @PrinterGroup
)
, cte_AttributedDevices AS
(
SELECT gd.[device_name]
, gd.[device_display_name]
, gd.[device_group_name]
, gd.[device_location]
, da.[attribute_name]
, CAST ( da.[attribute_value] AS DECIMAL (5,2) ) AS [attribute_value]
FROM cte_GroupedDevices AS gd
INNER JOIN dbo.DeviceAttributes AS da
ON gd.[device_id] = da.[device_id]
WHERE da.[attribute_name] IN
(
'attrib #1'
, 'attrib #2'
, 'attrib #3'
, 'attrib #4'
)
)
SELECT [device_display_name]
, [device_group_name]
, [device_location]
, [attrib_1]
, [attrib_2]
, [attrib_3]
, [attrib_4]
FROM cte_AttributedDevices
PIVOT
(
MIN ( [attribute_value] )
FOR [attribute_name] IN
(
[attrib_1]
, [attrib_2]
, [attrib_3]
, [attrib_4]
)
) AS pvt
ORDER BY [device_name] ASC ;
結果：
device_display_name | device_group_name | device_location | attrib_1 | attrib_2 | attrib_3 | attrib_4
------------------------------------------------------------------------------------------------------------
Device 1 Group 1A Location A NULL NULL NULL NULL
Device 2 Group 1A Location A NULL NULL NULL NULL
Device 3 Group 1A Location B NULL NULL NULL NULL
Device 4 Group 1A Location B NULL NULL NULL NULL
我PIVOT在查詢段中嘗試了許多不同的函數-- MIN, MAX, SUM, AVG-- 所有這些都會產生相同的結果。
我曾嘗試將數據轉儲到臨時表中——一個在attribute_value列上具有數字數據類型的表——但它產生相同的結果，無論是平面查詢還是透視查詢。
我已經在基表和臨時表中NOT NULL對attribute_value列進行了約束。同樣的結果。
我究竟做錯了什麼？
約翰·卡佩萊蒂
這是一個工作示例。注意：@YourResults 是您實際初始查詢的替代品。
我沒有看到你轉換attrib #1到的任何邏輯attrib_1
例子
Declare @YourResults Table ([device_display_name] varchar(50),[device_group_name] varchar(50),[device_location] varchar(50),[attribute_name] varchar(50),[attribute_value] varchar(50))
Insert Into @YourResults Values
('Device 1','Group 1A','Location A','attrib #1',0.10)
,('Device 1','Group 1A','Location A','attrib #2',0.02)
,('Device 1','Group 1A','Location A','attrib #3',0.07)
,('Device 1','Group 1A','Location A','attrib #4',0.02)
,('Device 2','Group 1A','Location A','attrib #1',0.16)
,('Device 2','Group 1A','Location A','attrib #2',0.05)
,('Device 2','Group 1A','Location A','attrib #3',0.12)
,('Device 2','Group 1A','Location A','attrib #4',0.04)
,('Device 3','Group 1A','Location B','attrib #1',0.15)
,('Device 3','Group 1A','Location B','attrib #2',0.05)
,('Device 3','Group 1A','Location B','attrib #3',0.07)
,('Device 3','Group 1A','Location B','attrib #4',0.06)
,('Device 4','Group 1A','Location B','attrib #1',0.10)
,('Device 4','Group 1A','Location B','attrib #2',0.03)
,('Device 4','Group 1A','Location B','attrib #3',0.07)
,('Device 4','Group 1A','Location B','attrib #4',0.03)
Select *
From @YourResults
Pivot (min([attribute_value]) for [attribute_name] IN (
[attrib #1]
, [attrib #2]
, [attrib #3]
, [attrib #4]
) ) Pvt
結果
編輯 - 如果你想要 [attrib_1]你可以指定列並分配別名。
Select [device_display_name]
,[device_group_name]
,[device_location]
,[attrib_1] = [attrib #1]
,[attrib_2] = [attrib #2]
,[attrib_3] = [attrib #3]
,[attrib_4] = [attrib #4]
From @YourResults
Pivot (min([attribute_value]) for [attribute_name] IN (
[attrib #1]
, [attrib #2]
, [attrib #3]
, [attrib #4]
) ) Pvt
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
编辑于
相关文章
TOP 榜单
- 1
Linux的官方Adobe Flash存储库是否已过时？
- 2
用日期数据透视表和日期顺序查询
- 3
应用发明者仅从列表中选择一个随机项一次
- 4
Java Eclipse中的错误13，如何解决？
- 5
在Windows 7中无法删除文件（2）
- 6
在 Python 2.7 中。如何从文件中读取特定文本并分配给变量
- 7
套接字无法检测到断开连接
- 8
带有错误“ where”条件的查询如何返回结果？
- 9
有什么解决方案可以将android设备用作Cast Receiver？
- 10
Mac OS X更新后的GRUB 2问题
- 11
ggplot：对齐多个分面图-所有大小不同的分面
- 12
验证REST API参数
- 13
如何从视图一次更新多行（ASP.NET - Core）
- 14
尝试反复更改屏幕上按钮的位置 - kotlin android studio
- 15
计算数据帧中每行的NA
- 16
检索角度选择div的当前值
- 17
离子动态工具栏背景色
- 18
UITableView的项目向下滚动后更改颜色，然后快速备份
- 19
VB.net将2条特定行导出到DataGridView
- 20
蓝屏死机没有修复解决方案
- 21
通过 Git 在运行 Jenkins 作业时获取 ClassNotFoundException
我来说两句