时间戳

首页 > 解决方案 > 查询 SQL Server 的列顺序错误 - 没有人能回答这个问题

sql - 查询 SQL Server 的列顺序错误 - 没有人能回答这个问题

问题描述

我正在使用 SQL Server 2014。我必须为我们的客户端生成一个数据提取作为文本文件，并且它必须在提取的底部有一个尾行

因此，在 A 行的表格底部，它需要有“TRL”，在 B 行的表格底部，它需要包含包含数据的行数减去标题行

当我在 SQL 中运行查询时，它会按我想要的顺序返回。但是当我建立与 Excel 的数据连接时，它以不同的顺序出现

下面是一个简单的模型，说明我需要如何为客户显示提取物

在此处输入图像描述

下面是我正在使用的 SQL 服务器中的查询

SELECT 
    CAST(DateOfCall AS sql_variant) AS 'DateOfCall', 
    CAST(AgentName AS sql_variant) AS 'AgentName', 
    Connects, Completes, DMC, COMPANY_BPID, 
    ContactNumber, TalkTime, Disposition, Campaign, CA, 
    mpan_mpr, Installer, PKCallResults
FROM 
    dbo.CompanyX_CallResults C
INNER JOIN 
    dbo.CompanyX_ContactInfo X ON C.CallAccount = X.PKCustomerRecord
WHERE 
    DateOfCall BETWEEN '2020-10-15 00:00:01' AND '2020-10-15 23:59:59'
    AND PoolFK = 504

UNION ALL

SELECT 
    'TRL ' AS Detail,
    COUNT(AgentName) - 1 AS ROWID,
    '' AS Detail3, '' AS Detail4,
    '' AS Detail5, '' AS Detail6,
    '' AS Detail7, '' AS Detail8,
    '' AS Detail9, '' AS Detail10,
    '' AS Detail11, '' AS Detail12,
    '' AS Detail13, '' AS Detail14
FROM 
    dbo.CompanyX_CallResults

查询工作正常，但是当我建立与 Excel 的连接时，列的顺序出现错误！

DateOfCallAgentName应该是A列和B列然后是D列和E列

我想不通，我的其他同事也想不通

请任何帮助将不胜感激

标签： sqlsql-server

解决方案

在公用表表达式中CTE，您可以分配一个“Order_Hierarchy”列，其中“呼叫详细信息的日期”的值被分配为 1，摘要行被分配为 2。然后从CTE并包含子句中选择：ORDER BYOrder_Hierarchy。另外，根据@AlwaysLearning，UNION ALL 后半部分的列名不是必需的，但我把它们留在了里面。像这样的东西

with union_cte as (
    SELECT 
        1 as Order_Hierarchy,
        CAST(DateOfCall AS sql_variant) AS DateOfCall, 
        CAST(AgentName AS sql_variant) AS AgentName, 
        Connects, Completes, DMC, COMPANY_BPID, 
        ContactNumber, TalkTime, Disposition, Campaign, CA, 
        mpan_mpr, Installer, PKCallResults
    FROM 
        dbo.CompanyX_CallResults C
    INNER JOIN 
        dbo.CompanyX_ContactInfo X ON C.CallAccount = X.PKCustomerRecord
    WHERE 
        DateOfCall BETWEEN '2020-10-15 00:00:01' AND '2020-10-15 23:59:59'
        AND PoolFK = 504
    UNION ALL
    SELECT 
        2 as Order_Hierarchy,
        'TRL ' AS Detail,
        COUNT(AgentName) - 1 AS ROWID,
        '' AS Detail3, '' AS Detail4,
        '' AS Detail5, '' AS Detail6,
        '' AS Detail7, '' AS Detail8,
        '' AS Detail9, '' AS Detail10,
        '' AS Detail11, '' AS Detail12,
        '' AS Detail13, '' AS Detail14
    FROM 
        dbo.CompanyX_CallResults)
select DateOfCall, AgentName,  Connects, Completes, DMC, COMPANY_BPID, 
        ContactNumber, TalkTime, Disposition, Campaign, CA, 
        mpan_mpr, Installer, PKCallResults
from union_cte
order by Order_Hierarchy;

推荐阅读