T-SQL 多重分组

want to group like product, price, min(startdate), max(startdate) but should have grouping in start date and end date as well........ something like below

想要的结果

Apples  4.9     2010-03-01 00:00:00.000     2010-03-02 00:00:00.000
Apples  2.5     2010-03-03 00:00:00.000     2010-03-03 00:00:00.000
Apples  4.9     2010-03-05 00:00:00.000     2010-03-09 00:00:00.000
Apples  2.5     2010-03-10 00:00:00.000     2010-03-10 00:00:00.000
Apples  4.9     2010-03-11 00:00:00.000     2010-03-16 00:00:00.000

推荐答案

我的方法.

数据:

create table t ( producte varchar(50), 
                 price money, 
                 start_date date,
                 end_date date);

insert into t values
( 'apple', 4.9, '2012-01-01', '2012-01-01' ),
( 'apple', 4.9, '2012-01-02', '2012-01-02' ),
( 'apple', 8, '2012-01-04', '2012-01-04' ),
( 'cat', 5, '2012-01-01', '2012-01-01' ),
( 'cat', 6, '2012-01-02', '2012-01-02' ),
( 'cat', 6, '2012-01-03', '2012-01-03' );

查询:

with start_dates as (
  select 
    t.producte, t.price, t.start_date, t.end_date, t.start_date as gr_date    
  from 
    t left outer join 
    t t1 on 
        t.price = t1.price and                         --new
        t.producte = t1.producte and
        t.start_date = dateadd(day,1, t1.end_date )
  where t1.producte is null
  union all
  select 
      t.producte, t.price, t.start_date,t. end_date, gr_date
  from
      t inner join 
      start_dates t1 on  
        t.price = t1.price and                         --new
        t.producte = t1.producte and
        t.start_date = dateadd(day,1, t1.end_date )
)
select t.producte, t.price , min( t.start_date ), max( t.end_date )
from start_dates t
group by  t.producte, gr_date  ,t.price

结果:

| PRODUCTE | PRICE |   COLUMN_2 |   COLUMN_3 |
----------------------------------------------
|    apple |   4.9 | 2012-01-01 | 2012-01-02 |
|    apple |     8 | 2012-01-04 | 2012-01-04 |
|      cat |     5 | 2012-01-01 | 2012-01-01 |
|      cat |     6 | 2012-01-02 | 2012-01-03 |

说明

这是一个递归 CTE 表达式.基本查询获取每组价格的初始日期.递归查询查找具有此价格的最后一条数据.