当然可以:
SELECT * FROM CUSTOMERS
WHERE case @ZTYPE
when 1 then DEPT_CODE = @ZVALUE
when 2 then CITY = @ZVALUE
else ......
end
SELECT * FROM CUSTOMERS
WHERE case @ZTYPE
when 1 then DEPT_CODE = @ZVALUE
when 2 then CITY = @ZVALUE
else ......
end
SELECT * FROM CUSTOMERS
WHERE case @ZTYPE
when 1 then DEPT_CODE
when 2 then CITY
else ......
end = @ZVALUE
应该
IF @ZTYPE = 1 THEN
SELECT * FROM CUSTOMERS WHERE DEPT_CODE = @ZVALUE
IF @ZTYPE = 2 THEN
SELECT * FROM CUSTOMERS WHERE CITY = @ZVALUE
...
DEPT_CODE = (CASE @ZTYPE WHEN 1 THEN @ZVALUE ELSE DEPT_CODE END)
AND
CITY = (CASE @ZTYPE WHEN 2 THEN @ZVALUE ELSE CITY END)
set @ZTYPE =1
set @ZVALUE=23
SELECT * FROM products
WHERE case @ZTYPE
when 1 then productid
when 2 then productname
end =@ZVALUE
declare @ZTYPE int,@ZVALUE varchar(30)
set @ZTYPE =2
set @ZVALUE='chai'
SELECT * FROM products
WHERE case @ZTYPE
when 1 then productid
when 2 then productname
end =@ZVALUE
我试了是可以,但是如果有一个条件我不想要,如何做,例如
declare @ZTYPE int,@ZVALUE varchar(30)
set @ZTYPE =2
set @ZVALUE='chai'
SELECT * FROM products
WHERE case @ZTYPE
when -1 (这种情况我不想生成条件,该如何做)
when 0 (这种情况我不想生成条件,该如何做)
when 1 then productid
when 2 then productname
end =@ZVALUE
SELECT * FROM CUSTOMERS WHERE
DEPT_CODE = (CASE @ZTYPE WHEN 1 THEN @ZVALUE ELSE DEPT_CODE END)
AND
CITY = (CASE @ZTYPE WHEN 2 THEN @ZVALUE ELSE CITY END)
==原因
case when ..then ..else ..end
要知道里面的必须要类型一致才可以。
也就是DEPT_CODE 与CITY字段,如果类型不一致,直接case when是不可以的。
那记得类型转换,不需要的条件直接删除就可以了
declare @ZTYPE int,@ZVALUE varchar(30)
set @ZTYPE =2
set @ZVALUE='chai'
SELECT * FROM products
WHERE case @ZTYPE
when 1 then convert(varchar,productid)
when 2 then productname
end =@ZVALUE
散分