SQL中的多个IF语句
问题描述:
我正尝试在一个sql语句中写入多个IF条件。 我想知道IF SMB是1,然后检查ACT,REN或REP是否为1,从resp表中选择QF列,我希望单个答案为'a'和'b'作为它的总和。SQL中的多个IF语句
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
答
试试这个,让我知道。
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
+0
CASE WHEN(@SMB = 1 OR Dealer = 1) 不应该来,因为表格对于所有内容都不相同 – user1989 2014-11-04 19:01:10
你需要什么帮助?你期待什么输出? – 2014-11-04 18:33:00
这不起作用。它给出了语法错误,什么不是 – user1989 2014-11-04 18:34:22
为您提供表结构和表数据和输出示例 – 2014-11-04 18:35:28