一个简单的SQL,快来抢分啊
大家看看下面两个sql有没有错误
1、sql= "select * from (select * from (select * from (
select cname,csex
from Net_FY_Company order by cname) where rownum <5*2) order by cname desc) where rownum <5 "
2、sql= "select * from (select * from (select * from (
select cname as 姓名,csex as 性别
from Net_FY_Company order by cname) where rownum <5*2) order by cname desc) where rownum <5 "
如果两个都是正确的，下面的代码又错在哪里，请大家帮我分析分析
<%
'asp代码
'如果此处是上面的第一句SQL，没问题
'如果是第二句SQL,就出错
xx= "select * from (select * from (select cname from (select cname from Net_FY_Company order by cname) where rownum <5*2) order by cname desc) where rownum <5 "
response.ContentType= "application/vnd.ms-excel "
set rs=conn.execute(xx)
%>
<table border= "1 " cellspacing= "0 ">
<tr>
<%
for i=0 to rs.fields.count-1
%>
<td width= "50 " align= "center " bgcolor= "#FFFFCC "> <%=rs(i).name%> </td>
<%next%>
</tr>
<%do while not rs.eof%>
<tr>
<%for i=0 to rs.fields.count-1%>
<td align= "center " > <%=rs(i)%> </td>
<%next%>
</tr>
<%
rs.movenext
loop
rs.close
%>
</table>
[解决办法]
select * from (
select * from (
select * from (
select cname as 姓名,csex as 性别
from Net_FY_Company order by cname
)
where rownum <5*2
)
order by cname desc//order by 姓名 desc
)
where rownum <5
估计是这个吧!
[解决办法]
不知是不是要加表別名
sql= "select * from (select * from (select * from (
select cname as 姓名,csex as 性别
from Net_FY_Company order by cname)t where rownum <5*2) tt order by cname desc) ttt where rownum <5 "
[解决办法]
不知道你要的结果是什么
create table Net_FY_Company (cname varchar2(100),csex varchar2(10));
insert into Net_FY_Company
select 'aa ', 'boy ' from dual
union all
select 'bb ', 'boy ' from dual
union all
select 'cc ', 'boy ' from dual
union all
select 'dd ', 'girl ' from dual
union all
select 'ee ', 'girl ' from dual
union all
select 'ff ', 'boy ' from dual
union all
select 'gg ', 'boy ' from dual
union all
select 'hh ', 'boy ' from dual
union all
select '1 ', 'boy ' from dual
union all
select '2 ', 'boy ' from dual
union all
select '3 ', 'boy ' from dual
union all
select '4 ', 'boy ' from dual
union all
select '5 ', 'boy ' from dual
union all
select '6 ', 'boy ' from dual
union all
select '7 ', 'boy ' from dual
union all
select '8 ', 'boy ' from dual
union all
select '9 ', 'boy ' from dual;
select * from (select * from (select * from (
select cname,csex
from Net_FY_Company order by cname) where rownum <5*2) order by cname desc) where rownum <5
--Result
19boy
28boy
37boy
46boy
[解决办法]
sql= "select * from (select * from (select * from (
select cname as 姓名,csex as 性别
from Net_FY_Company order by cname) where rownum <5*2) order by cname desc) where rownum <5 "
cname as 姓名,csex as 性别
已经符别名了,后面的order by cname 要改成order by 姓名
[解决办法]
select * from (select * from (select * from (
select cname as 姓名,csex as 性别
from Net_FY_Company order by 姓名) where rownum <5*2) order by 姓名 desc) where rownum <5