AVG +内部加入 [英] AVG + Inner join

问题描述

我正在尝试制作类似图表列表的内容，而我的实际查询是这样的:

I'm trying to make something like a chart list and my actual query is like this:

SELECT 
    user.username, 
    songs.*, 
    albums.desc, 
    albums.release, 
    albums.name, 
    AVG(songrating.rating)  
FROM 
    songs 
INNER JOIN 
    user 
ON 
    songs.userid=user.id 
INNER JOIN 
    albums 
ON 
    songs.albumid=albums.id 
INNER JOIN  
    songrating  
ON 
    songs.id=songrating.songid 
GROUP BY 
songrating.songid

它仅显示具有至少一个评分条目的条目，但我也希望这些条目没有评分

it only shows entries with at least one rating entry, but I also want these without a rating

我尝试将其与if/case一起使用，但是它不起作用(或者我做错了什么)

I tried to use it with if / case but it doesn't work (or I'm doing something wrong)

如果我删除平均值等，则它可以正常工作.

If I remove the avg etc, it works correctly.

推荐答案

可能是JOIN问题

INNER JOIN 关键字从两个表中选择所有行，只要有一个匹配两个表中的列.

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

LEFT JOIN 关键字返回左表(表1)中的所有行，并带有右表(table2)中的匹配行.如果没有匹配项，则结果在右侧为NULL.

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

尝试左加入

这篇关于AVG +内部加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！