I'm new to mySQL, and I'm trying to be able to either SELECT or CREATE A VIEW with the information I want to retrieve from two tables. SQLfiddle
我是mySQL的新成员，我试图通过两个表来选择或创建一个视图。SQLfiddle
People Table
人表
| people_id | username | Password | Email | ---------------------------------------------- | 1 | username1 | Password1 | Email1 | | 2 | username2 | Password2 | Email2 | | 3 | username3 | Password3 | Email3 |
Profile Table
概要文件表
| people_id | id | age | location | hobbies | about | ---------------------------------------------------------------------------------------------------------------------------- | 1 | 1 | 22 | USA | skiing, snowboarding | I am from the US and I like to snowboard | | 2 | 2 | 45 | Canada | curling, ice skating, reading | I like to ice skate! | | 3 | 3 | 38 | USA | tv, movies, you name it | I am from the US and I like to watch the tube | | 2 | 4 | 45 | Canada | curling, reading | I do not like to ice skate anymore | | 2 | 5 | 46 | Canada | bowling | Bowling is my new favorite hobby! I just turned 46! | | 1 | 6 | 22 | Brazil | skiing, snowboarding | I am orginally from the US but I just moved to brazil|
I would like to see/retrieve the data like this :
我希望看到/检索数据是这样的:
| people_id | username | age | location | hobbies | about | ------------------------------------------------------------------------------------------------------------------------------------ | 3 | username3 | 38 | USA | tv, movies, you name it | I am from the US and I like to watch the tube | | 2 | username2 | 46 | Canada | bowling | Bowling is my new favorite hobby! I just turned 46! | | 1 | username1 | 22 | Brazil | skiing, snowboarding | I am orginally from the US but I just moved to brazil|
So I need to select all the people_id and username from table People and then select the people_id row from Profile where the id is the largest number for each people_id
因此，我需要从表人员中选择所有people_id和用户名，然后从配置文件中选择people_id行，其中id是每个people_id的最大数字。
I've tried
我试过了
SELECT People.people_id, People.username, Profile.age, Profile.location, Profile.hobbies, Profile.about
FROM People
INNER JOIN Profile
ON People.people_id=Profile.people_id
Which gets me closer to what I want, but I don't want to show duplicate rows, I only want to show the last row inserted into the Profile table for each people_id.
这使我更接近我想要的，但是我不想显示重复的行，我只想显示最后一行插入到每个people_id的概要表中。
SQLfiddle
2 个解决方案
#1
1
The most efficient way to get what you want is to use a not exists condition in the where clause. This definitely takes some getting used to. What the query is going to do is to get the matching row from Profile subject to the condition that no other row has a larger id. This is a round-about way of saying "get the biggest id for each person". But, it happens to produce an efficient query plan (and this is true in most databases, not just MySQL).
得到你想要的最有效的方法是在where子句中使用一个不存在的条件。这确实需要一些习惯。查询要做的是从概要文件中获取匹配的行，而不是其他行具有较大的id。这是一个关于“为每个人获取最大id”的循环方式。但是，它碰巧产生了一个高效的查询计划(这在大多数数据库中是正确的，而不仅仅是MySQL)。
SELECT p.people_id, p.username, pr.age, pr.location, pr.hobbies, pr.about
FROM People p INNER JOIN
Profile pr
ON p.people_id = pr.people_id
WHERE NOT EXISTS (SELECT 1
FROM Profile pr2
WHERE pr2.people_id = pr.people_id AND
pr2.id > pr.id
);
#2
1
SELECT People.people_id, People.username, Profile.age, Profile.location, Profile.hobbies, Profile.about
FROM People
INNER JOIN (SELECT * FROM Profile ORDER BY id DESC) AS Profile
ON People.people_id=Profile.people_id
GROUP BY People.people_id
ORDER BY people_id DESC
#1
1
The most efficient way to get what you want is to use a not exists condition in the where clause. This definitely takes some getting used to. What the query is going to do is to get the matching row from Profile subject to the condition that no other row has a larger id. This is a round-about way of saying "get the biggest id for each person". But, it happens to produce an efficient query plan (and this is true in most databases, not just MySQL).
得到你想要的最有效的方法是在where子句中使用一个不存在的条件。这确实需要一些习惯。查询要做的是从概要文件中获取匹配的行，而不是其他行具有较大的id。这是一个关于“为每个人获取最大id”的循环方式。但是，它碰巧产生了一个高效的查询计划(这在大多数数据库中是正确的，而不仅仅是MySQL)。
SELECT p.people_id, p.username, pr.age, pr.location, pr.hobbies, pr.about
FROM People p INNER JOIN
Profile pr
ON p.people_id = pr.people_id
WHERE NOT EXISTS (SELECT 1
FROM Profile pr2
WHERE pr2.people_id = pr.people_id AND
pr2.id > pr.id
);
#2
1
SELECT People.people_id, People.username, Profile.age, Profile.location, Profile.hobbies, Profile.about
FROM People
INNER JOIN (SELECT * FROM Profile ORDER BY id DESC) AS Profile
ON People.people_id=Profile.people_id
GROUP BY People.people_id
ORDER BY people_id DESC