测试脚本:
--Begin 测试数据
DECLARE @temp TABLE(
number int
)INSERT INTO @temp(number) VALUES(0)
INSERT INTO @temp(number) VALUES(-1)
INSERT INTO @temp(number) VALUES(+1)
INSERT INTO @temp(number) VALUES(-2)
INSERT INTO @temp(number) VALUES(+2)DECLARE @type int --0: 列出全部数据 1:列出全部正数 2:列出全部负数
SET @type = RAND() * 3
SELECT @type
--End 测试数据IF @type = 0 -- 全部
SELECT number
FROM @temp
ELSE IF @type = 1 -- 正数
SELECT number
FROM @temp
WHERE number > 0
ELSE -- 负数
SELECT number
FROM @temp
WHERE number < 0
GO
第二次简化成一个SELECT语句,,,不知道还能不能再简化
--Begin 测试数据
DECLARE @temp TABLE(
number int
)INSERT INTO @temp(number) VALUES(0)
INSERT INTO @temp(number) VALUES(-1)
INSERT INTO @temp(number) VALUES(+1)
INSERT INTO @temp(number) VALUES(-2)
INSERT INTO @temp(number) VALUES(+2)DECLARE @type int --0: 全部 1:正数 2:负数
SET @type = RAND() * 3
SELECT @type
--End 测试数据SELECT number
FROM @temp
WHERE (
CASE @type
WHEN 0 THEN 1
WHEN 1 THEN (CASE WHEN number > 0 THEN 1 ELSE 0 END)
WHEN 2 THEN (CASE WHEN number < 0 THEN 1 ELSE 0 END)
END) <> 0
请你支招,如果没有更简的,分就散了。
--Begin 测试数据
DECLARE @temp TABLE(
number int
)INSERT INTO @temp(number) VALUES(0)
INSERT INTO @temp(number) VALUES(-1)
INSERT INTO @temp(number) VALUES(+1)
INSERT INTO @temp(number) VALUES(-2)
INSERT INTO @temp(number) VALUES(+2)DECLARE @type int --0: 列出全部数据 1:列出全部正数 2:列出全部负数
SET @type = RAND() * 3
SELECT @type
--End 测试数据IF @type = 0 -- 全部
SELECT number
FROM @temp
ELSE IF @type = 1 -- 正数
SELECT number
FROM @temp
WHERE number > 0
ELSE -- 负数
SELECT number
FROM @temp
WHERE number < 0
GO
第二次简化成一个SELECT语句,,,不知道还能不能再简化
--Begin 测试数据
DECLARE @temp TABLE(
number int
)INSERT INTO @temp(number) VALUES(0)
INSERT INTO @temp(number) VALUES(-1)
INSERT INTO @temp(number) VALUES(+1)
INSERT INTO @temp(number) VALUES(-2)
INSERT INTO @temp(number) VALUES(+2)DECLARE @type int --0: 全部 1:正数 2:负数
SET @type = RAND() * 3
SELECT @type
--End 测试数据SELECT number
FROM @temp
WHERE (
CASE @type
WHEN 0 THEN 1
WHEN 1 THEN (CASE WHEN number > 0 THEN 1 ELSE 0 END)
WHEN 2 THEN (CASE WHEN number < 0 THEN 1 ELSE 0 END)
END) <> 0
请你支招,如果没有更简的,分就散了。
然后把接收的参数改成逻辑运算符=,>,<
这样的话……
SELECT number
FROM @temp
WHERE (
CASE @type
WHEN 0 THEN true
WHEN 1 THEN number > 0
WHEN 2 THEN number < 0
END) <> 0
不能执行 囧
where sign([number]) = case when @type=2 then -1 else @type end
number int
)INSERT INTO @temp(number) VALUES(0)
INSERT INTO @temp(number) VALUES(-1)
INSERT INTO @temp(number) VALUES(+1)
INSERT INTO @temp(number) VALUES(-2)
INSERT INTO @temp(number) VALUES(+2)DECLARE @type int --0: 全部 1:正数 2:负数
SET @type = RAND() * 3
--SELECT @typeSELECT *
FROM @temp
WHERE SIGN(number)=SIGN(@type)
如果对付实体表,数据多的话,
如果对number加索引,
那跑起来还没有你第一种写法速度快
if 判断只在编译时跑过吧
declare @sql varchar(256)
set @sql = 'select number from table_name where number '+@type+' 0'
execute(@sql)
前提是@type要接收=,>,<
--Begin 测试数据
DECLARE @temp TABLE(
number1 int
,number2 int
,char3 varchar(10)
)INSERT INTO @temp(number1, number2, char3) VALUES(0, 0, '囧a')
INSERT INTO @temp(number1, number2, char3) VALUES(-1, 0, '囧b')
INSERT INTO @temp(number1, number2, char3) VALUES(+1, 0, '')
INSERT INTO @temp(number1, number2, char3) VALUES(-2, 0, '囧d')
INSERT INTO @temp(number1, number2, char3) VALUES(+2, 0, '囧e')INSERT INTO @temp(number1, number2, char3) VALUES(0, -1, '囧1')
INSERT INTO @temp(number1, number2, char3) VALUES(-1, -1, '囧2')
INSERT INTO @temp(number1, number2, char3) VALUES(+1, -1, '')
INSERT INTO @temp(number1, number2, char3) VALUES(-2, -1, '囧4')
INSERT INTO @temp(number1, number2, char3) VALUES(+2, -1, '囧5')DECLARE @type1 int --0: 列出全部数据 1:列出全部正数 2:列出全部负数
SET @type1 = RAND() * 3
DECLARE @type2 int --0: 列出全部数据 1:列出全部正数 2:列出全部负数
SET @type2 = RAND() * 3
DECLARE @type3 int --0: 列出全部数据 1:列出长度大于0的数据
SET @type3 = RAND() * 3
--End 测试数据
SELECT @type1, @type2, @type3
GO
set @sql=@sql+' and col1='''+@con1+''''if @con2 is not null
set @sql=@sql+' and col2='''+@con2+''''exec(@sql)
--Begin 测试数据
DECLARE @temp TABLE(
number1 int
,number2 int
,char3 varchar(10)
)INSERT INTO @temp(number1, number2, char3) VALUES(0, 0, '囧a')
INSERT INTO @temp(number1, number2, char3) VALUES(-1, 0, '囧b')
INSERT INTO @temp(number1, number2, char3) VALUES(+1, 0, '囧c')
INSERT INTO @temp(number1, number2, char3) VALUES(-2, 0, '囧d')
INSERT INTO @temp(number1, number2, char3) VALUES(+2, 0, '囧e')INSERT INTO @temp(number1, number2, char3) VALUES(0, -1, '囧1')
INSERT INTO @temp(number1, number2, char3) VALUES(-1, -1, '囧2')
INSERT INTO @temp(number1, number2, char3) VALUES(+1, -1, '囧3')
INSERT INTO @temp(number1, number2, char3) VALUES(-2, -1, '囧4')
INSERT INTO @temp(number1, number2, char3) VALUES(+2, -1, '囧5')INSERT INTO @temp(number1, number2, char3) VALUES(0, +1, '囧1')
INSERT INTO @temp(number1, number2, char3) VALUES(-1, +1, '囧2')
INSERT INTO @temp(number1, number2, char3) VALUES(+1, +1, '囧3')
INSERT INTO @temp(number1, number2, char3) VALUES(-2, +1, '囧4')
INSERT INTO @temp(number1, number2, char3) VALUES(+2, +1, '囧5')INSERT INTO @temp(number1, number2, char3) VALUES(0, 0, '')
INSERT INTO @temp(number1, number2, char3) VALUES(-1, 0, '')
INSERT INTO @temp(number1, number2, char3) VALUES(+1, 0, '')
INSERT INTO @temp(number1, number2, char3) VALUES(-2, 0, '')
INSERT INTO @temp(number1, number2, char3) VALUES(+2, 0, '')INSERT INTO @temp(number1, number2, char3) VALUES(0, -1, '')
INSERT INTO @temp(number1, number2, char3) VALUES(-1, -1, '')
INSERT INTO @temp(number1, number2, char3) VALUES(+1, -1, '')
INSERT INTO @temp(number1, number2, char3) VALUES(-2, -1, '')
INSERT INTO @temp(number1, number2, char3) VALUES(+2, -1, '')INSERT INTO @temp(number1, number2, char3) VALUES(0, +1, '')
INSERT INTO @temp(number1, number2, char3) VALUES(-1, +1, '')
INSERT INTO @temp(number1, number2, char3) VALUES(+1, +1, '')
INSERT INTO @temp(number1, number2, char3) VALUES(-2, +1, '')
INSERT INTO @temp(number1, number2, char3) VALUES(+2, +1, '')DECLARE @type1 int --0: 列出全部数据 1:列出全部正数 2:列出全部负数
SET @type1 = RAND() * 3
DECLARE @type2 int --0: 列出全部数据 1:列出全部正数 2:列出全部负数
SET @type2 = RAND() * 3
DECLARE @type3 int --0: 列出全部数据 1:列出长度大于0的数据 2:列出长度为0的数据
SET @type3 = RAND() * 3
--End 测试数据
SELECT @type1, @type2, @type3SELECT *
FROM @temp
WHERE (
CASE @type1
WHEN 0 THEN 1
WHEN 1 THEN (CASE WHEN number1 > 0 THEN 1 ELSE 0 END)
WHEN 2 THEN (CASE WHEN number1 < 0 THEN 1 ELSE 0 END)
END) <> 0
AND (
CASE @type2
WHEN 0 THEN 1
WHEN 1 THEN (CASE WHEN number2 > 0 THEN 1 ELSE 0 END)
WHEN 2 THEN (CASE WHEN number2 < 0 THEN 1 ELSE 0 END)
END) <> 0
AND (
CASE @type3
WHEN 0 THEN 1
WHEN 1 THEN (CASE WHEN LEN(char3) > 0 THEN 1 ELSE 0 END)
WHEN 2 THEN (CASE WHEN LEN(char3) <= 0 THEN 1 ELSE 0 END)
END) <> 0
GO
FROM @temp
WHERE SIGN(CASE WHEN @type=0 THEN 0 ELSE number END)=SIGN(CASE WHEN @type=2 THEN -1 ELSE @type END)