时间戳

首页 > 解决方案 > SQL - T-SQL Pivot 返回 NULL

sql - SQL - T-SQL Pivot 返回 NULL

问题描述

我的查询中有以下问题

基本上，旋转字段（VENDA_DIARIA）返回 null

SELECT 
  'SALES AVG BY DATE' AS Vlr_Medio_Vendas_Diario, [VENDA_DIARIA]
FROM (SELECT VENDA_VL_TOTAL, VENDA_DT_VEND FROM VENDA) AS VENDAS PIVOT (AVG(VENDA_VL_TOTAL) FOR VENDA_DT_VEND IN ([VENDA_DIARIA])) AS TB_PIVOT

VENDA 表中没有空字段。

文达表：

CREATE TABLE [DBO].[VENDA](
    [VENDA_NR_ID] [NUMERIC](18, 0) IDENTITY(1,1) NOT NULL,
    [VENDA_NR_CLIENTE] [NUMERIC](18, 0) NULL,
    [VENDA_VL_TOTAL] [NUMERIC](17, 2) NULL,
    [VENDA_DT_VEND] [VARCHAR](8) NULL,
    [VENDA_NR_VEICULO] [NUMERIC](18, 0) NULL
)

文达的价值观：

INSERT INTO VENDA VALUES (10,17955.00   ,'20200915',    1 )
INSERT INTO VENDA VALUES (9 ,28405.00   ,'20200915',    2 )
INSERT INTO VENDA VALUES (8 ,32205.00   ,'20200915',    3 )
INSERT INTO VENDA VALUES (7 ,3800.00    ,'20200915',    4 )
INSERT INTO VENDA VALUES (6 ,22325.00   ,'20200915',    5 )
INSERT INTO VENDA VALUES (5 ,11400.00   ,'20200915',    6 )
INSERT INTO VENDA VALUES (4 ,19000.00   ,'20200915',    7 )
INSERT INTO VENDA VALUES (3 ,19000.00   ,'20200915',    8 )
INSERT INTO VENDA VALUES (2 ,19000.00   ,'20200915',    9 )
INSERT INTO VENDA VALUES (1 ,19000.00   ,'20200915',    10)

标签： sqlsql-servertsql

解决方案

您需要为结果中的每一列指定值：

SELECT 
  'SALES AVG BY DATE' AS Vlr_Medio_Vendas_Diario, [20200915]
FROM (SELECT VENDA_VL_TOTAL, VENDA_DT_VEND FROM #t) AS VENDAS 
PIVOT (AVG(VENDA_VL_TOTAL) FOR VENDA_DT_VEND IN ([20200915])) AS TB_PIVOT

推荐阅读