主页 / dba / 问题 / 343226
Accepted
Jefferson
Asked: 2024-10-26 01:12:22 +0800 CST

临时表简化查询

  • 772

我正在尝试从 SiteConfiguration 表中的数据获取客户表。

我正在获取数据并将其设置为@AdditionalVoteViewers，这将是一个电子邮件地址字符串。然后我拆分字符串并将结果插入到临时表中。

此时，我将临时表与 Customer 表连接起来以返回CustomerId。

我只是想看看是否有更好的方法来做到这一点。

站点配置表中的数据

[email protected];[email protected]

客户表中的数据

[email protected] 1
[email protected] 2

代码：

declare @AdditionalVoteViewers VARCHAR(100) 
Select @AdditionalVoteViewers = 
(
    SELECT [Value] FROM [SiteConfiguration] 
    where Name = 'AdditionalVoteViewers'
)
print @AdditionalVoteViewers
--CREATE TABLE #TempAdditionalVoteViewers(email  varchar(MAX) )

select * into #TempAdditionalVoteViewers 
from [dbo].[Split] (@AdditionalVoteViewers,';')
--select * from #TempAdditionalVoteViewers

select [CustomerId] into #TempAdditionalVoteViewersId
from [dbo].[Customer] c 
join #TempAdditionalVoteViewers t on c.Email = t.val


select * from #TempAdditionalVoteViewersId
sql-server

2 个回答

  1. Best Answer

    没关系

    你做得对，因为你没有在查询中拆分字符串并将其连接到结果中。这通常会导致性能灾难。

    如果我可以提出一个建议，那就是确保临时表创建语句或选择将电子邮件列转换为与客户表中的电子邮件列匹配的数据类型和长度。

    在您的示例代码中，您正在执行以下操作：

    --CREATE TABLE #TempAdditionalVoteViewers(email  varchar(MAX) )

select * into #TempAdditionalVoteViewers from [dbo].[Split] (@AdditionalVoteViewers,';')

    对列或参数/变量使用最大数据类型将导致您无法查找任何有用的索引。我不知道您的Split函数代码是做什么的，但我看到的大多数代码都是从 20 多年的 SQL Server 博客中复制粘贴的，它们本来是通用的，而且经常返回荒谬的输出类型。

    • 3 
  2. -- as a general rule, I never do an INSERT INTO
-- I want the table to exist before the insert with whatever
-- DDL provisions that I see necessary for performance and integrity

CREATE TABLE #TempAdditionalVoteViewers (
    [email] varchar(max) PRIMARY KEY 
    -- I never have a temp table without a primary key unless there is
    -- a good reason (which almost never happens).
)

-- I never ever ever use implicit column order in insert statements
-- even if there is only one column like here
INSERT #TempAdditionalVoteViewers ([email])
SELECT DISTINCT splt.[email]  
FROM [dbo].[Split] (@AdditionalVoteViewers,';') as splt
WHERE LEN(splt.[email]) > 0


-- You could see the entire [SiteConfiguration] table shredded in one 
-- command, this way

SELECT sc.[Name], x.[email]
FROM [SiteConfiguration] as sc
CROSS APPLY (
    SELECT DISTINCT splt.[email] 
    from [dbo].[Split] (sc.[Value],';') as splt
    WHERE LEN(splt.[email]) > 0
) as x


-- This might be the answer that you are hoping for. No temp tables

SELECT sc.[Name], x.[email]
FROM [SiteConfiguration] as sc
CROSS APPLY (
    SELECT DISTINCT splt.[email] 
    FROM [dbo].[Split] (sc.[Value],';') as splt
    WHERE LEN(splt.[email]) > 0
) as x
WHERE sc.[Name] = 'AdditionalVoteViewers'
    • -2

相关问题