MySQL-如何联接两个没有重复的表? [英] MySQL - How to join two tables without duplicates?

问题描述

我有两个如下表

hotels
------
hotelID
hotelName

第二张桌子

operators
---------
opID
opName
opServices
opHotelID

一个简短的解释:在第一个表中，我有很多旅馆的ID都是唯一的.第二个表包含为该酒店提供其他服务的所有运营商.这里的opID也是唯一的，但是opHotelID存在多次，因为可能有很多运营商提供该酒店.

a short explanation: In the first table I have a lot of hotels which have an increment id which is unique. The second table contains all the operators offering this hotel with additional services. The opID here is unique too but the opHotelID exists multiple times, because there can be many operators offering the hotel.

现在，我想得到的是以下内容:

Now, what I want to get is the following:

我想获取HotelName和附加的一列(称为运营商")，该列列出了提供酒店的所有运营商.

I want to get the HotelName and an additional Column (called Operators) which lists all the operators offering the hotel.

所以结果应该是这样的...

So the result should be like this...

123 - Hotel ABC - OP1,Op2,OP3

代替这个...

123 - Hotel ABC - OP1
123 - HOtel ABC - OP2
123 - Hotel ABC - OP3

是否有一种方法可以在一个SQL查询中执行此操作，或者您将如何解决此问题?我目前正在使用搜索功能，目前我有一个带有左联接的简单SELECT查询，但这将返回更多行.现在，搜索应仅显示唯一的HotelID，并将不同的运算符合并到一栏中.

Is there a way to do this in one SQL query or how would you solve this problem? I am currently working on a search function and currently i have a simple SELECT query with a left join but this returns a lot of more rows. Now the Search should only display unique HotelIDs and combine the different Operators in one column.

感谢您的帮助，祝您有美好的一天...

Thanks for you help and have a nice day...

再见 WorldSignia

Bye WorldSignia

推荐答案

尝试以下方法:

SELECT hotels.hotelID, 
hotels.hotelName,
GROUP_CONCAT(operators.opName SEPARATOR ', ') AS opList
FROM hotels
INNER JOIN operators 
ON operators.opHotelID = hotels.hotelID
GROUP BY(hotels.hotelID)

如果要具有操作员数量，则必须在操作员ID上使用COUNT，如下所示:

If you want to have the number of operators, you have to use COUNT on the operators ID like that:

SELECT hotels.hotelID, 
hotels.hotelName,
GROUP_CONCAT(operators.opName SEPARATOR ', ') AS opList,
COUNT(operators.opID) AS nbOperatos
FROM hotels
LEFT JOIN operators 
ON operators.opHotelID = hotels.hotelID
GROUP BY(hotels.hotelID)

这篇关于MySQL-如何联接两个没有重复的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！