编写一个查询，可用于查找从客户发送到不同国家/地区的订单

So I am currently in a Database 2 class at my University. We are using the Northwinds db. I haven't used SQL in a few years so I am a little rusty.

所以我目前在我的大学的数据库2课程。我们正在使用Northwinds数据库。几年后我没有使用SQL,所以我有点生疏了。

I changed a few of the pieces in the Orders table so that instead of 'Germany' it was 'Tahiti'. Now I need to write a query to find which orders shipped where.

我更改了Orders表中的一些部分,而不是'德国',它是'Tahiti'。现在我需要编写一个查询来查找哪些订单发送到哪里。

I know that I will need to use an Join but I am not exactly sure how. I have gone to W3Schools and looked at the Joins SQL page but still haven't found the correct answer I am looking for.

我知道我需要使用Join,但我不确定如何。我去了W3Schools并查看了Joins SQL页面,但仍未找到我正在寻找的正确答案。

This is what I have currently (which I am also not sure if it is correct):

这就是我目前所拥有的(我也不确定它是否正确):

SELECT Customers.Country
FROM Customer
WHERE Customer.Country = 'Germany'

INNER JOIN

SELECT Orders.ShipCountry
FROM Orders
WHERE Orders.ShipCountry = 'Tahiti'

So if anyone could give me help I would really appreciate it.

所以,如果有人能给我帮助,我会非常感激。

EDIT

So this is the actual question I was given which I think is also kind of poorly worded. "Suspicious e-commerce transactions include orders placed by a customer in one country that are shipped to another country. In fact there are no such orders in the Northwind db, so create a few by modifying some of the "Germany" shipcountry entries in the orders table to "Tahiti". Then write a query which finds orders shipped to a different country from the customer. Hint: in order to do this, you will need to join the Customers and Orders table."

所以这是我给出的实际问题,我认为这也是措辞不好。 “可疑的电子商务交易包括客户在一个国家运送到另一个国家的订单。实际上Northwind数据库中没有这样的订单,所以通过修改一些”德国“船舶国家的条目来创建一些订单。订购表到“Tahiti”。然后写一个查询,查找从客户运送到不同国家的订单。提示:为了做到这一点,您需要加入客户和订单表。“

2 个解决方案

#1

Is this what you are looking for:

这是你想要的:

SELECT *
FROM Customer AS C
INNER JOIN
Orders AS O
ON C.CustomerID = O.CustomerID
WHERE C.Country = 'Germany' AND  O.ShipCountry = 'Tahiti';

The above query is based on the Schema as defined in CodePlex

以上查询基于CodePlex中定义的Schema

#2

-1

i hope that this can help you

我希望这可以帮到你

SELECT Orders.* 
FROM Orders  -- table name, also can use alias
inner join Customer -- table name, also can use alias
on Orders.ShipCountry = Customer.Country  -- you must declare what is the field to use by join
where Customer.Country in ('Germany','Tahiti')

#1