分数 20
全屏浏览题目
切换布局
作者 柯海丰
单位 浙大城市学院
查找订单数最多的员工信息,显示为:姓(LastName)
,名(FirstName)
和职务(Title)
提示:请使用SELECT语句作答。
表结构:
employees
表:
列名 | 数据类型 | 长度 | 主码 | 说明 |
---|---|---|---|---|
EmployeeID | int | 11 | √ | 员工编号 |
LastName | varchar | 20 | 姓 | |
FirstName | varchar | 10 | 名 | |
Title | varchar | 30 | 职务 | |
TitleOfCourtesy | varchar | 25 | 礼貌称号 | |
BirthDate | datetime, | 出生日期 | ||
HireDate | datetime, | 入职日期 | ||
Address | varchar | 60 | 地址 | |
City | varchar | 15 | 城市 | |
Region | varchar | 15 | 区域 | |
PostalCode | varchar | 10 | 邮政编码 | |
Country | varchar | 15 | 国家 | |
HomePhone | varchar | 24 | 住宅电话 | |
Extension | varchar | 4 | 范围 | |
Photo | longblob, | 照片 | ||
ReportsTo | int | 11 | 直接上级 | |
PhotoPath | varchar | 255 | 照片 |
orders
表:
列名 | 数据类型 | 长度 | 主码 | 说明 |
---|---|---|---|---|
OrderID | int | 11 | √ | 订单编号 |
CustomerID | varchar | 5 | 顾客编号 | |
EmployeeID | int | 11 | 员工编号 | |
OrderDate | datetime, | 订购日期 | ||
RequiredDate | datetime, | 预计到达日期 | ||
ShippedDate | datetime, | 发货日期 | ||
ShipVia | int | 11 | 运货商 | |
Freight | decimal | 10,4 | 运费 | |
ShipName | varchar | 40 | 货主姓名 | |
ShipAddress | varchar | 60 | 货主地址 | |
ShipCity | varchar | 15 | 所在城市 | |
ShipRegion | varchar | 15 | 区域 | |
ShipPostalCode | varchar | 10 | 邮政编码 | |
ShipCountry | varchar | 15 | 所在国家 |
表样例
employees
表:
EmployeeID | LastName | FirstName | Title | TitleOfCourtesy | BirthDate | HireDate | Address | City | Region | PostalCode | Country | HomePhone | Extension | ReportsTo |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Davolio | Nancy | Sales Representative | Ms. | 1948-12-08 00:00:00 | 1992-05-01 00:00:00 | 507 - 20th Ave. E.Apt. 2A | Seattle | WA | 98122 | USA | (206) 555-9857 | 5467 | 2 |
2 | Fuller | Andrew | Vice President, Sales | Dr. | 1952-02-19 00:00:00 | 1992-08-14 00:00:00 | 908 W. Capital Way | Tacoma | WA | 98401 | USA | (206) 555-9482 | 3457 | |
3 | Leverling | Janet | Sales Representative | Ms. | 1963-08-30 00:00:00 | 1992-04-01 00:00:00 | 722 Moss Bay Blvd. | Kirkland | WA | 98033 | USA | (206) 555-3412 | 3355 | 2 |
4 | Peacock | Margaret | Sales Representative | Mrs. | 1937-09-19 00:00:00 | 1993-05-03 00:00:00 | 4110 Old Redmond Rd. | Redmond | WA | 98052 | USA | (206) 555-8122 | 5176 | 2 |
5 | Buchanan | Steven | Sales Manager | Mr. | 1955-03-04 00:00:00 | 1993-10-17 00:00:00 | 14 Garrett Hill | London | SW1 8JR | UK | (71) 555-4848 | 3453 | 2 |
orders
表:
OrderID | CustomerID | EmployeeID | OrderDate | RequiredDate | ShippedDate | ShipVia | Freight | ShipName | ShipAddress | ShipCity | ShipRegion | ShipPostalCode | ShipCountry |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
10248 | VINET | 5 | 1996-07-04 00:00:00 | 1996-08-01 00:00:00 | 1996-07-16 00:00:00 | 3 | 32.38 | Vins et alcools Chevalier | 59 rue de l-Abbaye | Reims | 51100 | France | |
10250 | HANAR | 4 | 1996-07-08 00:00:00 | 1996-08-05 00:00:00 | 1996-07-12 00:00:00 | 2 | 65.83 | Hanari Carnes | Rua do Pao, 67 | Rio de Janeiro | RJ | 05454-876 | Brazil |
10251 | VICTE | 3 | 1996-07-08 00:00:00 | 1996-08-05 00:00:00 | 1996-07-15 00:00:00 | 1 | 41.34 | Victuailles en stock | 2, rue du Commerce | Lyon | 69004 | France | |
10252 | SUPRD | 4 | 1996-07-09 00:00:00 | 1996-08-06 00:00:00 | 1996-07-11 00:00:00 | 2 | 51.3 | Suprmes dlices | Boulevard Tirou, 255 | Charleroi | B-6000 | Belgium | |
10253 | HANAR | 3 | 1996-07-10 00:00:00 | 1996-07-24 00:00:00 | 1996-07-16 00:00:00 | 2 | 58.17 | Hanari Carnes | Rua do Pao, 67 | Rio de Janeiro | RJ | 05454-876 | Brazil |
10254 | CHOPS | 5 | 1996-07-11 00:00:00 | 1996-08-08 00:00:00 | 1996-07-23 00:00:00 | 2 | 22.98 | Chop-suey Chinese | Hauptstr. 31 | Bern | 3012 | Switzerland | |
10256 | WELLI | 3 | 1996-07-15 00:00:00 | 1996-08-12 00:00:00 | 1996-07-17 00:00:00 | 2 | 13.97 | Wellington Importadora | Rua do Mercado, 12 | Resende | SP | 08737-363 | Brazil |
10257 | HILAA | 4 | 1996-07-16 00:00:00 | 1996-08-13 00:00:00 | 1996-07-22 00:00:00 | 3 | 81.91 | HILARION-Abastos | Carrera 22 con Ave. Carlos Soublette #8-35 | San Cristbal | Tchira | 5022 | Venezuela |
10258 | ERNSH | 1 | 1996-07-17 00:00:00 | 1996-08-14 00:00:00 | 1996-07-23 00:00:00 | 1 | 140.51 | Ernst Handel | Kirchgasse 6 | Graz | 8010 | Austria | |
10259 | CENTC | 4 | 1996-07-18 00:00:00 | 1996-08-15 00:00:00 | 1996-07-25 00:00:00 | 3 | 3.25 | Centro comercial Moctezuma | Sierras de Granada 9993 | Mxico D.F. | 5022 | Mexico |
输出样例:
LastName | FirstName | Title |
---|---|---|
Peacock | Margaret | Sales Representative |
select LastName,FirstName,Title
from employees
join orders on orders.EmployeeID=employees.EmployeeID
group by LastName,FirstName,Title
having count(orders.EmployeeID)
order by count(orders.EmployeeID) desc
limit 1