时间戳

首页 > 解决方案 > 如何创建此数据透视 SQL 查询？

sql - 如何创建此数据透视 SQL 查询？

问题描述

我的 SQL Server 2012 实例中有以下表格：

表资产

------------------------------------
| ASSETID | ASSETTYPE | NAME       | 
|---------|-----------|------------|
| 1       | A         | Printer A  |
| 2       | A         | Printer B  |
| 3       | A         | Printer C  |
| 4       | B         | Laptop A   |
------------------------------------

tblASSETTYPES

--------------------------------------
| ASSETTYPE | TYPENAME | ICON        |
|-----------|----------|-------------|
| A         | Printer  | Printer.png |
| B         | Laptop   | Laptop.png  |
--------------------------------------

tblASSETCUSTOM

-------------------------------------------------------------
| CUSTOMID | ASSETID | MAKE | MODEL          | PRINTEDPAGES |
|----------|---------|------|----------------|--------------|
| 1        | 1       | HP   | Laserjet 4v    | 530          |
| 2        | 2       | HP   | Laserjet 4v    | 10000        |
| 3        | 3       | HP   | Officejet 1050 | NULL         |
| 4        | 4       | HP   | Probook 430 G3 | NULL         |
-------------------------------------------------------------

tblOID 数据

---------------------------------------------
| OIDDATAID | ASSETID | LABEL        | DATA |
|-----------|---------|--------------|------|
| 1         | 1       | Black copies | 430  |
| 2         | 1       | Color copies | 110  |
| 3         | 2       | Black copies | 5300 |
| 4         | 2       | Scans        | 450  |
---------------------------------------------

我想构建一个查询，将所有打印机及其所有详细信息作为列返回。我已经创建了这个查询：

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

select @cols = STUFF((SELECT ',' + QUOTENAME(label) 
                    from tblOIDData
                    group by label
                    order by label
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') 

set @query = 'Select * from (   Select Top 1000000 tAT.icon As icon, tat.typename as [ASSET TYPE] ,tA.Name as [ASSET NAME], tac.Model as [DEVICE MODEL], snmp.label as label, TRY_CONVERT(INT, TRY_CONVERT(NVARCHAR(100), snmp.data)) as PageCount, TA.Printepages as [PRINTED PAGES]   from tblAssets as tA,       tblAssetCustom as tAC,          tsysAssetTypes as tAT,      tblOIDData as SNMP  where tA.AssetType = tAT.AssetType      AND tAT.Typename = ''Printer''      AND tAC.AssetID = tA.AssetID        AND snmp.AssetID = tA.AssetID        ) as s PIVOT (     sum(PageCount)  for [LABEL] IN (' + @cols + ') ) AS pvt'

execute(@query);

这几乎给出了预期的结果。我唯一面临的是 ASSETID 3（打印机 C）不在结果中。可能是因为它不在 tblOIDData 表中。

我怎样才能将此资产也包含在我的结果中？

标签： sqlsql-server

解决方案

您可以LEFT JOIN在动态 sql 中使用 a 。

set @query = N'SELECT * 
FROM 
(
  SELECT TOP 1000000
   tAT.icon AS [icon],
   tat.typename AS [ASSET TYPE],
   tA.Name AS [ASSET NAME],
   tac.Model AS [DEVICE MODEL],
   snmp.label AS [label],
   TRY_CONVERT(INT, TRY_CONVERT(NVARCHAR(100), snmp.data)) AS [PageCount],
   TA.Printepages AS [PRINTED PAGES]
  FROM tblAssets AS tA
  JOIN tblAssetCustom AS tAC ON tAC.AssetID = tA.AssetID
  JOIN tsysAssetTypes AS tAT ON tAT.AssetType = tA.AssetType
  LEFT JOIN tblOIDData AS SNMP ON snmp.AssetID = tA.AssetID
  WHERE tAT.Typename = ''Printer''
) AS src 
PIVOT
(
  SUM([PageCount])
  FOR [label] IN (' + @cols + N') 
) AS pvt';

推荐阅读