求一个sql语句，看起来好简单，我怎么不会做

有个表：
id Saler date .....
统计的是销售签单的表
1.某个Saler 现在是连续几周签单了（每周都有这个Saler的数据，没有间断）
2.历史上最长的连续每周签单的Saler是谁

id 是 主键 int
saler 是销售员的名字 环境是Int 这里是 nvchar 为了简化
date是 签约的时期，这里我要按周统计 ，并且是以国内的周一开始一周

这两个问题 第一个类似于连续登陆日期（最少要判断今天有没登陆吧）
第二个 类似于历史连续登陆日期最长

SET DATEFIRST 1;
WITH x0
AS ( SELECt 1 AS id ,
'Jack' AS saler ,
CAST('2015-08-23' AS DATETIME) AS ctime
UNIOn ALL
SELECT 2 AS id ,
'Mike' AS saler ,
CAST('2015-08-24' AS DATETIME) AS ctime
UNIOn ALL
SELECT 3 AS id ,
'Jone' AS saler ,
CAST('2015-08-25' AS DATETIME) AS ctime
UNIOn ALL
SELECT 4 AS id ,
'Jack' AS saler ,
CAST('2015-08-26' AS DATETIME) AS ctime
UNIOn ALL
SELECT 5 AS id ,
'Jone' AS saler ,
CAST('2015-09-11' AS DATETIME) AS ctime
UNIOn ALL
SELECT 6 AS id ,
'Jack' AS saler ,
CAST('2015-09-2' AS DATETIME) AS ctime
UNIOn ALL
SELECT 7 AS id ,
'Mike' AS saler ,
CAST('2015-09-04' AS DATETIME) AS ctime
UNIOn ALL
SELECT 8 AS id ,
'Jack' AS saler ,
CAST('2015-09-11' AS DATETIME) AS ctime
UNIOn ALL
SELECT 9 AS id ,
'Jack' AS saler ,
CAST('2015-09-23' AS DATETIME) AS ctime
UNIOn ALL
SELECT 10 AS id ,
'Mike' AS saler ,
CAST('2015-09-12' AS DATETIME) AS ctime
)
SELECT * INTO #t FROM x0
SELECt saler,gp,COUNT(*) AS cn FROM(
SELECt saler,dweek-ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS gp FROM(
SELECt saler ,
MIN(ctime) AS ctime_min ,
DATEPART(week, ctime) AS dweek
FROM #t
GROUP BY saler ,
DATEPART(week, ctime))t)t GROUP BY saler,gp

--不能在字符把变量直接加到sql语句中的,要先生成sql语句的字符串再执行 declare @j int declare @strsql nvarchar(200) set @j=5 set @strsql='select top '+convert(nvarchar,@j)+' stuno from stumarks where writeexam = 60' exec(@strsql)

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息