这样修改下SQL> SQL> with K_YFKC as 2 ( 3 select '[2011]001号' cl,1 no from dual union all 4 select '[2011]003号' cl,2 no from dual union all 5 select '[2011]002号' cl,4 no from dual union all 6 select '[2011]001号' cl,7 no from dual 7 ) 8 SELECT max(no) as num 9 FROM K_YFKC 10 where CL like '['||to_char(sysdate,'yyyy')||']%号' 11 ; NUM ---------- 7SQL>
where CL like '[(select to_char(sysdate,'yyyy')from dual)]%号' 这儿不对,改成下面试试, where CL like '['||(select to_char(sysdate,'yyyy')from dual)||']___号'
改了一下,试一下SELECT max(no) as num FROM K_YFKC where CL like '[' || to_char(sysdate,'yyyy') || ']%';
用动态sql吧
一个查询语句放到''中,已经成了一个字符串
SQL> with K_YFKC as
2 (
3 select '[2011]001号' cl,1 no from dual union all
4 select '[2011]003号' cl,2 no from dual union all
5 select '[2011]002号' cl,4 no from dual union all
6 select '[2011]001号' cl,7 no from dual
7 )
8 SELECT max(no) as num
9 FROM K_YFKC
10 where CL like '['||to_char(sysdate,'yyyy')||']%号'
11 ; NUM
----------
7SQL>
这儿不对,改成下面试试,
where CL like '['||(select to_char(sysdate,'yyyy')from dual)||']___号'
max(no) as num
FROM K_YFKC
where CL like '[' || to_char(sysdate,'yyyy') || ']%';
字符串 + 表达式 时,必须使用连接符拼接,oracle 中的连接符为 ||
因此结果 => 'zifu' || to_char(number)