将多个值分组为同一组

I have a table similar to this:

我有一个类似的表格:

ID      WEEK
1       1
2       1
3       1
4       2
5       2
6       3
7       3
8       3

This is my current query:

这是我当前的问题:

SELECT COUNT(*), `week` FROM data GROUP BY `week`

Here's an sqlfiddle http://sqlfiddle.com/#!2/bfdb6/2/0

这里有一个sqlfiddle http://sqlfiddle.com/ ! 2 / bfdb6/2/0

What I need is to group rows every 2 weeks into the same COUNT.

我需要的是每两周将行分组到同一个计数中。

So instead of this:

而不是:

COUNT(*)    WEEK
3           1
2           2
3           3
3           4

I'd get:

我得到:

COUNT(*)    WEEK
5           1
5           2
6           3
3           4

Where every week also has the next weeks COUNT added to it.

每周也会有下一周的统计增加。

Question amended:

修改问题:

I should have been clear about what I really needed.

我应该清楚我真正需要的是什么。

Rather than grouping weeks 1 and 3, what I need is grouping by every 2 weeks.

与其把第1周和第3周分组，我需要的是每两周分组一次。

So group by weeks 1 and 2, 2 and 3, 3 and 4, 4 and 5, etc

第1和2、2、3、3、4、4、5等等。

Automatically if possible, but the sql could be generated outside the query for the week groupings.

如果可能的话，可以自动生成sql，但是可以在查询之外生成sql。

Thanks.

谢谢。

3 个解决方案

#1

I think I have it for you...

我想这是给你的……

First, get distinct weeks available from the data... Then, join to the data but specifically on EITHER week or week +1

首先，从数据中获得明显的可用周数……然后，加入到数据中，但具体是在一周或一周+1中。

I applied this to your existing SQLFiddle and it appeared to work.

我把它应用到你现有的SQLFiddle中，它似乎起作用了。

select
      JustWeeks.`week`,
      Count(*) as TwoWeekSum
   from
      ( select distinct
              `week`
           from
              data ) JustWeeks
      JOIN data 
         ON JustWeeks.`week` = data.`week`
         OR JustWeeks.`week` +1 = data.`week`
   group by
      JustWeeks.`week`

#2

you may look for this

你可以找这个

   SELECT 
   case When week In  (1,3) Then ( select count(week) from data
                           where week in (1,3) ) 
   else ( select count(week) from data
                           where week =2 ) end count , week
   FROM data 
   group by count
  order by count desc

DEMO HERE

演示

output

输出

  COUNT    week
    6       1
    2       2

#3

create a case expression that represents the group buckets:

创建表示组桶的case表达式:

SELECT COUNT(*), case When week In (1,3) Then 'W13' Else 'W2' End
FROM data 
GROUP BY case When week In (1,3) Then 'W13' Else 'W2' End

Based on edited post, if week is integer, dividing by 2 will create what you want, since

基于编辑后的文章，如果周是整数，除以2将创建你想要的，因为

 week    week/2
   0         0
   1         0
   2         1
   3         1
   4         2
   5         2

....... .etc.

.......等。

so try this then:

那么试试这个:

SELECT COUNT(*), week/2 
FROM data 
GROUP BY week/2

#1