求一sql语句！

[dongcenphoenix]

在一个table中有field1，field2，field3，用户输入一个数据a，查询的时候当field1不为null时就以field1=a为查询条件，否则当field1为null，field2不为null就以field2=a为查询条件，最后当filed1,field2都为null时就用field3=a为查询条件。

[沝林]

用COALESCE()函数就可以了：
select * from table where COALESCE(field1,field2,field3) = a；

COALESCE (expr1, expr2, ..., exprn)相当于
CASE WHEN expr1 IS NOT NULL THEN expr1
ELSE COALESCE (expr2, ..., exprn) END

[heyixiang]

mark

[njhart2003]

select * from table
where nvl(field1,nvl(field2,field3))=a;

[waterfirer]

select * from tablename where (field1=a and field1 is not null) or (field1 is null and field2=a) or (field1 is null and field2 is null and field3=a)

[waterfirer]

还是njhart2003()的简单