请教一个sql语句

[redcoconut]

雇员表emp（empid,empname）和客户表cust（custid,custname,empid）是一对多的关系，如果想按照雇员所拥有的客户数量对雇员信息排序，这个sql语句应该怎么写？

[真的不知道取什么名]

select empname,count(*) 
from emp,cust
where emp.empid=cust.empid
order by 2 --按照查询出来的第二列排序

[sh65489]

[Quote=引用 1 楼 zhuomingwang 的回复:]
SQL code
select empname,count(*)
from emp,cust
where emp.empid=cust.empid
order by 2 --按照查询出来的第二列排序
[/Quote]

少个group by

[luoyoumou]

select emp.empname, count(1) as nums
from emp, cust
where emp.empid=cust.empid
group by emp.empname
order by 2;

[陈字文]

select a.empname,a.empid
from emp a,cust b
where a.empid=b.empid
group by a.empname,a.empid
order by count(b.empid) desc;

[陈字文]

select a.empname,a.empid,count(1) as nums
from emp a,cust b
where a.empid=b.empid
group by a.empname,a.empid
order by count(b.empid) desc;

[lky19861123]

select emp.empid,
emp.empname,
(select count(empid) from cust group by empid) as custnum
from emp
where emp.empid =cust.empid
order by custnum;
这样还是可以吧

[陈字文]

select a.empname,a.empid
from emp a,cust b
where a.empid=b.empid
group by a.empname,a.empid
order by count(b.empid)
/*
SELECT STATEMENT, GOAL = ALL_ROWS			耗费=7	基数=4	字节=312	CPU 耗费=14566013	IO 耗费=4	时间=1
 SORT ORDER BY			耗费=7	基数=4	字节=312	CPU 耗费=14566013	IO 耗费=4	时间=1
  HASH GROUP BY			耗费=7	基数=4	字节=312	CPU 耗费=14566013	IO 耗费=4	时间=1
   HASH JOIN			耗费=5	基数=4	字节=312	CPU 耗费=2925893	IO 耗费=4	时间=1
    TABLE ACCESS FULL	对象所有者=SYS	对象名称=EMP	耗费=2	基数=2	字节=130	CPU 耗费=7461	IO 耗费=2	时间=1
    TABLE ACCESS FULL	对象所有者=SYS	对象名称=CUST	耗费=2	基数=4	字节=52	CPU 耗费=7881	IO 耗费=2	时间=1
*/

select a.empname,a.empid
from emp a,cust b
where a.empid=b.empid
group by a.empname,a.empid
order by count(1)

/*
SELECT STATEMENT, GOAL = ALL_ROWS			耗费=6	基数=4	字节=312	CPU 耗费=8745953	IO 耗费=4	时间=1
 SORT ORDER BY			耗费=6	基数=4	字节=312	CPU 耗费=8745953	IO 耗费=4	时间=1
  HASH GROUP BY			耗费=6	基数=4	字节=312	CPU 耗费=8745953	IO 耗费=4	时间=1
   HASH JOIN			耗费=5	基数=4	字节=312	CPU 耗费=2925893	IO 耗费=4	时间=1
    TABLE ACCESS FULL	对象所有者=SYS	对象名称=EMP	耗费=2	基数=2	字节=130	CPU 耗费=7461	IO 耗费=2	时间=1
    TABLE ACCESS FULL	对象所有者=SYS	对象名称=CUST	耗费=2	基数=4	字节=52	CPU 耗费=7881	IO 耗费=2	时间=1
*/

select emp.empname, count(1) as nums
from emp, cust
where emp.empid=cust.empid
group by emp.empname
order by 2

/*
SELECT STATEMENT, GOAL = ALL_ROWS			耗费=6	基数=4	字节=312	CPU 耗费=8745953	IO 耗费=4	时间=1
 SORT ORDER BY			耗费=6	基数=4	字节=312	CPU 耗费=8745953	IO 耗费=4	时间=1
  HASH GROUP BY			耗费=6	基数=4	字节=312	CPU 耗费=8745953	IO 耗费=4	时间=1
   HASH JOIN			耗费=5	基数=4	字节=312	CPU 耗费=2925893	IO 耗费=4	时间=1
    TABLE ACCESS FULL	对象所有者=SYS	对象名称=EMP	耗费=2	基数=2	字节=130	CPU 耗费=7461	IO 耗费=2	时间=1
    TABLE ACCESS FULL	对象所有者=SYS	对象名称=CUST	耗费=2	基数=4	字节=52	CPU 耗费=7881	IO 耗费=2	时间=1
*/