求一SQL语句，从原始表得到想要的数据！！！！！！！
要求：
以字段SESSION_ID分组做GROUP，获取最小的VISIT_TIME,和最小VISIT_TIME对应的REFERRER,还有如果URL='/login/reg.aspx'，就把结果记录中的REGFLG字段置为1。
原始表
VISIT_TIMESESSION_IDURLREFERRER
2011/11/9 9:28FE6FE647CD8E4D79A7B878A57C954CE9/login/login.aspxNULL
2011/11/9 9:19CCFDD3ED891D42F3A03055A975F4A2C1/Content/418.htmlhttp://help.sina.com/payarea.html?page=/Content/21.html
2011/11/9 9:27CCFDD3ED891D42F3A03055A975F4A2C1/http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7
2011/11/9 23:421752F55AB02C4C79B50E375B3DAEB85E/Login/Login.aspxNULL
2011/11/9 9:37FE6FE647CD8E4D79A7B878A57C954CE9/shopping/shopping.aspxhttp://item.sina.com/0120958.html
2011/11/9 10:00FE6FE647CD8E4D79A7B878A57C954CE9/Order/http://my.sina.com/order/
2011/11/9 9:31FE6FE647CD8E4D79A7B878A57C954CE9/Content/38.htmlhttp://help.sina.com/payarea.html?page=/Content/21.html
2011/11/9 9:59FE6FE647CD8E4D79A7B878A57C954CE9/order/NULL
2011/11/9 23:471752F55AB02C4C79B50E375B3DAEB85E/http://s.sina.com/2776.html
2011/11/9 23:451752F55AB02C4C79B50E375B3DAEB85E/0095787.htmlhttp://item.sina.com/0067878.html?ref=Search_Browse_1_0067878
2011/11/9 23:421752F55AB02C4C79B50E375B3DAEB85E/http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7
2011/11/9 23:561752F55AB02C4C79B50E375B3DAEB85E/0159577.htmlhttp://fashion.sina.com/search/?navtype=02&cateid=5142
2011/11/9 23:471752F55AB02C4C79B50E375B3DAEB85E/channel/men.htmlhttp://www.sina.com/
2011/11/9 23:581752F55AB02C4C79B50E375B3DAEB85E/0159577.htmlhttp://fashion.sina.com/search/?navtype=05&cateid=5142
2011/11/9 9:28FE6FE647CD8E4D79A7B878A57C954CE9/NULL
2011/11/9 23:591752F55AB02C4C79B50E375B3DAEB85E/search/http://fashion.sina.com/search/?navtype=05&cateid=5142
2011/11/9 9:29FE6FE647CD8E4D79A7B878A57C954CE9/0120958.htmlhttp://weiyi.sina.com/
2011/11/9 9:33FE6FE647CD8E4D79A7B878A57C954CE9/Content/21.htmlhttp://help.sina.com/Content/21.html
2011/11/9 9:32FE6FE647CD8E4D79A7B878A57C954CE9/Content/21.htmlhttp://help.sina.com/Content/38.html
2011/11/9 9:33FE6FE647CD8E4D79A7B878A57C954CE9/Content/21.htmlhttp://help.sina.com/Content/21.html
2011/11/9 23:541752F55AB02C4C79B50E375B3DAEB85E/http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7
2011/11/9 9:27CCFDD3ED891D42F3A03055A975F4A2C1/login/login.aspxNULL
2011/11/9 10:00FE6FE647CD8E4D79A7B878A57C954CE9/http://my.sina.com/Order/
2011/11/9 9:24CCFDD3ED891D42F3A03055A975F4A2C1/shopping/shopping.aspxhttp://help.sina.com/Content/418.html
2011/11/9 9:20CCFDD3ED891D42F3A03055A975F4A2C1/login/reg.aspxNULL
2011/11/9 23:441752F55AB02C4C79B50E375B3DAEB85E/0067878.htmlhttp://s.sina.com/2776.html
2011/11/9 23:491752F55AB02C4C79B50E375B3DAEB85E/1247.htmlhttp://s.sina.com/1247.html
2011/11/9 9:28FE6FE647CD8E4D79A7B878A57C954CE9/http://www.sina.com/
2011/11/9 9:58FE6FE647CD8E4D79A7B878A57C954CE9/Shopping/Shopping_Complete.aspxhttps://shopping.sina.com/shoppingcar/index
2011/11/9 9:58FE6FE647CD8E4D79A7B878A57C954CE9/Shopping/Shopping_Complete.aspxhttps://shopping.sina.com/shoppingcar/index
2011/11/9 9:14CCFDD3ED891D42F3A03055A975F4A2C1/0120958.htmlhttp://weiyi.sina.com/
2011/11/9 9:13CCFDD3ED891D42F3A03055A975F4A2C1/http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76
2011/11/9 23:591752F55AB02C4C79B50E375B3DAEB85E/http://fashion.sina.com/search/?navtype=05
2011/11/9 9:13CCFDD3ED891D42F3A03055A975F4A2C1/http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7
2011/11/9 23:451752F55AB02C4C79B50E375B3DAEB85E/0067878.htmlhttp://s.sina.com/2776.html
2011/11/9 9:32FE6FE647CD8E4D79A7B878A57C954CE9/Content/21.htmlhttp://help.sina.com/Content/38.html
2011/11/9 23:471752F55AB02C4C79B50E375B3DAEB85E/1247.htmlhttp://www.sina.com/channel/men.html
2011/11/9 9:27CCFDD3ED891D42F3A03055A975F4A2C1/login/reg.aspxNULL
2011/11/9 9:28FE6FE647CD8E4D79A7B878A57C954CE9/http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76
2011/11/9 23:551752F55AB02C4C79B50E375B3DAEB85E/http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76
2011/11/9 23:561752F55AB02C4C79B50E375B3DAEB85E/0159576.htmlhttp://fashion.sina.com/search/?navtype=02&cateid=5142
2011/11/9 23:501752F55AB02C4C79B50E375B3DAEB85E/1247.htmlhttp://s.sina.com/1247.html?p=2
2011/11/9 23:531752F55AB02C4C79B50E375B3DAEB85E/channel/men.htmlhttp://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76
2011/11/9 9:37FE6FE647CD8E4D79A7B878A57C954CE9/shoppingcar/indexNULL
2011/11/9 23:431752F55AB02C4C79B50E375B3DAEB85E/channel/men.htmlhttp://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76
2011/11/9 23:571752F55AB02C4C79B50E375B3DAEB85E/search/http://fashion.sina.com/
2011/11/9 9:41FE6FE647CD8E4D79A7B878A57C954CE9/category/transferNULL
2011/11/9 9:24CCFDD3ED891D42F3A03055A975F4A2C1/Content/418.htmlNULL
2011/11/9 23:449F64D379138C43E083C589FDB9732FA9/Login/Login.aspxNULL
2011/11/9 23:431752F55AB02C4C79B50E375B3DAEB85E/2776.htmlhttp://www.sina.com/channel/men.html
2011/11/9 23:481752F55AB02C4C79B50E375B3DAEB85E/0137106.htmlhttp://s.sina.com/1247.html
2011/11/9 23:551752F55AB02C4C79B50E375B3DAEB85E/0159607.htmlhttp://fashion.sina.com/search/?navtype=02&cateid=5142
2011/11/9 23:521752F55AB02C4C79B50E375B3DAEB85E/http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7
2011/11/9 23:551752F55AB02C4C79B50E375B3DAEB85E/search/http://fashion.sina.com/
想要得到的结果：
VISIT_TIMESESSION_IDREFERRERREGFLG
2011/11/9 9:13CCFDD3ED891D42F3A03055A975F4A2C1http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B71
2011/11/9 9:28FE6FE647CD8E4D79A7B878A57C954CE9http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe760
2011/11/9 23:421752F55AB02C4C79B50E375B3DAEB85ENULL0
2011/11/9 23:449F64D379138C43E083C589FDB9732FA9NULL0
[解决办法]
--思路是这样select *,REGFLG=(case when CHARINDEX('/login/reg.aspx',URL)>0 then 1 else 0 end)from tb a where not exists(select 1 from tb where SESSION_ID=a.SESSION_ID and VISIT_TIME<a.VISIT_TIME)/*但是具体细节LZ没说，所以请LZ自行调整。比如，如果最小时间相等怎么办？是任意取一条？还是有什么规律？这里LZ自己改。
[解决办法]
select * from(select no=row_number() over(partition by SESSION_ID order by VISIT_TIME), VISIT_TIME,SESSION_ID,REFERRER, REGFLG=case when exists(select 1 from 表 where SESSION_ID=a.SESSION_ID and URL like '%/login/reg.aspx%') then 1 else 0 end from 表 a) t where no=1
[解决办法]
这样最接近:
;with cte as(select *,row_number()over(order by visit_time)rn from tb)select visit_time,session_id,referrer,(case when exists(select 1 from cte where session_id=a.session_id and url='/login/reg.aspx') then 1 else 0 end) as REGFLGfrom cte a where not exists(select 1 from cte where session_id=a.session_id and rn<a.rn)/*visit_time session_id referrer REGFLG----------------------- ---------------------------------------- ---------------------------------------------------------------- -----------2011-11-09 09:13:00.000 CCFDD3ED891D42F3A03055A975F4A2C1 http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76 12011-11-09 09:28:00.000 FE6FE647CD8E4D79A7B878A57C954CE9 NULL 02011-11-09 23:42:00.000 1752F55AB02C4C79B50E375B3DAEB85E NULL 02011-11-09 23:44:00.000 9F64D379138C43E083C589FDB9732FA9 NULL 0(4 行受影响)*/