如何在不使用 join 或 cte 的情况下在同一查询中使用动态生成的列

2022-01-23 00:00:00 sql subquery mysql

我正在学习 SQL.我有一种情况，我必须使用我在运行时创建的同一列.该表是 患者，只有 2 列，分别是 dateOfCheckup 和 duration 以分钟为单位.

I'm learning SQL. I've a situation where I've to use the same column which I created in runtime. The table is patient with 2 columns only which are dateOfCheckup and duration in minutes.

+---------------+-----------------+
| dateOfCheckup |   duration      | 
+---------------+-----------------+
|   2020-05-28  |  30 min         | 
|   2020-05-29  |  30 min         |
|   2020-05-30  |  1 hour         | 
|   2020-06-03  |  1 hour 30 min  | 
|   2020-06-05  |  30 min         |
|   2020-07-21  |  1 hour         |
|   2020-07-22  |  1 hour 30 min  | 
|   2020-07-28  |  1 hour 30 min  | 
+---------------+-----------------+

现在我将在运行时再创建 1 列，即 minutes(只是将 duration 转换为整数值的总分钟数):

Now I'll create 1 more column in run time i.e. minutes (which is just duration converted to total minutes as integer value) with this query:

select dateOfCheckup, duration,
((case when duration like '% hour%' then substring_index(duration, ' hour', 1) * 60 else 0 end) + (case when duration like '%min%' then substring_index(substring_index(duration, ' min', 1), ' ', -1) + 0 else 0 end)) as minutes from patient;

此查询运行良好.查看结果:

This query is working perfectly. See the result:

+---------------+-----------------+-----------------+
| dateOfCheckup |  duration       |   minutes       |
+---------------+-----------------+-----------------+
|   2020-05-28  |  30 min         |   30            | 
|   2020-05-29  |  30 min         |   30            |
|   2020-05-30  |  1 hour         |   60            |
|   2020-06-03  |  1 hour 30 min  |   90            |
|   2020-06-05  |  30 min         |   30            |
|   2020-07-21  |  1 hour         |   60            |
|   2020-07-22  |  1 hour 30 min  |   90            |
|   2020-07-28  |  1 hour 30 min  |   90            |
+---------------+-----------------+-----------------+

我的问题是，如果我想在同一个查询中使用这个新创建的列 minutes 来处理其他任务，例如 group by、order by、sum、avg 等.我只是询问是否有这样的场景，那么我们如何实现这一点.我尝试使用 sum(minutes).错误是:

My question is, What if I want to use this newly created column minutes in the same query for other tasks such as group by, order by, sum, avg, etc. I'm just asking if there's a scenario like this, then how do we achieve this. I tried using sum(minutes). The error is:

字段列表"中的未知列分钟"

Unknown column 'minutes' in 'field list'

这是一个db<>fiddle.请帮帮我.

推荐答案

你必须把当前查询当作子查询，然后对结果求和

You have to treat current query as a sub query and then sum the result

SELECT SUM(minutes) AS total_time 
FROM (
    select dateOfCheckup, duration, 
    ((case when duration like '% hour%' then substring_index(duration, ' hour', 1) * 60 else 0 end) + 
    (case when duration like '%min%' then substring_index(substring_index(duration, ' min', 1), ' ', -1) + 0 else 0 end)
    ) as minutes 
from patient
) AS a;

相关文章