主页 / dba / 问题 / 142139
Accepted
James Lupolt
Asked: 2016-06-25 02:42:31 +0800 CST

有没有办法让这个选择通过一次搜索检索相同的结果？

  • 772

是否可以通过修改查询或影响优化器的策略，通过一次查找或扫描检索与以下相同的数据？

与此类似的代码和架构目前在 SQL Server 2014 上。

在此处输入图像描述

复制脚本。设置：

USE tempdb;
GO
IF OBJECT_ID('dbo.TestUpload', 'U') IS NOT NULL 
  DROP TABLE dbo.TestUpload; 


CREATE TABLE dbo.TestUpload(
    JobRunId bigint NOT NULL,
    ThingAName nvarchar(255) NOT NULL,
    ThingAType nvarchar(255) NOT NULL,
    ThingAGranularity nvarchar(255) NOT NULL,
    ThingBName nvarchar(255) NOT NULL,
    ThingBType nvarchar(255) NOT NULL,
    ThingBGranularity nvarchar(255) NOT NULL
);
CREATE CLUSTERED INDEX IX_JobRunId ON dbo.TestUpload (JobRunId);

GO

INSERT INTO dbo.TestUpload (JobRunId, ThingAName, ThingAType, ThingAGranularity, ThingBName, ThingBType, ThingBGranularity)
  VALUES (1, 'A', 'B', 'C', 'D', 'E', 'F');
GO 10

INSERT INTO dbo.TestUpload (JobRunId, ThingAName, ThingAType, ThingAGranularity, ThingBName, ThingBType, ThingBGranularity)
  VALUES (1, 'D', 'E', 'F', 'A', 'B', 'C');
GO 10

询问：

DECLARE @JobRunID bigint = 1;

SELECT JobRunId,
  ThingAName AS Name, 
  ThingAType AS [Type], 
  ThingAGranularity AS Granularity
FROM dbo.TestUpload
WHERE JobRunId = @JobRunID
UNION
SELECT JobRunId,
  ThingBName AS Name, 
  ThingBType AS [Type], 
  ThingBGranularity AS Granularity
FROM dbo.TestUpload
WHERE JobRunId = @JobRunID;

拆除：

IF OBJECT_ID('dbo.TestUpload', 'U') IS NOT NULL 
  DROP TABLE dbo.TestUpload;

我认为这可能不是理想的建模。我试图从开发人员那里获得更多关于如何选择架构的信息，但我很好奇是否有我忽略的 TSQL 技巧，因为更改查询比更改架构更容易。

sql-server t-sql

2 个回答

  1. Best Answer

    我会尝试这个，但我不知道它是否会更有效率。您需要DISTINCT删除重复项，因此UNION ALL可能更合适，不需要两个不同的操作：

    SELECT DISTINCT 
    JobRunId = @JobRunID, 
    d.*
FROM dbo.TestUpload
  CROSS APPLY 
    (   SELECT 
          ThingAName AS Name, 
          ThingAType AS [Type], 
          ThingAGranularity AS Granularity
      UNION                            -- or UNION ALL
        SELECT 
          ThingBName, 
          ThingBType, 
          ThingBGranularity
    ) AS d 
WHERE JobRunId = @JobRunID ;

    UNION ALL计划：

    UNION ALL计划

    UNION计划：

    联盟计划

    • 7

  2. 使用交叉应用将列反透视为行 

    SELECT --DISTINCT most probably
  JobRunId,
  ut.Name, 
  ut.[Type], 
  ut.Granularity
FROM dbo.TestUpload
CROSS APPLY (
  SELECT ThingBName AS Name, 
  ThingBType AS [Type], 
  ThingBGranularity AS Granularity
  UNION ALL 
  SELECT ThingAName AS Name, 
  ThingAType AS [Type], 
  ThingAGranularity AS Granularity
  ) ut
WHERE JobRunId = @JobRunID
    • 4

相关问题