代码之家 › 专栏 › 技术社区 › Ben

合并3个SQL表(一个需要搜索,一个需要平均值,一个需要计数)

sql mysql

Ben · 技术社区 · 6 年前

我的桌子:

Members

|MemberID | Fname | Lname
|  1      | foo   | barr
|  2      | bazz  | lorem
(PK MemberID)

MemberRatings

|RatingID | MemberID | RatingValue
|1        | 1        | 5.0
|2        | 1        | 4.4
|3        | 2        | 4.5
|4        | 1        | 4.0
(PK RatingID, FK MemberID-> Members MemberID

Rental

|RentalID | MemberID | RentalDate
|1        | 1        | 2018-06-06
|2        | 1        | 2018-08-08
(PK RentalID, FK MemberID->Members MemberID)

SELECT
    #member info
    m.MemberID, m.Fname, m.Lname,
    #ratings info (get ready to cast AVG as decimal)
    ra.RatingID, ra.MemberID, CAST(AVG(ra.RatingValue) AS DECIMAL(3,2)),
    #check how many rentals they have
    re.RentalID, re.MemberID
FROM
    #Member MemberID = Rating MemberID
    Members m 
LEFT JOIN 
    MemberRatings ra ON m.MemberID = ra.MemberID
    #Member MemberID = Rental memberID
LEFT JOIN 
    Rental re ON m.MemberID = re.MemberID
    #Where search terms match
WHERE 
    (m.Fname LIKE '%".$membersSearchTerm."%' OR 
     m.Lname LIKE '%".$membersSearchTerm."%')
#Group by memberID to do the AVG
GROUP BY 
    m.MemberID;

接近我想要的输出,但正在拉取重复列并返回空结果

输出:

| MemberID | Fname | Lname | RatingID | MemberID | CAST(AVG(ra.RatingValue) AS DECIMAL(3,2)) | RentalID | MemberID 
| 1        | foo   | barr  | 1        | 1        | 4.46 | 1    | 1
| 2        | bazz  | lorem | 2        | 2        | 4.5  | NULL | NULL

感觉我加入不正确,但我对SQL的了解非常有限,我试图通过左加入来实现这一点,是否有更好的方法将结果输出为以下内容:

| MemberID | Fname | Lname | AVG  | Rentals |
| 1        | foo   | barr  | 4.46 | 2       | 
| 2        | bazz  | lorem | 4.5  | 0       |

我厌倦了添加 RentalID = NOT NULL 在 WHERE 但这也不能完全解决问题。

1 回复 | 直到 6 年前

Madhur Bhaiya 6 年前

当你在做一个 Group By ,您需要确保您的选择列表只包含聚合列(例如:minimum、maximum、average、count等)和/或所选列在…上。

请阅读: SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

COUNT()

请尝试以下操作:

SELECT
  m.MemberID, 
  m.Fname, 
  m.Lname,
  CAST(AVG(ra.RatingValue) AS DECIMAL(3,2)) AS average_rating,
  COUNT(DISTINCT re.RentalID) AS rental_count 
FROM Members m 
LEFT JOIN MemberRatings ra 
       ON m.MemberID = ra.MemberID
LEFT JOIN Rental re 
       ON m.MemberID = re.MemberID
WHERE (
  m.Fname LIKE '%".$membersSearchTerm."%' OR
  m.Lname LIKE '%".$membersSearchTerm."%'
)
GROUP BY m.MemberID, 
         m.Fname, 
         m.Lname