IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15 BEGIN PRINT 'The following titles are excellent mod_cook books:' PRINT ' ' SELECT SUBSTRING(title, 1, 35) AS Title FROM titles WHERE type = 'mod_cook' END ELSE PRINT 'Average title price is more than $15.'下面是结果集:The following titles are excellent mod_cook books:
Title ----------------------------------- Silicon Valley Gastronomic Treats The Gourmet Microwave (2 row(s) affected)
IF Exists(select 1 from sysobjects where name='tbMembers' and type='U') DROP TABLE tbMembers GO
B. 使用多个 IF...ELSE 块 下面的示例使用了两个 IF 块。如果书的平均价格不低于 $15,那么就显示文本:Average title price is more than $15。如果现代烹调书的平均价格高于 $15,则显示现代烹调书价格昂贵的语句。USE pubsIF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15 BEGIN PRINT 'The following titles are excellent mod_cook books:' PRINT ' ' SELECT SUBSTRING(title, 1, 35) AS Title FROM titles WHERE type = 'mod_cook' END ELSE IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') > $15 BEGIN PRINT 'The following titles are expensive mod_cook books:' PRINT ' ' SELECT SUBSTRING(title, 1, 35) AS Title FROM titles WHERE type = 'mod_cook' END
谢谢!!!好心的人们!!! 我决定嵌套一个子查询!! if exists(select 1 from sysobjects where name = 'tbMembers' and xtype = 'U') drop table tbMembers
if objectproperty(object_id('tbMembers'),N'isusertable')=1 drop table tbMembers
drop table tbMembers
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]
BEGIN
PRINT 'The following titles are excellent mod_cook books:'
PRINT ' '
SELECT SUBSTRING(title, 1, 35) AS Title
FROM titles
WHERE type = 'mod_cook'
END
ELSE
PRINT 'Average title price is more than $15.'下面是结果集:The following titles are excellent mod_cook books:
Title
-----------------------------------
Silicon Valley Gastronomic Treats
The Gourmet Microwave (2 row(s) affected)
DROP TABLE tbMembers
GO
下面的示例使用了两个 IF 块。如果书的平均价格不低于 $15,那么就显示文本:Average title price is more than $15。如果现代烹调书的平均价格高于 $15,则显示现代烹调书价格昂贵的语句。USE pubsIF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15
BEGIN
PRINT 'The following titles are excellent mod_cook books:'
PRINT ' '
SELECT SUBSTRING(title, 1, 35) AS Title
FROM titles
WHERE type = 'mod_cook'
END
ELSE
IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') > $15
BEGIN
PRINT 'The following titles are expensive mod_cook books:'
PRINT ' '
SELECT SUBSTRING(title, 1, 35) AS Title
FROM titles
WHERE type = 'mod_cook'
END
if exists(select 1 from sysobjects where name = 'tbMembers' and xtype = 'U')
drop table tbMembers
drop table tbMembers