使用变量时如何在查询中使用 GROUP BY [英] How to use GROUP BY in a query while using variables

问题描述

我使用的是 MySql V5.7.我有一张表PatientAppointment.我需要 GROUP BY CDRId 这是同一个表中的一列.这是查询:

SELECT AppointmentDateTime,期间，分钟，@prev_month := BillingMonth BillingMonth,@prev_total := 总计从(选择约会日期时间，期间，@cur_dur := ((当持续时间为 '% hour%' 然后 substring_index(duration, 'hour', 1) * 60 else 0 end) +(当持续时间像 '%min%' 然后 substring_index(substring_index(duration, ' min', 1), ' ', -1) + 0 else 0 end)) 作为分钟，CASE WHEN @year_month = date_format(AppointmentDateTime, '%Y-%m')然后@cum_sum := @cum_sum + @cur_dur否则@cum_sum := @cur_dur结束总，@year_month := date_format(AppointmentDateTime, '%Y-%m') BillingMonth来自 PatientAppointment, (SELECT @year_month:='', @cum_sum:=0, @cur_dur:=0) 变量按 CDRId 分组 <------ 错误ORDER BY AppointmentDateTime) 子查询，(SELECT @prev_month:=0, @prev_total:=0) 变量ORDER BY AppointmentDateTime，总计

这是一个有效的

我收到此错误:

<块引用>

您的 SQL 语法有错误；查看与您的 MySQL 服务器版本相对应的手册...

解决方案

遗憾的是，SQL 无法合并表格单元格"；就像您在屏幕截图中显示的那样.SQL 不像电子表格.

您必须获取行，然后在应用程序代码中确定您希望如何对它们进行分组.

(你在另一个话题上让我过来回答.)

I'm using MySql V5.7. I have a table PatientAppointment. I need to GROUP BY CDRId which is a column in the same table. Here's the query:

SELECT AppointmentDateTime,
       duration,
       minutes,
       @prev_month := BillingMonth BillingMonth,
       @prev_total := total total
FROM (select AppointmentDateTime,
             duration,
             @cur_dur := ((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,

             CASE WHEN @year_month = date_format(AppointmentDateTime, '%Y-%m')
                  THEN @cum_sum := @cum_sum + @cur_dur
                  ELSE @cum_sum := @cur_dur
                  END total,
             @year_month := date_format(AppointmentDateTime, '%Y-%m') BillingMonth

      from PatientAppointment, (SELECT @year_month:='', @cum_sum:=0, @cur_dur:=0) variables
      GROUP BY CDRId  <------ ERROR
      ORDER BY AppointmentDateTime) subquery, 
(SELECT @prev_month:=0, @prev_total:=0) variable
ORDER BY AppointmentDateTime, total

Here is a working db<>fiddle. Please help me. I want the entire result set to be grouped by CDRId

If we cannot use GROUP BY clause here, can we do it some logic changes here.

This is the type of grouping I want. See this:

I'm getting this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server v...

解决方案

Unfortunately, SQL has no way of "merging table cells" like you show in your screenshot. SQL is not like a spreadsheet.

You must fetch the rows and then work out in application code how you want to group them.

(You asked me on another thread to come here and give an answer.)

这篇关于使用变量时如何在查询中使用 GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！