mirror of
https://gitee.com/coder-xiaomo/leetcode-problemset
synced 2025-11-04 03:33:12 +08:00
64 lines
1.8 KiB
HTML
64 lines
1.8 KiB
HTML
<p>项目表 <code>Project</code>： </p>
|
||
|
||
<pre>
|
||
+-------------+---------+
|
||
| Column Name | Type |
|
||
+-------------+---------+
|
||
| project_id | int |
|
||
| employee_id | int |
|
||
+-------------+---------+
|
||
主键为 (project_id, employee_id)。
|
||
employee_id 是员工表 <code>Employee 表的外键。</code>
|
||
</pre>
|
||
|
||
<p>员工表 <code>Employee</code>：</p>
|
||
|
||
<pre>
|
||
+------------------+---------+
|
||
| Column Name | Type |
|
||
+------------------+---------+
|
||
| employee_id | int |
|
||
| name | varchar |
|
||
| experience_years | int |
|
||
+------------------+---------+
|
||
主键是 employee_id。
|
||
</pre>
|
||
|
||
<p> </p>
|
||
|
||
<p>请写一个 SQL 语句，查询每一个项目中员工的 <strong>平均 </strong>工作年限，<strong>精确到小数点后两位</strong>。</p>
|
||
|
||
<p>查询结果的格式如下：</p>
|
||
|
||
<pre>
|
||
Project 表：
|
||
+-------------+-------------+
|
||
| project_id | employee_id |
|
||
+-------------+-------------+
|
||
| 1 | 1 |
|
||
| 1 | 2 |
|
||
| 1 | 3 |
|
||
| 2 | 1 |
|
||
| 2 | 4 |
|
||
+-------------+-------------+
|
||
|
||
Employee 表：
|
||
+-------------+--------+------------------+
|
||
| employee_id | name | experience_years |
|
||
+-------------+--------+------------------+
|
||
| 1 | Khaled | 3 |
|
||
| 2 | Ali | 2 |
|
||
| 3 | John | 1 |
|
||
| 4 | Doe | 2 |
|
||
+-------------+--------+------------------+
|
||
|
||
Result 表：
|
||
+-------------+---------------+
|
||
| project_id | average_years |
|
||
+-------------+---------------+
|
||
| 1 | 2.00 |
|
||
| 2 | 2.50 |
|
||
+-------------+---------------+
|
||
第一个项目中，员工的平均工作年限是 (3 + 2 + 1) / 3 = 2.00；第二个项目中，员工的平均工作年限是 (3 + 2) / 2 = 2.50
|
||
</pre>
|