34,838
社区成员




USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT IDENTITY(1,1) PRIMARY KEY,
Num INT
)
GO
INSERT INTO t(Num) VALUES (1);
INSERT INTO t(Num) VALUES (0);
INSERT INTO t(Num) VALUES (1);
INSERT INTO t(Num) VALUES (0);
declare @type int=0
select *,@type as type from t where num ^ (2-@type)>0
set @type=1
select *,@type as type from t where num ^ (2-@type)>0
set @type=2
select *,@type as type from t where num ^ (2-@type)>0
/*
--@type=0 :
id Num type
1 1 0
2 0 0
3 1 0
4 0 0
--@type=1:
id Num type
2 0 1
4 0 1
--@type=2 :
id Num type
1 1 2
3 1 2
*/
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT IDENTITY(1,1) PRIMARY KEY,
Num INT
)
GO
INSERT INTO t(Num) VALUES (1);
INSERT INTO t(Num) VALUES (0);
INSERT INTO t(Num) VALUES (1);
INSERT INTO t(Num) VALUES (0);
GO
--- 以上为测试表及测试数据 ----
--下面的写法,也许不算简洁,但足够清晰,如果有更多的情况,要扩展也非常容易
DECLARE @Type INT
SET @type=1
select * from t where Num IN (
SELECT [Num] FROM (
VALUES
(0,0) --当参数@Type的值为0时,查询所有记录(包括0和1)
,(0,1)
,(1,0) --当参数@Type的值为1时,查询Num为0的记录
,(2,1) --当参数@Type的值为2时,查询Num为1的记录
) AS T([Type],[Num])
WHERE [Type]=@Type
)
/*
id Num
----------- -----------
2 0
4 0
(2 行受影响)
*/
--或者
select * from t where Num IN (
SELECT [Num] FROM (
SELECT 0 AS [Type],0 AS [Num] --当参数@Type的值为0时,查询所有记录(包括0和1)
UNION ALL SELECT 0,1
UNION ALL SELECT 1,0 --当参数@Type的值为1时,查询Num为0的记录
UNION ALL SELECT 2,1 --当参数@Type的值为2时,查询Num为1的记录
) AS T
WHERE [Type]=@Type
)
/*
id Num
----------- -----------
2 0
4 0
(2 行受影响)
*/
declare int @Type
select * from table where CASE WHEN @Type=0 THEN num WHEN @Type=1 THEN 0 ELSE 1 END = NUM