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

程序设计习题 第 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)