CASE 具有两种格式:
Simple CASE function:
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[ ELSE else_result_expression ]
END Searched CASE function:
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[ ELSE else_result_expression ]
ENDTHEN result_expression 是当 input_expression = when_expression 计算结果为 TRUE,或者 Boolean_expression 计算结果为 TRUE 时返回的表达式。问题:这里的表达式input_expression 或Boolean_expression 是指的数据表中的某一列名吗?
如:
Select(case flag
when 0 then "0代表的意义"
when 1 then "1代表的意义"
else "2代表的意义"
end)flag from temp;但这个例子却是常数“1”啊,这是为什么:
select * from T_D where Issue in
(
select
case 1 when 1 then
(select Issue from T_D where Issue = (select min(Issue) from T_D where Issue > t1.Issue))
end
from T_D as t1 where Issue in (
select Issue from T_D
where (No1=2 and No2=4 and No3=6) or (No1=2 and No2=6 and No3=4) or
(No1=4 and No2=2 and No3=6) or (No1=4 and No2=6 and No3=2) or
(No1=6 and No2=4 and No3=2) or (No1=6 and No2=2 and No3=4)
)
)
Simple CASE function:
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[ ELSE else_result_expression ]
END Searched CASE function:
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[ ELSE else_result_expression ]
ENDTHEN result_expression 是当 input_expression = when_expression 计算结果为 TRUE,或者 Boolean_expression 计算结果为 TRUE 时返回的表达式。问题:这里的表达式input_expression 或Boolean_expression 是指的数据表中的某一列名吗?
如:
Select(case flag
when 0 then "0代表的意义"
when 1 then "1代表的意义"
else "2代表的意义"
end)flag from temp;但这个例子却是常数“1”啊,这是为什么:
select * from T_D where Issue in
(
select
case 1 when 1 then
(select Issue from T_D where Issue = (select min(Issue) from T_D where Issue > t1.Issue))
end
from T_D as t1 where Issue in (
select Issue from T_D
where (No1=2 and No2=4 and No3=6) or (No1=2 and No2=6 and No3=4) or
(No1=4 and No2=2 and No3=6) or (No1=4 and No2=6 and No3=2) or
(No1=6 and No2=4 and No3=2) or (No1=6 and No2=2 and No3=4)
)
)
when 0 then '男'
when 1 then '女'
else '不男不女'
http://topic.csdn.net/u/20080911/22/8b02ddc9-0a3c-497e-8a2d-7a1735bc116a.html
这个值是那里的值呢,数据表里面的吗?
是任意一列的数据值吗?还是特指的某一列?
那么这句:
select * from T_D where Issue in
(
select
case 1 when 1 then
(select Issue from T_D where Issue = (select min(Issue) from T_D where Issue > t1.Issue))
end
from T_D as t1 where Issue in (
select Issue from T_D
where (No1=2 and No2=4 and No3=6) or (No1=2 and No2=6 and No3=4) or
(No1=4 and No2=2 and No3=6) or (No1=4 and No2=6 and No3=2) or
(No1=6 and No2=4 and No3=2) or (No1=6 and No2=2 and No3=4)
)
) case 1 when 1 then ...表里面并没有1这个字段了,这怎么样理解?
表示查询所有行的记录都执行“then”后面的语句。
相当于:
if(true){...}
或case 2 when 2 then ...
这里的表达式代表一个值
Select(case flag
when 0 then "0代表的意义"
when 1 then "1代表的意义"
else "2代表的意义"
end)flag from temp;case flag表示字段flag =0如何;flag =1又如何。
相当于:
if(flag ==0){...}
if(flag ==1){...}
这里表达式flag 表示一个字段查询出来的值和when_expression 的值作对比
这里的表达式input_expression 或Boolean_expression 是指的数据表中的某一列名吗?列的数据所有行的数据, 有多少行就会判断多少次.