northwind 中查询 哪个国家供货商提供的商品种类最多?
表suppliers中country字段 suppliers表与products表中supplierid关联
我写的代码select top 1* from (select country ,count(productname) as num
from suppliers as s inner join products as p on s.supplierid=p.supplierid
group by country
order by num)
错误信息 消息 1033,级别 15,状态 1,第 4 行
除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
表suppliers中country字段 suppliers表与products表中supplierid关联
我写的代码select top 1* from (select country ,count(productname) as num
from suppliers as s inner join products as p on s.supplierid=p.supplierid
group by country
order by num)
错误信息 消息 1033,级别 15,状态 1,第 4 行
除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
from suppliers as s inner join products as p on s.supplierid=p.supplierid
group by country order by num desc
/*
country num
--------------- -----------
USA 12(所影响的行数为 1 行)
*/
from suppliers as s inner join products as p on s.supplierid=p.supplierid
group by country
order by num
from suppliers as s inner join products as p on s.supplierid=p.supplierid
group by country
) order by num
select top 1 * from (select country ,count(productname) as num
from suppliers as s inner join products as p on s.supplierid=p.supplierid
group by country
) a
order by num desc
--或
select top 1 * from (select top 100 percent country ,count(productname) as num
from suppliers as s inner join products as p on s.supplierid=p.supplierid
group by country
order by num desc) a