mysql - SQL 中的多个 IF 语句

Question

我正在尝试在sql语句中编写多个IF条件。我想知道 IF SMB 为 1，然后检查 ACT、REN 或 REP 是否为 1，从 resp 表中选择 QF 列，我想要一个答案作为“a”和“b”作为其总和。

Input :

 @SMB = 1,@Dealer  = 0, @Act  = 1, @Ren = 1, @Rep = 1


tblACT:
QF
1
2

tblREP
QF
1
2

tblREN
QF
1
2


OUTPUT :
a b
6 0

@SMB bit = 1,
@Dealer bit = 1,
@Act bit = 1,
@Ren bit = 1,
@Rep bit = 1

Select SUM(tbl.a), SUM(tbl.b) from 
(If @SMB ='1'
    If @ACT ='1'  
       CASE WHEN QF IN (1,2) THEN 1 ELSE 0 as a 
       CASE WHEN QF IN (4,5) THEN 1 ELSE 0 as b from tblACT
    union all
    If @REN='1'  
      CASE WHEN QF IN (1,2) THEN 1 ELSE 0 as a
       CASE WHEN QF IN (4,5) THEN 1 ELSE 0 as b from tblREN
    union all
    If @REP='1'  
       CASE WHEN QF IN (1,2) THEN 1 ELSE 0 as a
       CASE WHEN QF IN (4,5) THEN 1 ELSE 0 as b from tblREP
union all
If @Dealer ='1'
   If @ACT ='1'  
       CASE WHEN QF IN (1,2) THEN 1 ELSE 0 as a
       CASE WHEN QF IN (4,5) THEN 1 ELSE 0 as b from tblACT
    union all
    If @REN='1'  
      CASE WHEN QF IN (1,2) THEN 1 ELSE 0 as a
       CASE WHEN QF IN (4,5) THEN 1 ELSE 0 as b from tblREN
    union all
    If @REP='1'  
       CASE WHEN QF IN (1,2) THEN 1 ELSE 0 as a
       CASE WHEN QF IN (4,5) THEN 1 ELSE 0 as b from tblREP) as tbl

Answer 1

试试这个并让我知道。

CREATE TABLE #tmp
(   
    QF INT
)

INSERT INTO #tmp VALUES (1) 
INSERT INTO #tmp VALUES (2) 
INSERT INTO #tmp VALUES (4)
INSERT INTO #tmp VALUES (4)
INSERT INTO #tmp VALUES (5)


DECLARE @SMB bit ,@Dealer BIT,@Act bit ,@Ren BIT,@Rep bit 
SET @SMB = 1
SET @Dealer = 1
SET @Act = 1
SET @Ren = 1
SET @Rep = 1

SELECT  SUM(( CASE WHEN ( @SMB = 1
                          OR @Dealer = 1
                        )
                        AND @ACT = 1
                        AND QF IN ( 1, 2 ) THEN 1
                   WHEN ( @SMB = 1
                          OR @Dealer = 1
                        )
                        AND @REN = 1
                        AND QF IN ( 1, 2 ) THEN 1
                   WHEN ( @SMB = 1
                          OR @Dealer = 1
                        )
                        AND @REP = 1
                        AND QF IN ( 1, 2 ) THEN 1
                   ELSE 0
              END )) AS A ,
        SUM(( CASE WHEN ( @SMB = 1
                          OR @Dealer = 1
                        )
                        AND @ACT = 1
                        AND QF IN ( 4, 5 ) THEN 1
                   WHEN ( @SMB = 1
                          OR @Dealer = 1
                        )
                        AND @REN = 1
                        AND QF IN ( 4, 5 ) THEN 1
                   WHEN ( @SMB = 1
                          OR @Dealer = 1
                        )
                        AND @REP = 1
                        AND QF IN ( 4, 5 ) THEN 1
                   ELSE 0
              END )) AS B
FROM    #tmp