本文介绍了在MySQL中按周分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

表结构是

Table name: btq_user

name, email, kall, state_id, datain

现在我想按每周计数在(51,20,46)中具有kall = 1或state_id的数字记录.无论年份是否更改,我都只需要每周(周一至周日)的结果.例如,假设2012年12月31日为星期一,而2013年6月1日为星期日,那么结果应包括一周.不管年份是否更改,都只需要数周即可.

Now i want to count number records which has kall = 1 or state_id in( 51, 20, 46) by weekly.i need the results only by weekly (Mon - Sun) no matter if year changes. lets say for example 31-12-2012 is Monday and 6-1-2013 is Sunday so result should include that as a week. no matter if year changes it should count only with weeks.

这是我尝试过的但无法正常工作.

This is what i tried but not working.

SELECT
count( if( A.state_id = 51 or A.state_id = 20 or A.state_id = 46, A.state_id,
null  ) ) AS state_total, count( if( A.kall =1, A.kall, null ) ) AS appointment,
CONCAT( WEEK( A.datain) , " -
", YEAR( A.datain ) ) AS week
FROM btq_user A
GROUP BY week
ORDER BY A.datain ASC

是否有可能显示结果的星期数(31-12-2012-6-1-2013)?

also is there any possibility to display weeks ( 31-12-2012 - 6-1-2013 ) with results ?

感谢您阅读我的问题.

推荐答案

两个步骤:

一个,您需要进行一周截断操作-将获取您的DATETIME项,并在前一个星期日归还午夜(如果您的业务规则说该周从星期日开始).

One, you need a week-truncate operation -- that will take your DATETIME item and give back midnight on the preceding Sunday (if your business rule says that the week begins on Sunday).

这将成为一个合适的GROUP BY项目. WEEK()/YEAR()骇客不适合此功能.每年的最后/第一周确实很乱.

That becomes a suitable GROUP BY item. The WEEK() / YEAR() hack isn't suitable for this. It really makes a mess in the last/first week of each year.

根据我的经验,这种表达方式会在周日至周六为您完成一周截断的技巧.

In my experience, this exxpression will do the week-truncate trick for you, Sunday - Saturday,

  FROM_DAYS(TO_DAYS(TIMESTAMP) -MOD(TO_DAYS(TIMESTAMP) -1, 7))

要获取星期一-星期几,请使用此表达式.

To get Monday - Sunday weeks, use this expression.

  FROM_DAYS(TO_DAYS(TIMESTAMP) -MOD(TO_DAYS(TIMESTAMP) -2, 7))

所以您可以这样做.

  SELECT COUNT(whatever), SUM(whatelse),
         FROM_DAYS(TO_DAYS(event_time) -MOD(TO_DAYS(event_time) -1, 7)) as WEEKSTART,
    FROM TABLE
   GROUP BY FROM_DAYS(TO_DAYS(event_time) -MOD(TO_DAYS(event_time) -1, 7))

如何在MySQL中按周分组?

第二,您需要在截断的日期前增加六天,以便可以显示每周的最后一天和第一天.

Second, you need to add six days to that truncated date, so you can display the last day of each week along with the first day.

这是使用嵌套查询的一种好方法

This is a good way to do that, with a nested query

SELECT whats, elses, weekstart, weekstart + INTERVAL 6 DAY AS weekend
  FROM (
  SELECT COUNT(whatever) AS whats, SUM(whatelse) AS elses,
         FROM_DAYS(TO_DAYS(event_time) -MOD(TO_DAYS(event_time) -1, 7)) AS weekstart,
    FROM TABLE
   GROUP BY FROM_DAYS(TO_DAYS(event_time) -MOD(TO_DAYS(event_time) -1, 7))
  ) AS summary
ORDER BY weekstart

这样做很多吗?我建议您创建一个存储的TRUNC_WEEK函数.

Doing lots of this? I suggest you create a stored TRUNC_WEEK function.

这篇关于在MySQL中按周分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-23 00:08
查看更多