以下操作都是在SQLServer2000的Northwind数据库中所涉及的表均在原有的数据库中
6、求出 1996 年中,最好卖的前10种产品的信息。(提示:年份OrderData在Orders表中,卖的数量在Order Details表中,产品的信息在product表中)
-------------------------------------------------------------------------------
7、在orders表中,求出freight总和最大的customers表的customers的姓名。用子查询语句完成(用子查询完成,并截图)这是我写的
Select contactname from customers
Where customerid in( select customerid from orders group by customerid having max(freight))Select contactname from customers
Where customerid exists (select customerid from orders group by customerid having max(freight) )
6、求出 1996 年中,最好卖的前10种产品的信息。(提示:年份OrderData在Orders表中,卖的数量在Order Details表中,产品的信息在product表中)
-------------------------------------------------------------------------------
7、在orders表中,求出freight总和最大的customers表的customers的姓名。用子查询语句完成(用子查询完成,并截图)这是我写的
Select contactname from customers
Where customerid in( select customerid from orders group by customerid having max(freight))Select contactname from customers
Where customerid exists (select customerid from orders group by customerid having max(freight) )
解决方案 »
- 为什么我的网站用电脑打开很卡,但回收下进程池就能打开?
- 为什么自定义函数的参数为"0"带头的话,会过去就不带"0"了呢?
- mysql问题
- 从2000万条数据中提取50万条数据,大家的程序是怎么提取数据,会不会连接超时
- 请问查看一个表的结构用什么命令?
- 请问:求出最近日期的sql语句?
- 如何在不安装sql sever2000下,使用像管家婆这样需要sql数据库的软件?安装msde2000吗?
- SQL语句更改关键字段的数据类型
- 请问SQL SERVER可以支持的最大连接数是多少?
- 如何实现商品进出库管理?软件和硬件。谢谢。
- 如何将一对多关系表,查询一表把多表字段以逗号隔开显示?
- 语句优化问题,现在速度慢
SELECT TOP 10 SUM(QTY) AS SUMQTY,product FROM OrderDetails GROUP BY product ORDER BY SUMQTY DESC手頭上沒有Northwind庫 將就看
from [Order Details] a left join products b on a.productid=b.productid
left join orders c on a.orderid=c.orderid
where year(c.orderdate)='1996'
order by a.quantity descquantity productname
-------- ----------------------------------------
120 Pâté chinois
100 Steeleye Stout
80 Teatime Chocolate Biscuits
80 Camembert Pierrot
80 Escargots de Bourgogne
80 Vegie-spread
77 Gula Malacca
70 Northwoods Cranberry Sauce
70 Northwoods Cranberry Sauce
70 Gorgonzola Telino(所影响的行数为 10 行)
from customers a
where customerid =( select customerid
from orders
where freight=( select max(freight)
from orders))companyname
----------------------------------------
QUICK-Stop(所影响的行数为 1 行)
6。select productname from products
where productid in (
select top 10 productid from [Order Details] where orderid in
(select orderid from orders where datepart(yy,OrderDate)=1996)
group by productid order by count(1) desc)7。select CompanyName from customers where customerid in
(select top 1 customerid from orders group by customerid order by sum(freight) desc)
6的结果:
Gorgonzola Telino
Mozzarella di Giovanni
Guaraná Fantástica
Camembert Pierrot
Tarte au sucre
Flotemysost
Raclette Courdavault
Tourtière
Scottish Longbreads
Chang7的结果:
Save-a-lot Markets