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

qyswt5oh  于 2021-06-19  发布在  Mysql
关注(0)|答案(1)|浏览(256)

这是一个有点奇怪的查询,我接近,但未能解决我有3个表共享一些共同的ID,我想抓取和显示数据,但不确定是否可能?
我的table: 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 MemberIDRental ```
|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` 但这也不能完全解决问题。
jxct1oxe

jxct1oxe1#

当你在做一个 Group By ,您需要确保您的选择列表只包含聚合列(例如:minimum、maximum、average、count等)和/或要分组的列。
do read:选择列表不在group by子句中,并且包含未聚合的列。。。。与sql不兼容\u mode=only \u full \u 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

相关问题