如何使用EXISTS运算符？
如何使用EXISTS运算符？
提问于 2021-03-31 23:24:34
也许有人可以帮我用EXISTS运算符检查-- ev.tfe_evento = 'Inizio lettura file'和ev.tfe_evento = 'lettura file'。我需要显示包含inizio lettura file和lettura file的所有文件。代码模板为：
SELECT column-names
FROM table-name
WHERE EXISTS (
SELECT column-name
FROM table-name
WHERE condition
)但是我怎样才能修改下面的代码来遵循这个例子呢？
DROP TABLE IF EXISTS #tflussi_eventi;
SELECT ev.*
INTO tab1
FROM tab1 ev (nolock)
JOIN (
SELECT lab
FROM #lab1
GROUP BY labname
) tf ON ev.labnameLIKE tf.labname+ '%'
WHERE ev.labevent= 'today'
AND ev.labevent= 'yesterday'回答 2
Stack Overflow用户
发布于 2021-03-31 23:42:37
有时我喜欢在检查这样的条件时使用窗口函数。您要在组中查找多个条件。
这不是您的实际查询，只是为了给您一个概念。
使用窗口函数：
SELECT DISTINCT fileName
FROM (SELECT E.*,
CASE WHEN MAX(CASE WHEN E.tfe_evento = 'lettura file' THEN 1
ELSE 0
END
) OVER ( PARTITION BY E.FileName ) = 1
AND MAX(CASE WHEN E.tfe_evento = 'Inizio lettura file' THEN 1
ELSE 0
END
) OVER ( PARTITION BY E.FileName ) = 1
THEN 1
ELSE 0
END AS FileHasBothEventConds
FROM EVENT E
)
WHERE FileHasBothEventConds = 1;使用EXISTS：
SELECT DISTINCT fileName
FROM EVENTS E
WHERE EXISTS
( SELECT 1
FROM EVENTS E_LF
WHERE E.fileName = E_LF.fileName
AND E_FL.tfe_evento = 'lettura file'
)
AND EXISTS
( SELECT 1
FROM EVENTS E_ILF
WHERE E.fileName = E_ILF.fileName
AND E_ILF.tfe_evento = 'Inizio lettura file'
)Stack Overflow用户
发布于 2021-03-31 23:45:08
您应该使用LIKE运算符，而不是=。即：
SELECT ev.*
INTO #tflussi_eventi
FROM tflussi_eventi ev (nolock)
JOIN (
SELECT FileNameCutoff
FROM #FileCutoffTime
GROUP BY FileNameCutoff
) tf ON ev.tfe_sorgente LIKE tf.FileNameCutoff + '%'
WHERE ev.tfe_evento LIKE '%Inizio lettura file%'
-- Unnecessary because of first criteria
-- AND ev.tfe_evento = 'lettura file'
AND ev.tfe_data >= @DateToCheck
AND ev.tfe_data < @DateToCheck + 1 -- not sure what you mean here, if it is datetime, using DateAdd() is safer但是，这可能会影响性能，您应该考虑创建全文索引并使用全文搜索。
编辑:虽然它仍然会因为您不执行全文搜索而受到影响，但您可能会节省，也许可以忽略不计的连接表上的时间，这实际上除了“存在检查”之外没有其他重要的意义：
SELECT ev.*
INTO #tflussi_eventi
FROM tflussi_eventi ev (nolock)
WHERE ev.tfe_evento LIKE '%Inizio lettura file%'
-- Unnecessary because of first criteria
-- AND ev.tfe_evento = 'lettura file'
AND ev.tfe_data >= @DateToCheck
AND ev.tfe_data < @DateToCheck + 1 -- not sure what you mean here, if it is datetime, using DateAdd() is safer
and EXISTS (
SELECT *
FROM #FileCutoffTime tf
where ev.tfe_sorgente LIKE tf.FileNameCutoff + '%');页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/66890703
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号