SQL语句-检查子查询的行数=1

Question

对如何做到这一点有什么建议吗？我在查询中有一个复杂的if语句，需要检查给定表的各种条件。例：

IF EXISTS  (SELECT Labeler
  FROM [xx].[dbo].[manuf]
  where Lname like '@Lname' AND Approved = 0 AND Access = 'P')
      
  AND NOT EXISTS (SELECT Labeler
  FROM [xx].[dbo].[manuf]
  where Lname like '@Lname' AND Approved = 1)

  AND NOT EXISTS(SELECT Labeler
  FROM [xx].[dbo].[manuf]
  where Lname like '@Lname' AND Approved = 2)


    RETURN 1
ELSE...

但是，对于第一个子查询，我还需要确保它只产生一行。如果第一个子查询只有一行，那么如何约束它只返回1呢？

Answer 1

如果您特别需要一行，则可以使用聚合：

IF 1 = (SELECT COUNT(*)
        FROM [xx].[dbo].[manuf]
        WHERE Lname like '@Lname' AND Approved = 0 AND Access = 'P'
       ) AND . . .

当您只需要检查是否存在时，我强烈建议使用EXISTS，因为它比聚合更快。

Answer 2

此查询可以简化为只访问[xx].[dbo].[manuf]表一次：

IF 1 = (
  select 1
  from [xx].[dbo].[manuf]
  where Lname like '@Lname'
    and Approved in (0, 1, 2)
  having
    /*There's only one row, that is approved = 0 and access = 'P'*/
    sum(case when Approved = 0 and Access = 'P' then 1 end) = 1
    /*And there's no rows with Approved > 0*/
    and max(Approved) = 0
)

  RETURN 1
...

db<>fiddle是https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=720069f7f2c44cf7b16bcff44af7bf9d。

Answer 3

我会用ROW_NUMBER()

SELECT ROW_NUMBER(PARTITION BY Labeler ORDER BY x)
FROM [xx].[dbo].[manuf]
where Lname like '@Lname' AND Approved = 0 AND Access = 'P'

然后检查看是否ROW_NUMBER()