请写出SQL语句

onku

如图所示，根据两张表，得出结果表，请问sql怎样写？？？？


不用存储过程能实现么

hobbylu

select cname,count(*) from course a,xuanze b where a.cno=b.cno group by a.cno

onku

老大，这样好像不行吧

hobbylu

你试过没有,我已经得到正确的结果了

onku

select c.cname,sum(case when cno is not null then 1 else 0 end) as num from (
select b.cname,a.cno as cno from course b
left join xuanze a on a.cno=b.cno
)c
group by cname
order by num desc



或是
select b.cname,count(*) as cnt from xuanze a ,course b
where a.cno=b.cno
group by  b.cname
union
select cname,0 from course where cname not in
(
select b.cname from xuanze a ,course b
where a.cno=b.cno
)
order by cnt desc

hobbylu

呵呵,献丑了,非得要把0也选出来

onku

赫赫，我也是问别人的。