查询在MySQL中检索数据

I have 3 tables.

我有3张桌子。

a. salesperson(SSN, lName, startYear, deptNo)

一个。销售人员(SSN,lName,startYear,deptNo)

b. trip(tripID, SSN, fromCity, toCity, departureDate, returnDate)

湾旅行(tripID,SSN,fromCity,toCity,departureDate,returnDate)

c. expense(tripID, accountNumber, amount)

C。费用(tripID,accountNumber,金额)

The primary keys for each table are. SSN for the Salesperson table. TripId for the Trip table, and tripID and account number for the Expense table.

每个表的主键是。 Salesperson表的SSN。 Trip表的TripId,以及Expense表的tripID和帐号。

The foreign keys for each table are. deptNo for the Salesperson table. SSN in the Trip table, and tripID in the Expense Table

每个表的外键是。 deptNo for Salesperson表。 Trip表中的SSN和费用表中的tripID

---

What I am trying to do:

我想做什么:

1) Find the tripID, SSN, fromCity, tocity, departureDate, returnDate and amount for trips that exceed $2000 in expenses.

1)找到tripID,SSN,fromCity,tocity,departureDate,returnDate和超过2000美元费用的旅行金额。

2) Find the SSN for Sales Representatives that took trips to ‘CHICAGO’.

2)找到前往“芝加哥”的销售代表的SSN。

3) Find the total trip expenses incurred by the salesman with SSN = ‘123-45-6789’.

3)找出SSN ='123-45-6789'的销售员的总旅行费用。

I did not create any of this, it is a practice assignment from school, thank you very much for the help and please don't use very technical language.

我没有创建任何这个,这是来自学校的练习作业,非常感谢你的帮助,请不要使用非常技术性的语言。

2 个解决方案

#1

1.

SELECT tripID,SSN,fromCity,tocity,departureDate,returnDate
FROM
(SELECT Trip.tripID, Trip.SSN, Trip.fromCity, Trip.tocity, Trip.departureDate, Trip.returnDate,sum(Expense.amount) total
FROM Trip left join Expense
ON Trip.tripID = Expense.tripID
GROUP BY Trip.tripID) data
WHERE data.total>2000

2.

SELECT distinct SSN 
FROM Trip 
WHERE fromCity='Chicago'

3.

SELECT sum(Expense.amount) total
    FROM Trip left join Expense
    ON Trip.tripID = Expense.tripID
GROUP BY Trip.SSN = '123456789'

You have to check these by yourself.

你必须自己检查这些。

#2

You appear to be asking for the full solution to your assignment. * is a Q&A (Question and Answer) site. You won't, or at least you shouldn't, get any help unless you show that you have at least attempted to write the queries yourself. People will be happy to help fix specific issues if you are having trouble getting a query you have written to work as it should.

您似乎要求为您的作业提供完整的解决方案。 *是一个Q&A(问答)网站。你不会,或者至少你不应该得到任何帮助,除非你证明你至少试图自己编写查询。如果您在查找已编写的查询时遇到问题,人们将很乐意帮助您解决具体问题。

I suggest you try this tutorial, paying particular attention to sections 1, 3, 4, 5, 11, 8, 17 & 18 :

我建议你试试这个教程,特别注意第1,3,4,5,11,8,17和18节:

25 Essential MySQL Select Command Examples

25必备MySQL选择命令示例

Edited to add: I suggest you tackle the second query first as it is the simplest. If you can get that one working you should be able to expand your understanding in order to implement the other two.

编辑添加:我建议你首先处理第二个查询,因为它是最简单的。如果你可以使那个工作,你应该能够扩展你的理解,以实现其他两个。