北京
乾学院 1061 号会员
2 回帖  •  782 浏览  • 
     ...

程序设计习题 第 10 章 找关联

 

10.1 主键

1.有销售数据如下：

ID Name Sales
010010001 Tom 7,588,420
010010002 John 8,953,172
010010003 Joan 6,276,185
020010004 Rocky 1,281,280
020010005 Ham 2,686,043
020010006 Kate 5,851,210
020010007 Rose 292,254
030020008 Nomy 8,216,267
030020009 Neil 5,143,192

（1）将ID设为主键

（2）查询第2条记录的主键值

（3）为主键建立索引

（4）查找主键值为10010002,20010004,30020008的记录

（5）删除主键

（6）将ID和Name设为主键

（7）查找主键值20010005,"Ham"的记录

10.2 外键

1. 有表格Person和Address如下

表Age

personId lastName firstName
1 Wang Allen
2 Alice Bob

personId 是该表的主键

该表包含一些人的 ID 和他们的姓和名的信息。

表Address

addressId personId city state
1 2 New York New York
2 3 Los Angeles California

addressId 是该表的主键

该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息

（1）使用 switch函数， 以Address为事实表，将外键字段转成对应的维表记录，对应记录不存在时置为 null。

（2）将维表记录中的lastName添加到表Address中，效果如下

..

2. 有某产品的Customers表和Orders表，找出购买过产品的客户名单（用switch和join两种方法实现）

表Customers

id name
1 Joe
2 Henry
3 Sam
4 Max

表Orders

id customerId amount
1 3 787
2 1 675
3 3 203

3. 在第2题的Customers和Orders表格中找出未购买过产品的客户名单

4. 有员工薪资表和部门表如下，求各部门工资最高的员工及其工资

Employee

id name salary departmentId
1 Joe 70000 1
2 Henry 90000 1
3 Sam 80000 2
4 Max 60000 2
5 Jim 90000 1

Department

id name
1 IT
2 Sales

注意，工资最高的员工可能不止一个

5. 有客户订单表和订单明细表如下，

Orders

OrderID Customer Area Orderdate
1001 Tom west 2020/2/5
1002 Jack south 2020/2/5
1003 Tim east 2020/2/5
1004 Rose north 2020/2/6
1005 Kate east 2020/2/6
1006 John south 2020/2/7
1007 Horn north 2020/2/7

Order_detail

OrderID Index Product Price Quantity
1001 1 p1 12.5 32
1001 2 p2 14.3 55
1001 3 p3 15.7 14
1002 1 p1 12.5 10
1002 2 p3 15.7 8
1003 1 p1 12.5 36
1003 2 p4 8.8 15
1003 3 p6 62.3 85
1004 1 p2 14.3 24
1005 1 p3 15.7 12
1005 2 p4 8.8 27
1006 1 p1 12.5 18
1006 2 p4 8.8 92
1006 3 p6 62.3 10
1007 1 p3 15.7 6
1007 2 p5 24.8 8

（1）请查出订单总金额大于 1000 的客户订单信息。

提示：一份订单包含多种产品，订单总金额等于订单内所有产品金额的总和。

结果示例：

..

（2）查出 north 地区的客户订单明细信息。

结果示例：

..

6. 有运费标准表和运输订单表如下

Freight

City First1KG Add1KG
Alabama 12 3
Alaska 11 5
Arizona 11 5
Arkansas 10 5
Boston 12 3
California 10 4
Colorado 10 4
Connecticut 12 5

Orders

OID City WeightKG
100001 Arizona 15
100002 Arkansas 13
100003 Boston 11
100004 Colorado 3
100005 Connecticut 2.5
100006 Arizona 8
100007 Alabama 3.6
100008 Alaska 22
100009 California 19

根据运费标准表，求实际运费。

提示：实际运费 = 首重运费 + 超出重量 * 超重单价，其中不满 1KG 的按 1Kg 计算

7. 有员工就餐数据和餐费标准如下

Meal

Name Mealtype
Tom breakfast
John lunch
Joan supper
Rocky breakfast
Ham lunch
Kate supper
Rose breakfast
Nomy lunch
Neil supper
Jack breakfast
Joe lunch
Peter supper
Sunny breakfast
Tiger lunch
Alice breakfast
Cindy lunch
Leon supper
Lily breakfast
Kevin lunch
Shelly supper
Panzy breakfast
Maggie lunch
Mark supper
Aileen breakfast

Charge

Mealtype Mealcharge
breakfast 10
lunch 15
supper 20

计算Meal表格中每个员工的餐费

8. 区间关联

有数量表和价格标准表，计算数量表中对应的价格

Quantity

Quantity
221
87
33
73
162
227
403
288
78
213
374
152

Price

StartQuantity EndQuantity Price
0 50 15
50 100 13.75
100 300 13
300 500 12.5

提示：segp()为pseg()的选出函数，根据区段号返回对应的成员

结果示例：

..

10.3 归并

1. 有 2018、2019 年的销售订单表数据如下，两个 Sheet 有相同的列结构：

2018

OrderID CustomerID ProductID OrderDate Amount
10248 VINET 11 2018-07-04 168
10248 VINET 42 2018-07-04 98
10248 VINET 72 2018-07-04 174
10249 TOMSP 14 2018-07-05 167.4
10249 TOMSP 51 2018-07-05 1696
10250 HANAR 41 2018-07-08 77
10250 HANAR 51 2018-07-08 1484
10250 HANAR 65 2018-07-08 252
10251 VICTE 22 2018-07-08 100.8
10251 VICTE 57 2018-07-08 234
10251 VICTE 65 2018-07-08 336
10252 SUPRD 20 2018-07-09 2592
10252 SUPRD 33 2018-07-09 50
10252 SUPRD 60 2018-07-09 1088
10253 HANAR 31 2018-07-10 200

2019

OrderID CustomerID ProductID OrderDate Amount
10400 EASTC 29 2019-01-01 2079
10400 EASTC 35 2019-01-01 504
10400 EASTC 49 2019-01-01 480
10401 RATTC 30 2019-01-01 372.6
10401 RATTC 56 2019-01-01 2128
10401 RATTC 65 2019-01-01 336
10401 RATTC 71 2019-01-01 1032
10402 ERNSH 23 2019-01-02 432
10402 VINET 72 2019-01-02 2281.5
10403 ERNSH 16 2019-01-03 291.9
10403 ERNSH 48 2019-01-03 714
10404 HANAR 65 2019-01-03 747
10404 MAGAA 42 2019-01-03 448
10404 MAGAA 49 2019-01-03 480
10405 LINOD 3 2019-01-06 400

（1）找出这两年同一客户（CustomerID）购买同一产品（ProductID）的订单

（2）将两年的订单合并到一张表，并统计每个客户的总金额

（3）找出 2019 年新增客户的订单情况：

（4）找出 2019 年所有流失的客户名单 CustomerID

2. 有一月份和二月份销售额排名前十的产品名称和销售员姓名

Jan

ProductName Name
Sasquatch Ale Sunny
Steeleye Stout Mark
Aniseed Syrup Peter
Chef Anton's Cajun Seasoning Sunny
Chef Anton's Gumbo Mix Mark
Genen Shouyu Mark
Grandma's Boysenberry Spread Sunny
Gula Malacca Sunny
Louisiana Fiery Hot Pepper Sauce Peter
Louisiana Hot Spiced Okra Peter

Feb

ProductName Name
Northwoods Cranberry Sauce Mark
Original Frankfurter grüne So?e Peter
Chef Anton's Cajun Seasoning Ben
Genen Shouyu Mark
Louisiana Fiery Hot Pepper Sauce Peter
Sasquatch Ale John
Valkoinen suklaa Mark
Zaanse koeken Peter
Camembert Pierrot Sunny
Flotemysost Mark

（1）以ProductName为关键列，找出一二月份均进入前十的产品数据（销售员姓名列出一月份的即可）

（2）以ProductName为关键列，找出一、二月份有一次以上进入前十的产品数据

（3）以ProductName为关键列，找出一月份进了前十但是二月份没有进前十的产品销售数据

（4）基于整行数据对比，找出一二月份均进入前十的产品及销售员的数据

3. 多个表格归并

有多个月份的销售数据top10Sales如下，并且随着时间的推移，月数还会增加。

top10Sales.xlsx

..

求这几个月全部进了前十的产品名单

4. 某商家根据销售渠道不同，销售记录分别存储在线上和实体店两个表。有时线上线下同时搞活动，部分销售记录被同时存储在两个表中。销售表结构相同，如下：

online

OrderID Customer SellerId OrderDate Amount
10400 EASTC 1 2014/1/1 3063
10401 HANAR 1 2014/1/1 3868.6
10402 ERNSH 8 2014/1/2 2713.5
10403 ERNSH 4 2014/1/3 1005.9
10404 MAGAA 2 2014/1/3 1675

store

OrderID Customer SellerId OrderDate Amount
10402 ERNSH 8 2014/1/2 2713.5
10403 ERNSH 4 2014/1/3 1005.9
10405 TOMSP 5 2014/1/3 2592
10406 VICTE 3 2014/1/4 252
10407 SUPRD 2 2014/1/5 100.8

（1）请汇总线上和线下的订单数据，并计算商家实际的总销售额

（2）查找线上线下重复保存的销售记录

5. 根据版本不同的两个交易信息储存文件 old.xlsx 和 new.xlsx，分别找出新增的、删除的和修改的记录。

old.xlsx:

UserName Date SaleValue SaleCount
Rachel 2015/3/1 4500 9
Rachel 2015/3/3 8700 4
Tom 2015/3/2 3000 8
Tom 2015/3/3 5000 7
Tom 2015/3/4 6000 12
John 2015/3/2 4000 3
John 2015/3/2 4300 9
John 2015/3/4 4800 4

new.xlsx

UserName Date SaleValue SaleCount
Rachel 2015/3/1 4500 9
Rachel 2015/3/2 5000 5
Ashley 2015/3/1 6000 5
Rachel 2015/3/3 11700 4
Tom 2015/3/3 5000 7
Tom 2015/3/4 6000 12
John 2015/3/2 4000 3
John 2015/3/2 4300 9
John 2015/3/4 4800 4

10.4 连接

1. 有某产品的Customers表和Orders表

id name
1 Joe
2 Henry
3 Sam
4 Max

表Orders

id customerId amount
1 3 787
2 1 675
3 3 203

（1）找出购买过产品的客户姓名和其消费总额

（2）找出未购买过产品的客户名单

2. 有两张表格，分别为某些肉类产品的价格表和库存表，现需要将两张表格横向连接到一起。

Meats.xlsx

Name UnitPrice
Mutton 7.69
Pork 4.58
Chicken 5.77
Beef 7.96

MeatStock.xlsx

Name Stock MinimumStock
Mutton 5000 3000
Chicken 4000 3000
Duck 2500 2000
Beef 3600 2000

（1）按照Name进行连接，连接后保留所有行数据

..

（2）按照Name连接，只保留两个文件中都有的行

..

（3）按照Name连接，保留第一个文件的行

..

3. 如下两个表格，按照Region和Name两个字段连接，保留第一个文件的行

MeatsRegion.xlsx

Region Name UnitPrice
A Mutton 7.69
A Pork 4.58
A Chicken 5.77
A Beef 7.96
B Mutton 6.32
B Pork 4.13
B Chicken 6.33

MeatRegionStock.xlsx

Region Name Stock MinimumStock
A Mutton 500 3000
A Chicken 4000 3000
A Duck 2500 2000
A Beef 3600 2000
B Mutton 2000 1500
B Pork 600 300
B Beef 3200 2000

连接后效果：

..

4. 有两张表格

Types.xlsx

Type Description
Fruits Edible plant fruit which is succulent and tastes mainly sweet and sour
Meats Edible subcutaneous tissue and muscle of animals

Foods.xlsx

Type Name UnitPrice
Fruits Apple 1.69
Fruits Banana 0.69
Fruits Peach 0.88
Fruits Strawberry 1.97
Meats Mutton 7.69
Meats Pork 4.58
Meats Chicken 5.77
Meats Duck 6.89
Meats Beef 7.96

按照Type连接，将Description添加到每种 food 的后面

连接后效果：

..

5. 根据社区人员表和年龄分段表，查询社区居民所处的年龄段。（用xjoin()实现）

Community:

ID Name Age
1 David 28
2 Daniel 15
3 Andrew 65
4 Rudy

Age:

Group Start End
Children 0 15
Youth 16 40
Middle 41 60
Old 61 100

查询效果：

..

参考答案

10.1 主键

1.


 A
1 =T("Sales.xlsx")
2 =A1.keys(ID)
3 =A1(2).key()
4 >A1.index()
5 =A1.find(10010002,20010004,30020008)
6 =A1.keys()
7 =A1.keys(ID,Name)
8 =A1.find([20010005,"Ham"])

10.2 外键

1.


 A
1 =T("Person.xlsx").keys(personId)
2 =T("Address.xlsx").keys(addressId)
3 >A2.switch(personId,A1)
4 =A2.new(addressId,personId.lastName:lastName,city,state)

2.


 A
1 =T("Customers.xlsx").keys(id)
2 =T("Orders").keys(id)
3 >A2.switch(customerId,A1)
4 =A2.(customerId.name).id()


A
1 =T("Customers.xlsx").keys(id)
2 =T("Orders").keys(id)
3 =A2.join(customerId,A1,name).(name).id()

3.


 A
1 =T("Customers.xlsx").keys(id)
2 =T("Orders").keys(id)
3 =A2.group(customerId;)
4 =A1.join@d(id,A3:customerId)

4.


 A
1 =T("Employee").keys(id)
2 =T("Department").keys(id)
3 =A1.join(departmentId,A2,name:deptname)
4 =A3.group(deptname;~.maxp@a(salary).(name):name,~.max(salary):salary)

5.

（1）


 A
1 =T(“Orders.xlsx”)
2 =T(“Order_detail.xlsx”)
3 =A2.groups(OrderID;sum(Price*Quantity):Amount).select(Amount>1000)
4 =A1.join@i(OrderID,A3:OrderID)

（2）


 A
1 =T(“Orders.xlsx”)
2 =T(“Order_detail.xlsx”)
3 =A1.select(Area==“north”)
4 =A2.join@i(OrderID,A3:OrderID)

6.


 A
1 =T(“Freight.xlsx”)
2 =T(“Orders.xlsx”)
3 =A2.join(City,A1:City,First1KG+(ceil(WeightKG)-1)*Add1KG:Fee)

7.


 A
1 =T(“Meal.xlsx”)
2 =T(“Charge.xlsx”)
3 =A1.join(Mealtype, A2:Mealtype, Mealcharge)

8.


 A
1 =T(“Quantity.xlsx”)
2 =T(“Price.xlsx”)
3 =A1.derive(A2.segp@r(StartQuantity,Quantity).Price:Price)

10.3 归并

1.

（1）


 A
1 =T(“2018.xlsx”)
2 =T(“2019.xlsx”)
3 =[A1,A2].merge@io(CustomerID,ProductID)

（2）


 A
1 =T(“2018.xlsx”)
2 =T(“2019.xlsx”)
3 =[A1,A2].merge@o(OrderID)
4 =A3.groups(CustomerID;sum(Amount):TotalAmount)

（3）


 A
1 =T(“2018.xlsx”)
2 =T(“2019.xlsx”)
3 =A2.id(CustomerID)\A1.id(CustomerID)
4 =A2.select(A3.contain(CustomerID))

（4）


 A
1 =T(“2018.xlsx”)
2 =T(“2019.xlsx”)
3 =A1.id(CustomerID)\A2.id(CustomerID)

2.


 A
1 =T(“Jan.xlsx”)
2 =T(“Feb.xlsx”)
3 =[A1,A2].merge@oi(ProductName)
4 =[A1,A2].merge@ou(ProductName)
5 =[A1,A2].merge@od(ProductName)
6 =[A1,A2].merge@oi()

3.


 A
1 =file("top10Sales.xlsx").xlsopen()
2 =A1.(A1.xlsimport@t(;stname)).merge@oi(ProductName)

4.

（1）


 A
1 =T("Online.xlsx")
2 =T("Store.xlsx")
3 =A1.sort(OrderID)
4 =A2.sort(OrderID)
5 =[A3,A4].merge@u(OrderID)
6 =A5.sum(Amount)

（2）


 A
1 =T("Online.xlsx")
2 =T("Store.xlsx")
3 =A1.sort(OrderID)
4 =A2.sort(OrderID)
5 =[A3,A4].merge@i(OrderID)

5.


 A
1 =T("old.xlsx")
2 =T("new.xlsx")
3 =A1.sort(UserName,Date)
4 =A2.sort(UserName,Date)
5 =new=[A4,A3].merge@d(UserName,Date)
6 =delete=[A3,A4].merge@d(UserName,Date)
7 =diff=[A4,A3].merge@d(UserName,Date,SaleValue,SaleCount)
8 =update=[diff,new].merge@d(UserName,Date)
9 return [new, delete, update]

10.4 连接

1.

（1）


 A
1 =T("Customers.xlsx")
2 =T("Orders")
3 =A2.groups(customerId;sum(amount):Amount)
4 =join(A1:a,id;A3:b,customerId)
5 =A4.new(a.name,b.Amount)

（2）


 A
1 =T("Customers.xlsx")
2 =T("Orders")
3 =A2.groups(customerId)
4 =join@d(A1:a,id;A3:b,customerId)

2.

（1）


 A
1 =file("Meats.xlsx").xlsimport@t()
2 =file("MeatStock.xlsx").xlsimport@t()
3 =join@f(A1:Price,Name;A2:Stock,Name)
4 =A3.new([Price.Name,Stock.Name].ifn():Name,Stock.Stock,Stock.MinimumStock,Price.UnitPrice)
5 =file("MeatsPriceStock.xlsx").xlsexport@t(A4)

（2）


 A
1 =file("Meats.xlsx").xlsimport@t()
2 =file("MeatStock.xlsx").xlsimport@t()
3 =join(A1:Price,Name;A2:Stock,Name)
4 =A3.new(Stock.Name,Stock.Stock,Stock.MinimumStock,Price.UnitPrice)
5 =file("MeatsPriceStock.xlsx").xlsexport@t(A4)

（3）


 A
1 =file("Meats.xlsx").xlsimport@t()
2 =file("MeatStock.xlsx").xlsimport@t()
3 =join@1(A1:Price,Name;A2:Stock,Name)
4 =A3.new(Price.Name,Stock.Stock,Stock.MinimumStock,Price.UnitPrice)
5 =file("MeatsPriceStock.xlsx").xlsexport@t(A4)

3.


 A
1 =file("MeatsRegion.xlsx").xlsimport@t()
2 =file("MeatRegionStock.xlsx").xlsimport@t()
3 =join@1(A1:Price,Region,Name;A2:Stock,Region,Name)
4 =A3.new(Price.Region,Price.Name,Stock.Stock,Stock.MinimumStock,Price.UnitPrice)
5 =file("MeatsPriceStock.xlsx").xlsexport@t(A4)

4.


 A
1 =T("Types.xlsx")
2 =T("Foods.xlsx")
3 =join@f(A1:Type,Type;A2:Food,Type)
4 =A3.new(Food.Type,Food.Name,Food.UnitPrice,Type.Description)
5 =T("FoodsDescription.xlsx",A4)

5.


 A
1 =T("Community.xlsx")
2 =T("Age.xlsx")
3 =xjoin@1(A1:Person; A2:Age, A2.Start<=Person.Age && A2.End>=Person.Age)
4 =A3.new(Person.ID:ID, Person.Name:Name, Person.Age:Age,Age.Group:Group)