时间戳

首页 > 解决方案 > 找不到记录时 SQL Server 显示结果

c# - 找不到记录时 SQL Server 显示结果

问题描述

当机器名称查找不在数据库中时，我试图让 SQL 列出未找到。我有查询工作，但我需要将它集成到我有其他查询的 C# 中。就目前而言，它比我想要的要复杂得多，我猜有一个更智能和简洁的原因让 SQL 达到相同的结果。

DECLARE @myHostCount as INT
DECLARE @myHostName1 as Varchar(50);
DECLARE @myHostName2  as Varchar(50) = 'Machine220054';
DECLARE @myHostCount1 as INT
DECLARE @myHostName3 as Varchar(50);
DECLARE @myHostName4  as Varchar(50) = 'Machine22054';

SET @myHostCount1 = (SELECT COUNT(*) FROM db_owner.host WHERE name LIKE '%Machine22054%')

SET @myHostName3 = (SELECT Name FROM db_owner.host WHERE name LIKE '%Machine22054%')

SET @myHostCount = (SELECT COUNT(*) FROM db_owner.host WHERE name LIKE '%Machine220054%')

SET @myHostName1 = (SELECT Name FROM db_owner.host WHERE name LIKE '%Machine220054%')

SELECT
    CASE
       WHEN @myHostCount = 1 THEN 'Found'
       WHEN @myHostCount = 0 THEN 'Not Found'
    END AS 'Result', 
    @myHostName2 AS 'HostName'

UNION

SELECT
    CASE
       WHEN @myHostCount1 = 1 THEN 'Found'
       WHEN @myHostCount1 = 0 THEN 'Not Found'
    END AS 'Result', 
    @myHostName4 AS 'HostName'

输出：

Result      Machine Name
--------------------------
Found       Machine220054
Not Found   Machine22054

标签： c#sql-server

解决方案

第一：尝试尽可能多地删除 SQL 中的过程逻辑。第二：尽可能减少使用“Like”查询。它将阻碍索引并最终导致瓶颈，或者至少将其限制为以“％”结尾而不是以 1 开头。第三：您假设您的代码中只有零次或一次命中。如果（给定非唯一或基于类似的查询）有多个怎么办？

我会去做这样的事情：

declare @looking table (name varchar(50))

insert into @looking (name) values ('Machine220054'),('Machine22054')
   select case when t.name is null then 'Not Found' else 'Found' end as [FoundOrNot], t.name as [FoundName],l.name as [SearchName]
          from @looking as l 
                     left outer join db_owner.host as t 
                             on t.name like '%' + l.name + '%'

更新：针对随机表进行测试，由于预期有一个命中或没有命中而删除了“分组依据”。

推荐阅读