我有一个有点“大脑褪色”走出今天下午的，因此，如果任何人都可以用这个MSSQL查询帮助那将是梦幻般的。
我所说的“四季”的表有三列（还有更多，但该示例无关）：seasonId，日期tariffId
SeasonId是一个独特的密钥。 一个日期只能有一个tariffid，但tariffId可以有很多不同的日期。
例如：
seasonId | date | tariffId
----------------------------------
1 | 1 jan 2009 | 1
2 | 2 jan 2009 | 1
3 | 3 jan 2009 | 2
4 | 4 jan 2009 | 3
5 | 5 jan 2009 | 3
我想有一个查询返回的顺序/日期范围的针对特定tariffId
例如，使用上面的数据，它将返回以下内容：
FromDate | ToDate | TariffId
-----------------------------------
1 | Jan 2009 2 | Jan 2009 1
3 | Jan 2009 3 | Jan 2009 2
4 | Jan 2009 5 | Jan 2009 3
这可能吗？
编辑感谢迄今所有的答案！ 我总是惊讶有多远，你得到回应！
然而，我的例子可能数据没有足够复杂的资费可以有1米或多个日期范围
seasonId | date | tariffId
----------------------------------
1 | 1 jan 2009 | 1
2 | 2 jan 2009 | 1
3 | 3 jan 2009 | 2
4 | 4 jan 2009 | 3
5 | 5 jan 2009 | 3
6 | 6 jan 2009 | 1
7 | 7 jan 2009 | 1
8 | 8 jan 2009 | 3
将给：
FromDate | ToDate | TariffId
------------------------------------
1 Jan 2009 | 2 Jan 2009 | 1
3 Jan 2009 | 3 Jan 2009 | 2
4 Jan 2009 | 5 Jan 2009 | 3
6 Jan 2009 | 7 Jan 2009 | 1
8 Jan 2009 | 8 Jan 2009 | 3
想法？
感谢大家他们对这个帮助！ 这个网站是真棒！
首先是一些测试数据：
create table seasons (seasonId int primary key
, "date" datetime not null unique
, tariffId int not null)
insert into seasons values (1, '2009-01-01', 1)
insert into seasons values (2, '2009-01-02', 1)
insert into seasons values (3, '2009-01-03', 2)
insert into seasons values (4, '2009-01-04', 3)
insert into seasons values (5, '2009-01-05', 3)
insert into seasons values (6, '2009-01-06', 1)
insert into seasons values (7, '2009-01-07', 1)
insert into seasons values (8, '2009-01-08', 3)
-- add a tarrif with a datespan larger than 2
insert into seasons values (9, '2009-01-09', 4)
insert into seasons values (10, '2009-01-10', 4)
insert into seasons values (11, '2009-01-11', 4)
建立在戴夫·巴克的回答，内嵌视图中添加ROW_NUMBER（），所以我们知道这是第一个最小值，这是第二等的tariffId。 （实际上，因为一个日期不能有一个以上的tariffId，我们不需要通过tariffId分区）。
SELECT MinValues.Seasonid, MinValues.Date, MaxValues.Date, MaxValues.tariffid
FROM (
SELECT *, row_number() over (partition by tariffId order by "date") as RN
FROM [dbo].[Seasons] tbl1
WHERE NOT EXISTS (SELECT *
FROM [dbo].[Seasons] tbl2
WHERE tbl1.seasonid - tbl2.seasonid = 1
AND tbl1.tariffId = tbl2.tariffId)) as minValues
JOIN (
SELECT *, row_number() over (partition by tariffId order by "date") as RN
FROM [dbo].[Seasons] tbl1
WHERE NOT EXISTS (SELECT *
FROM [dbo].[Seasons] tbl2
WHERE tbl2.seasonid - tbl1.seasonid = 1
AND tbl1.tariffId = tbl2.tariffId)) as maxValues
ON MinValues.TariffId = MaxValues.tariffId
and MinValues.RN = MaxValues.RN
order by MinValues.Date
结果：
1 2009-01-01 00:00:00.000 2009-01-02 00:00:00.000 1
3 2009-01-03 00:00:00.000 2009-01-03 00:00:00.000 2
4 2009-01-04 00:00:00.000 2009-01-05 00:00:00.000 3
6 2009-01-06 00:00:00.000 2009-01-07 00:00:00.000 1
8 2009-01-08 00:00:00.000 2009-01-08 00:00:00.000 3
9 2009-01-09 00:00:00.000 2009-01-11 00:00:00.000 4
SELECT min(date) as FromDate, MAX(date) as ToDate, tarifid
FROM seasons
GROUP BY tarifID
应该这样做。
SELECT min(date) as FromDate,MAX(date) as ToDate, tariffid FROM(
select s.*,ISNULL(
(SELECT MAX(seasonID) FROM seasons s1 WHERE s.tariffid <> s1.tariffid AND s1.seasonID < s.seasonID),0) as ranks from seasons s)r
GROUP BY tariffID,ranks
也许这？
select min(fromdate) as FromDate, max(todate) as ToDate, tarifid
from (
select min(date) as fromdate, null as todate, tarifid
from seasons
group by tarifid
union
select null, max(date), tarifid
from seasons
group by tarifid
) q
group by tarifid
它看起来像你找到tariffId的顺序出现，然后找到在连续发生日期的最小值和最大值。 为您的样品数据下面的作品，但我怀疑最终的连接需要一些调整，因为它觉得臭。
SELECT MinValues.Seasonid, MinValues.Date, MaxValues.Date, MaxValues.tariffid
FROM (
SELECT *
FROM [dbo].[Seasons] tbl1
WHERE NOT EXISTS (SELECT *
FROM [dbo].[Seasons] tbl2
WHERE tbl1.seasonid - tbl2.seasonid = 1
AND tbl1.tariffId = tbl2.tariffId)) as minValues
JOIN (
SELECT *
FROM [dbo].[Seasons] tbl1
WHERE NOT EXISTS (SELECT *
FROM [dbo].[Seasons] tbl2
WHERE tbl2.seasonid - tbl1.seasonid = 1
AND tbl1.tariffId = tbl2.tariffId)) as maxValues
ON MinValues.TariffId = MaxValues.tariffId
AND (MinValues.SeasonId = MaxValues.Seasonid or MinValues.SeasonId +1 = MaxValues.Seasonid)
