如何在其他select查询中包含select查询，其中包含左连接

xbp102n0 于 2021-06-23 发布在 Mysql

关注(0)|答案(1)|浏览(338)

student 表，

| student_id |  name  |  gender |
|------------|--------|---------|
|    1174    |  Steve |   male  |
|    1175    |  Jane  |  female |
|    1176    |  Mark  |   male  |
|    1177    |  Lily  |  female |
---------------------------------
``` `period` 表，此表用于确定学生可以聚集的最大与会者数

period_id	from	to	max_session_female	max_session_male
1	2018-03-02	2018-03-04	14	15
2	2018-03-05	2018-03-08	20	20
-----------------------------------------------------------------------------

``` attendance 学生出勤表

| student_id | period_id |    date    | tapping_time | session |
|------------|-----------|------------|--------------|---------|
|   1174     |    1      | 2018-03-02 |   15:30:49   |    C    |
|   1174     |    1      | 2018-03-02 |   19:56:15   |    F    |
|   1174     |    1      | 2018-03-03 |   05:10:20   |    E    |
|   1174     |    1      | 2018-03-03 |   12:28:54   |    B    |
|   1174     |    1      | 2018-03-03 |   15:31:12   |    C    |
|   1174     |    1      | 2018-03-04 |   12:26:33   |    B    |
|   1174     |    1      | 2018-03-04 |   15:39:06   |    C    |
|   1174     |    1      | 2018-03-04 |   18:32:40   |    E    |
|   1174     |    1      | 2018-03-04 |   19:56:09   |    F    |
|   1174     |    2      | 2018-03-05 |   05:14:55   |    E    |
|   1175     |    2      | 2018-03-05 |   12:27:29   |    B    |
|   1175     |    2      | 2018-03-05 |   19:53:19   |    F    |
|   1175     |    2      | 2018-03-06 |   12:25:45   |    B    |
|   1175     |    2      | 2018-03-08 |   12:29:41   |    B    |
|   1175     |    2      | 2018-03-08 |   15:32:14   |    E    |
|   1175     |    2      | 2018-03-08 |   20:24:03   |    F    |
|   1175     |    1      | 2018-03-02 |   05:15:13   |    C    |
|   1175     |    1      | 2018-03-02 |   12:36:19   |    B    |
|   1175     |    1      | 2018-03-02 |   15:38:20   |    C    |
|   1175     |    1      | 2018-03-02 |   19:52:09   |    F    |
|   1175     |    1      | 2018-03-03 |   05:14:24   |    C    |
|   1175     |    1      | 2018-03-03 |   12:29:26   |    B    |
|   1175     |    1      | 2018-03-03 |   15:31:48   |    C    |
|   1175     |    1      | 2018-03-03 |   19:55:41   |    F    |
|   1175     |    1      | 2018-03-04 |   12:29:52   |    B    |
|   1175     |    1      | 2018-03-04 |   15:40:39   |    C    |
|   1175     |    1      | 2018-03-04 |   19:53:18   |    F    |
|   1175     |    2      | 2018-03-05 |   05:12:05   |    A    |
|   1175     |    2      | 2018-03-05 |   12:29:27   |    B    |
|   1175     |    2      | 2018-03-05 |   15:28:16   |    C    |
|   1175     |    2      | 2018-03-05 |   19:55:52   |    F    |
|   1175     |    2      | 2018-03-06 |   05:15:10   |    A    |
|   1175     |    2      | 2018-03-06 |   12:32:10   |    B    |
|   1175     |    2      | 2018-03-06 |   15:33:11   |    C    |
|   1175     |    2      | 2018-03-06 |   20:13:48   |    F    |
|   1175     |    2      | 2018-03-07 |   05:13:25   |    A    |
|   1175     |    2      | 2018-03-07 |   12:28:13   |    B    |
|   1175     |    2      | 2018-03-07 |   15:37:28   |    C    |
|   1175     |    2      | 2018-03-07 |   20:23:06   |    F    |
|   1175     |    2      | 2018-03-08 |   05:11:47   |    A    |
|   1175     |    2      | 2018-03-08 |   12:31:43   |    B    |
|   1175     |    2      | 2018-03-08 |   15:28:29   |    C    |
|   1175     |    2      | 2018-03-08 |   20:21:29   |    F    |
|   1176     |    2      | 2018-03-07 |   20:50:43   |    F    |
|   1176     |    2      | 2018-03-08 |   19:54:32   |    F    |
|   1177     |    1      | 2018-03-02 |   05:13:30   |    A    |
|   1177     |    1      | 2018-03-02 |   12:38:29   |    B    |
|   1177     |    1      | 2018-03-02 |   19:53:38   |    F    |
|   1177     |    1      | 2018-03-03 |   05:12:33   |    A    |
|   1177     |    1      | 2018-03-03 |   12:34:48   |    B    |
|   1177     |    1      | 2018-03-03 |   15:39:05   |    C    |
|   1177     |    1      | 2018-03-03 |   20:00:51   |    F    |
|   1177     |    1      | 2018-03-04 |   05:10:59   |    A    |
|   1177     |    1      | 2018-03-04 |   12:33:56   |    B    |
|   1177     |    1      | 2018-03-04 |   15:39:09   |    C    |
|   1177     |    1      | 2018-03-04 |   19:48:59   |    F    |
|   1177     |    2      | 2018-03-05 |   05:12:15   |    A    |
|   1177     |    2      | 2018-03-05 |   12:30:53   |    B    |
|   1177     |    2      | 2018-03-05 |   15:34:53   |    C    |
|   1177     |    2      | 2018-03-05 |   19:53:19   |    F    |
|   1177     |    2      | 2018-03-06 |   05:12:56   |    A    |
|   1177     |    2      | 2018-03-06 |   12:30:03   |    B    |
|   1177     |    2      | 2018-03-06 |   15:31:00   |    C    |
|   1177     |    2      | 2018-03-06 |   19:52:40   |    F    |
|   1177     |    2      | 2018-03-07 |   05:12:30   |    A    |
|   1177     |    2      | 2018-03-07 |   12:34:48   |    B    |
|   1177     |    2      | 2018-03-07 |   15:40:34   |    C    |
|   1177     |    2      | 2018-03-07 |   19:53:47   |    F    |
|   1177     |    2      | 2018-03-08 |   05:11:21   |    A    |
|   1177     |    2      | 2018-03-08 |   12:35:21   |    B    |
|   1177     |    2      | 2018-03-08 |   15:36:26   |    C    |
|   1177     |    2      | 2018-03-08 |   19:52:32   |    F    |

我找到了一个查询，可以根据性别、女性来计算学生的平均分数：

SELECT (COUNT(a.tapping_time)/2)/p.max_session_female*100 AS 'women_score' 
FROM period p
LEFT JOIN attendance a ON p.period_id = a.period_id
LEFT JOIN student s ON a.student_id = a.student_id
WHERE s.gender = 'female'
GROUP BY p.period_id

男性：

SELECT (COUNT(a.tapping_time)/2)/p.max_session_male*100 AS 'men_score ' 
FROM period p
LEFT JOIN attendance a ON p.period_id = a.period_id
LEFT JOIN student s ON a.student_id = a.student_id
WHERE s.gender = 'male'
GROUP BY p.period_id

但是如何在一张table上得分呢？
期望结果：

-----------------------------------------------------------------------------
| period_id | from       | to         | total_att | women_score | men_score |
|-----------|------------|------------|-----------|-------------|-----------|
|     1     | 2018-03-02 | 2018-03-04 |    31     |     78      |     30    |
|     2     | 2018-03-05 | 2018-03-08 |    41     |     95      |     7.5   |
-----------------------------------------------------------------------------

mysql select

来源：https://stackoverflow.com/questions/50548730/how-to-include-select-query-in-other-select-query-with-left-join-inside

1条答案

按热度按时间

yks3o0rb1#

使用条件聚合：

SELECT
    period_id,
    MIN(date) AS `from`,
    MAX(date) AS `to`,
    COUNT(*) AS total_att,
    100 * COUNT(CASE WHEN s.gender = 'male'
               THEN a.tapping_time END) / p.max_session_male AS male_score,
    100 * COUNT(CASE WHEN s.gender = 'female'
               THEN a.tapping_time END) / p.max_session_female AS female_score
FROM period p
LEFT JOIN attendance a
    ON p.period_id = a.period_id
LEFT JOIN student s
    ON a.student_id = a.student_id
GROUP BY p.period_id;

请注意，选择 max_session_male 以及 max_session_female 按分组时的列 period_id 可能很可疑。但如果 period_id 是fhe的主键 period table，那就好了。

赞(0）回复(0）举报 2021-06-23

我来回答

如何在其他select查询中包含select查询，其中包含左连接

1条答案

相关问题

热门标签

最新问答