select * from enterprise
(select * from enterprise)
以上两句都正确,而且结果集也一样,
但是下面的:
(select * from enterprise order by en_id)
就是错误的,错误信息如下:
SQLWKS> (select * from enterprise order by en_id)
(select * from enterprise order by en_id)
*
ORA-00907: 缺少右括号
(select * from enterprise)
以上两句都正确,而且结果集也一样,
但是下面的:
(select * from enterprise order by en_id)
就是错误的,错误信息如下:
SQLWKS> (select * from enterprise order by en_id)
(select * from enterprise order by en_id)
*
ORA-00907: 缺少右括号
SQL>select rownum 名次, x.n,x.s
SQL>from (select sum(s.sale_money) s,e.en_name n from en_sale s,enterprise e
SQL>where s.en_id=e.en_id
SQL>group by e.en_name) x --order by sum(s.sale_money)
SQL>order by 名次;
where s.en_id=e.en_id
group by e.en_name
) x order by x.s;
看看这样行吗??
您写的和我的执行结果是一样的。
TO:welyngj(平平淡淡)
我也知道不可以,可是现在没有办法了。您有什么高招嘛?
where s.en_id=e.en_id
group by e.en_name
) x order by x.s desc;
再试试!!
这种逻辑诚然正确,可惜,第一名在最后,名次和金额都时倒着排的,习惯上排名都是把最多的排第一位。
To :likefox(狐妖)
名次 N S
-----------------------------------------------------
2 吉林省绿源有机食品有限责任公 12
1 吉林省冰冬食品股份有限公司 6
这是你的语句的效果
where s.en_id=e.en_id
group by e.en_name
) x order by x.s desc) t;
这次对了!!再试试!!
where s.en_id=e.en_id
group by e.en_name
) x order by x.s desc) t;
不好意思!!
最后哪个是错误的。子查询中不可以用ORDRE。
TO : baojianjun(包子)
可否做个示范 谢谢
SQL>from (select sum(a.sale_money) s,e.en_name n from en_sale a,enterprise e
SQL>where a.en_id=e.en_id
SQL>group by e.en_name
SQL>order by a.s desc) x --order by sum(s.sale_money)
select row_number() over(order by s desc),x.n,x.s from
(select sum(a.sal_money) s,e.en_name n from en_sale a,enterprise e
where a.en_id=e.en_id group by e.en_name) x;
不支持子查询中有order by,我的是9.2的环境,是可以的。
1 select rownum rn, x.n,x.s from (select sum(s.sal_money) s,e.en_name n from en_sale s,enterprise e
2 where s.en_id=e.en_id
3 group by e.en_name order by sum(s.sal_money) desc
4* ) x,dual order by rn
SQL> / RN N S
---------- -------------------- ----------
1 sdldlsflsdfldls 12
2 dsddsdsdsds 6
还有就是group by 有排序的功能,是按en_name做升序排列的。
这样就麻烦了。
where s.en_id=e.en_id
group by e.en_name) a order by s desc
from (select * from (select sum(s.sale_money) s,e.en_name n from en_sale s,enterprise e
where s.en_id=e.en_id
group by e.en_name) order by s desc) a
试一下
from (select sum(s.sale_money) s,e.en_name n
from en_sale s,enterprise e
where s.en_id=e.en_id
group by e.en_name
) x order by x.s desc;
没有启动数据库,没有试过,楼主自己试一试。括号什么的自己调一调,应该可以。
用过程
create or replace procedure pro
as
i number;
cursor cur is select sum(s.sale_money) s,e.en_name c from en_sale s,enterprise e
where s.en_id=e.en_id
group by e.en_name
order by s desc;
begin
i:=1;
for currec in cur loop
dbms_output.put_line('名次'||' '||'公司'||' '||'销售额');
dbms_output.put_line(i||' '||currec.c||' '||currec.s);
end loop;
end pro;
你试试,我还没有调试.
i:=i+1;
就是让i的植加一;
可是我有个问题,您的过程的确没有问题,只是看不到结果
直接键入:pro 吗?
好菜的问题.. .. ..
不知道你是哪一版本。如果不行,试试下面这条语句,应该可以:
select rownum , n, -s1 from (
select -s s1,n from (
select b.en_name n,sum(a.sale_money) s from en_sale a,enterprise b
where a.en_id=b.en_id
group by b.en_name
) group by -s,n
);
因为group by有默认的排序功能,所以最里面那个group by中的结果是按名字排序的,
外面那个group by出的结果才是按总钱数排序,用负的s可以使它降序排列。
于是最终出来的结果就总钱数和名次就正确了。do you understand?
as
i number;
cursor cur is select sum(s.sale_money) s,e.en_name c from en_sale s,enterprise e
where s.en_id=e.en_id
group by e.en_name
order by s desc;
begin
i:=1;
for currec in cur loop
dbms_output.put_line('名次'||' '||'公司'||' '||'销售额');
dbms_output.put_line(i||' '||currec.c||' '||currec.s);
i:i+1;
end loop;
end pro;
运行这个过程你键入如下;
execute pro
即可;在sql*plus中;不过,你应该用字符串函数rpad()使报表好看一些!
应该是i=i+1;
sorry
如果真错了,那我无话可说; 但要是对了,就是你对的我的劳动成果不尊重!select rownum , n, -s1 from (
select -s s1,n from (
select b.en_name n,sum(a.sale_money) s from en_sale a,enterprise b
where a.en_id=b.en_id
group by b.en_name
) group by -s,n
);