mysql - 获取平均值并连接 3 个表

Question

我有 3 个表格，如下所示；

用户表:

<table border="1">
<tr>
<td>uid</td>
<td>name</td>
<td>status</td>
<td>category</td>
</tr>
<tr>
<td>1</td>
<td>John Doe</td>
<td>1</td>
<td>C</td>
</tr>
<tr>
<td>2</td>
<td>Jane Doe</td>
<td>1</td>
<td>C</td>
</tr>

</table>

配置文件表:

<table border="1">
<tr>
<td>pid</td>
<td>uid</td>
<td>slug</td>
</tr>
<tr>
<td>1</td>
<td>1</td>
<td>john-doe</td>
</tr>
<tr>
<td>2</td>
<td>2</td>
<td>jane-doe</td>
</tr>
</table>

和评级表:

<table border="1">
<tr>
<td>rid</td>
<td>uid</td>
<td>rating</td>
</tr>
<tr>
<td>1</td>
<td>1</td>
<td>3.5</td>
</tr>
<tr>
<td>2</td>
<td>1</td>
<td>2.5</td>
</tr>
<tr>
<td>3</td>
<td>2</td>
<td>3.5</td>
</tr>
<tr>
<td>4</td>
<td>1</td>
<td>2.5</td>
</tr>

<tr>
<td>5</td>
<td>2</td>
<td>3.5</td>
</tr>
<tr>
<td>6</td>
<td>2</td>
<td>2.5</td>
</tr>


<tr>
<td>7</td>
<td>1</td>
<td>3.5</td>
</tr>
<tr>
<td>8</td>
<td>2</td>
<td>2.5</td>
</tr>
</table>

我正在尝试加入该表，并从评级表中的评级中获取具有通用 id 和平均评级的值。

我想要的输出如下；

<table border="1">
<tr>
<td>uid</td>
<td>name</td>
<td>slug</td>
<td>status</td>
<td>category</td>
<td>rating</td>
</tr>
<tr>
<td>1</td>
<td>John Doe</td>
<td>john-doe</td>
<td>1</td>
<td>C</td>
<td>3.25</td>
</tr>

<tr>
<td>2</td>
<td>Jane Doe</td>
<td>jane-doe</td>
<td>1</td>
<td>C</td>
<td>3</td>
</tr>


</table>

Answer 1

请尝试以下查询，我认为这对您有用。

select u.*, p.slug, (select avg(rating) from Rating r where r.uid=u.uid) as rating from User u JOIN Profile p ON p.uid=u.uid