需求：创建子查询或条件筛选secondz为0/30秒的SQL语句

方案一：直接在WHERE子句添加筛选条件 #

这是最简洁的方式，直接在现有WHERE条件后追加对秒数的判断，无需额外子查询：

SELECT TOP (1000) 
    FloatTable.[DateAndTime],
    DATEPART(ss, FloatTable.[DateAndTime]) AS secondz,
    FloatTable.[TagIndex],
    FloatTable.[Val],
    TagTable.[TagName],
    TagTable.[TagType],
    TagTable.[TagDataType]
FROM [FactoryTalk_Datalog].[dbo].[FloatTable] as FloatTable
JOIN [FactoryTalk_Datalog].[dbo].[TagTable] as TagTable
    ON FloatTable.[TagIndex] = TagTable.[TagIndex]
WHERE 
    (TagTable.[TagName] = '[PLC]FI225'
    OR TagTable.[TagName] = '[PLC]FI250'
    OR TagTable.[TagName] = '[PLC]SS_FIT1109')
    AND TagTable.[TagIndex] = 17
    AND FloatTable.[DateAndTime] >= '04/01/2022'
    -- 新增：筛选秒数为0或30的记录
    AND DATEPART(ss, FloatTable.[DateAndTime]) IN (0, 30)

方案二：使用子查询筛选 #

如果需要先计算出secondz字段再做筛选，可以用子查询包裹原查询逻辑，在外层过滤目标秒数：

SELECT *
FROM (
    SELECT TOP (1000) 
        FloatTable.[DateAndTime],
        DATEPART(ss, FloatTable.[DateAndTime]) AS secondz,
        FloatTable.[TagIndex],
        FloatTable.[Val],
        TagTable.[TagName],
        TagTable.[TagType],
        TagTable.[TagDataType]
    FROM [FactoryTalk_Datalog].[dbo].[FloatTable] as FloatTable
    JOIN [FactoryTalk_Datalog].[dbo].[TagTable] as TagTable
        ON FloatTable.[TagIndex] = TagTable.[TagIndex]
    WHERE 
        (TagTable.[TagName] = '[PLC]FI225'
        OR TagTable.[TagName] = '[PLC]FI250'
        OR TagTable.[TagName] = '[PLC]SS_FIT1109')
        AND TagTable.[TagIndex] = 17
        AND FloatTable.[DateAndTime] >= '04/01/2022'
) AS TempTable
WHERE TempTable.secondz IN (0, 30)

注意：若数据量较大，方案一的性能更优，因为它直接在WHERE子句中过滤，避免了子查询的额外开销。

内容的提问来源于stack exchange，提问作者robertuxo