这是一个有点像怪物的查询,我很接近,但没有解决我有3个表,共享一些共同的ID,我想从中获取和显示数据,但不确定是否可能?我的桌子: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.MemberIDFROM #Member MemberID = Rating MemberID Members mLEFT JOIN MemberRatings ra ON m.MemberID = ra.MemberID #Member MemberID = Rental memberIDLEFT JOIN Rental re ON m.MemberID = re.MemberID #Where search terms matchWHERE (m.Fname LIKE '%".$membersSearchTerm."%' OR m.Lname LIKE '%".$membersSearchTerm."%')#Group by memberID to do the AVGGROUP 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的知识非常有限,我试图通过LEFT JOIN来完成这项工作,是否有更好的方法将结果输出为如下所示:| MemberID | Fname | Lname | AVG | Rentals || 1 | foo | barr | 4.46 | 2 || 2 | bazz | lorem | 4.5 | 0 |我厌倦了在RentalID = NOT NULL中添加WHERE,但这也不能完全回答问题。 最佳答案 当您正在执行一个 >时,您需要确保您的选择列表只包含聚合列(例如:最小、最大、平均、计数等)和/或分组的列。请阅读:SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by要计算成员的总租金,您需要使用Group By函数。请尝试以下操作:SELECT m.MemberID, m.Fname, m.Lname, CAST(AVG(ra.RatingValue) AS DECIMAL(3,2)) AS average_rating, COUNT(DISTINCT re.RentalID) AS rental_countFROM Members mLEFT JOIN MemberRatings ra ON m.MemberID = ra.MemberIDLEFT JOIN Rental re ON m.MemberID = re.MemberIDWHERE ( m.Fname LIKE '%".$membersSearchTerm."%' OR m.Lname LIKE '%".$membersSearchTerm."%')GROUP BY m.MemberID, m.Fname, m.Lname关于mysql - 合并3个SQL表(一个需要搜索,一个平均,一个需要计数),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/52912915/
10-11 04:54