这是一个有点奇怪的查询,我接近,但未能解决我有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 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条答案
按热度按时间jxct1oxe1#
当你在做一个
Group By
,您需要确保您的选择列表只包含聚合列(例如:minimum、maximum、average、count等)和/或要分组的列。do read:选择列表不在group by子句中,并且包含未聚合的列。。。。与sql不兼容\u mode=only \u full \u group by
为了计算会员的总租金,您需要使用
COUNT()
功能。请尝试以下操作: