SQL CASE表达式

--测试数据
DECLARE @tempTaccounts AS TABLE
(
    FID INT PRIMARY KEY,
    FAccount VARCHAR(30),
    FCompanyID INT,
    FLogonTimes INT, --登陆次数
    FGradeID INT,    --等级标识(-1=总公司,1=公司,0=子账号,2=代理和会员)
    FAgentType INT   --代理类型(1=公司号,2=直属号)
)

INSERT INTO @tempTaccounts(FID,FAccount,FCompanyID,FLogonTimes,FGradeID,FAgentType)
SELECT TOP(5)FID,FAccount,FCompanyID,FLogonTimes,FGradeID,FAgentType    
FROM dbo.TAccounts
WHERE FCompanyID=36052
AND FGradeID=-1

INSERT INTO @tempTaccounts(FID,FAccount,FCompanyID,FLogonTimes,FGradeID,FAgentType)
SELECT TOP(5)FID,FAccount,FCompanyID,FLogonTimes,FGradeID,FAgentType    
FROM dbo.TAccounts
WHERE FCompanyID=36052
AND FGradeID=0

INSERT INTO @tempTaccounts(FID,FAccount,FCompanyID,FLogonTimes,FGradeID,FAgentType)
SELECT TOP(5)FID,FAccount,FCompanyID,FLogonTimes,FGradeID,FAgentType    
FROM dbo.TAccounts
WHERE FCompanyID=36052
AND FGradeID=1

INSERT INTO @tempTaccounts(FID,FAccount,FCompanyID,FLogonTimes,FGradeID,FAgentType)
SELECT TOP(5)FID,FAccount,FCompanyID,FLogonTimes,FGradeID,FAgentType    
FROM dbo.TAccounts
WHERE FCompanyID=36052
AND FGradeID=2

INSERT INTO @tempTaccounts(FID,FAccount,FCompanyID,FLogonTimes,FGradeID,FAgentType)
SELECT TOP(5)FID,FAccount,FCompanyID,FLogonTimes,FGradeID,FAgentType    
FROM dbo.TAccounts
WHERE FCompanyID=36052
AND FLogonTimes=1


SELECT * FROM @tempTaccounts

 

一、CASE简单表达式

--CASE简单表达式
SELECT  FID,FAccount,FCompanyID,
        FGradeID,
        FGradeName = 
        ( CASE FGradeID 
            WHEN -1 THEN '总公司'
            WHEN 0  THEN '子账号'
            WHEN 1  THEN '公司'
            WHEN 2  THEN '代理和会员'
            ELSE '未知等级'
          END
         )
FROM @tempTaccounts
WHERE FCompanyID=36052

 

二、CASE搜索表达式

--CASE搜索表达式
SELECT  FID,FAccount,FCompanyID,
        FLogonTimes,
        FActivityLevel = 
        (
          CASE 
            WHEN FLogonTimes = 1 THEN '一般'
            WHEN FLogonTimes BETWEEN 2 AND 30 THEN '良好'
            WHEN FLogonTimes > 30 THEN '很好'
            ELSE '不活跃' --如果没有指定ELSE,则默认返回NULL
          END
        )
FROM @tempTaccounts
WHERE FCompanyID=36052

 

 

三、CASE作为过滤条件

DECLARE @gameId INT
SET @gameId=0


SELECT TOP(20) FUserID,FGameID FROM dbo.TOrders
WHERE FGameID = CASE 
                    WHEN @gameId IS NULL OR @gameId=0 THEN FGameID 
                    ELSE @gameId END
GROUP BY FUserID, FGameID

 

DECLARE @gameId INT
SET @gameId=44


SELECT TOP(20) FUserID,FGameID FROM dbo.TOrders
WHERE FGameID = CASE 
                    WHEN @gameId IS NULL OR @gameId=0 THEN FGameID 
                    ELSE @gameId END
GROUP BY FUserID, FGameID