UNION与IF语句

问题描述：

我正在写一个查询，确实对数据库表各种检查，并返回结果的摘要（在一个单一的结果集）UNION与IF语句

IF (select COUNT(*) from member_table WHERE password IS NULL) > 0 
    SELECT 'password check' as name, 'ERROR' as result ,'there is user(s) with blank password' as description 
ELSE 
    SELECT 'password check' as name, 'COMPLETED' as result, 'OK' as description 

UNION 

IF (select COUNT(*) from server_context_properties) = 0 
    SELECT 'context properties check' as name, 'ERROR' as result ,'no context property has been entered' as description 
ELSE 
    SELECT 'context properties check' as name, 'COMPLETED' as result, 'OK' as description

结果表应该是这样的：

name     result description 
password check   COMPLETED OK 
contex properties check ERROR  no context property has been entered

我知道语法是不正确的，但我想不出有什么办法来实现这一点。

答

Select Properties.Name, Properties.Result, Properties.Description 
From (
     Select 'password check', 1 As value, 'ERROR' As Result, 'There is a user with a blank password' As Description 
     Union All Select 'password check', 0, 'COMPLETED', 'OK' 
     Union All Select 'context properties check', 0, 'ERROR', 'No context property has been entered' 
     Union All Select 'context properties check', 1, 'COMPLETED', 'OK' 
     ) As Properties 
    Join (
      Select 'password check' As name 
       , Case 
        When Exists(Select 1 From member_table Where password Is Null) Then 1 
        Else 0 
        End As Value 
      Union All 
      Select 'context properties check' 
       , Case 
        When Exists(Select 1 From server_context_properties) Then 1 
        Else 0 
        End 
      ) As Results 
     On Results.Name = Properties.Name 
      And Results.Value = Properties.Value

我认为这是最好的。似乎非常可扩展和干净:) – Joe 2011-03-17 18:47:56

答

也许通过这样做：

SELECT 'password check' as name, 'ERROR' as result ,'there is user(s) with blank password' as description 
WHERE EXISTS (select * from member_table WHERE password IS NULL) 
UNION 
SELECT 'password check' as name, 'ERROR' as result ,'there is user(s) with blank password' as description 
WHERE NOT EXISTS (select * from member_table WHERE password IS NULL) 

UNION 

SELECT 'context properties check' as name, 'ERROR' as result ,'no context property has been entered' as description 
WHERE NOT EXISTS (select * from server_context_properties) 
UNION 
SELECT 'context properties check' as name, 'COMPLETED' as result, 'OK' as description 
WHERE EXISTS (select * from server_context_properties)

该解决方案看起来最干净的，但我只是有点担心，这可能会导致性能开销在为每次检查执行相同的查询两次。 – Joe 2011-03-17 18:41:46

答

你可以试试这个：

DECLARE @passwordcheck INT, @contextcheck INT 

SELECT @passwordcheck = COUNT(*) 
FROM member_table 
WHERE [password] IS NULL 

SELECT @contextcheck = COUNT(*) 
FROM server_context_properties 

SELECT 'password check' as name, 
     CASE WHEN @passwordcheck > 0 THEN 'ERROR' ELSE 'COMPLETED' END as result, 
     CASE WHEN @passwordcheck > 0 THEN 'there is user(s) with blank password' ELSE 'OK' as description 
UNION 
SELECT 'context properties check' as name, 
     CASE WHEN @contextcheck = 0 THEN 'ERROR' ELSE 'COMPLETED' END as result, 
     CASE WHEN @contextcheck = 0 THEN 'no context property has been entered' ELSE 'OK' END as description

UNION与IF语句

相关推荐