这个语句如何写

[dqj]

select visible,ydqx = case
when post_id =1 then 4
when post_id =2 then 4
when ui.Department_id=ar.Department_id then 3
---when user_id=10004 then 1
---when user_id <>10004 then 2
else 2 end from userinfo ui , article ar--
where id=4114 and user_id=10004
and ydqx >=visible

ydqx 是表中没有的列,在'and ydqx >=visible'中总是报:列名 'ydqx' 无效。

不知道该如何在where 后引用 ydqx

[dqj]

select visible,ydqx = case
when post_id =1 then 4
when post_id =2 then 4
when ui.Department_id=ar.Department_id then 3
else 2 end from userinfo ui , article ar--
where id=4114 and user_id=10004
and ydqx >=visible

ydqx 是表中没有的列,在'and ydqx >=visible'中总是报:列名 'ydqx' 无效。

不知道该如何在where 后引用 ydqx

[paoluo]

計算列不能直接拿來使用，必須寫全

select visible,ydqx = case
when post_id =1 then 4
when post_id =2 then 4
when ui.Department_id=ar.Department_id then 3
---when user_id=10004 then 1
---when user_id <>10004 then 2
else 2 end from userinfo ui , article ar--
where id=4114 and user_id=10004
and (case
when post_id =1 then 4
when post_id =2 then 4
when ui.Department_id=ar.Department_id then 3
---when user_id=10004 then 1
---when user_id <>10004 then 2
else 2 end) >=visible

[Limpire]

select * from

(select visible,ydqx = case
when post_id =1 then 4
when post_id =2 then 4
when ui.Department_id=ar.Department_id then 3
else 2 end from userinfo ui , article ar--
where id=4114 and user_id=10004) a

where ydqx >=visible

[paoluo]

或者使用子查詢


Select * From(
select visible,ydqx = case
when post_id =1 then 4
when post_id =2 then 4
when ui.Department_id=ar.Department_id then 3
else 2 end from userinfo ui , article ar--
where id=4114 and user_id=10004) A
Where ydqx >=visible

[paoluo]

...

[a2krh]

好像需要套一层子表
select a.*
from(
select visible,ydqx = case
when post_id =1 then 4
when post_id =2 then 4
when ui.Department_id=ar.Department_id then 3
---when user_id=10004 then 1
---when user_id <>10004 then 2
else 2 end from userinfo ui , article ar--
where id=4114 and user_id=10004
) a
where a.ydqx >= a.visible

[dqj]

谢了