寻找一种优雅的方式来解决这个问题...
DECLARE @ZIP INT
SET @ZIP = 55555
IF @ZIP = ALL(SELECT ZIP FROM PEOPLE WHERE PERSONTYPE = 1)
PRINT 'All people of type 1 have the same zip!'
ELSE
PRINT 'Not All people of type 1 have the same zip!'
问题在于,如果(SELECT ZIP FROM PEOPLE WHERE PERSONTYPE = 1)没有返回任何记录,那么上述IF语句将被评估为true。我正在寻找一种方法,在ALL子查询没有返回记录时,使其评估为false。
我的当前解决方案:
DECLARE @ZIP INT
SET @ZIP = 55555
DECLARE @ALLZIPS TABLE (INT ZIP)
INSERT INTO @ALLZIPS
SELECT ZIP FROM PEOPLE WHERE PERSONTYPE = 1
IF EXISTS(SELECT TOP 1 * FROM @ALLZIPS) AND (@ZIP = ALL (SELECT ZIP FROM @ALLZIPS))
PRINT 'All people of type 1 have the same zip!'
ELSE
PRINT 'Not All people of type 1 have the same zip!'