求一SQL语句，从原始表得到想要的数据！！！！！！！

Question

要求：
以字段SESSION_ID分组做GROUP，获取最小的VISIT_TIME,和最小VISIT_TIME对应的REFERRER,还有如果URL='/login/reg.aspx'，就把结果记录中的REGFLG字段置为1。
原始表
VISIT_TIME SESSION_ID URL REFERRER
2011/11/9 9:28 FE6FE647CD8E4D79A7B878A57C954CE9 /login/login.aspx NULL
2011/11/9 9:19 CCFDD3ED891D42F3A03055A975F4A2C1 /Content/418.html http://help.sina.com/payarea.html?page=/Content/21.html
2011/11/9 9:27 CCFDD3ED891D42F3A03055A975F4A2C1 / http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7
2011/11/9 23:42 1752F55AB02C4C79B50E375B3DAEB85E /Login/Login.aspx NULL
2011/11/9 9:37 FE6FE647CD8E4D79A7B878A57C954CE9 /shopping/shopping.aspx http://item.sina.com/0120958.html
2011/11/9 10:00 FE6FE647CD8E4D79A7B878A57C954CE9 /Order/ http://my.sina.com/order/
2011/11/9 9:31 FE6FE647CD8E4D79A7B878A57C954CE9 /Content/38.html http://help.sina.com/payarea.html?page=/Content/21.html
2011/11/9 9:59 FE6FE647CD8E4D79A7B878A57C954CE9 /order/ NULL
2011/11/9 23:47 1752F55AB02C4C79B50E375B3DAEB85E / http://s.sina.com/2776.html
2011/11/9 23:45 1752F55AB02C4C79B50E375B3DAEB85E /0095787.html http://item.sina.com/0067878.html?ref=Search_Browse_1_0067878
2011/11/9 23:42 1752F55AB02C4C79B50E375B3DAEB85E / http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7
2011/11/9 23:56 1752F55AB02C4C79B50E375B3DAEB85E /0159577.html http://fashion.sina.com/search/?navtype=02&cateid=5142
2011/11/9 23:47 1752F55AB02C4C79B50E375B3DAEB85E /channel/men.html http://www.sina.com/
2011/11/9 23:58 1752F55AB02C4C79B50E375B3DAEB85E /0159577.html http://fashion.sina.com/search/?navtype=05&cateid=5142
2011/11/9 9:28 FE6FE647CD8E4D79A7B878A57C954CE9 / NULL
2011/11/9 23:59 1752F55AB02C4C79B50E375B3DAEB85E /search/ http://fashion.sina.com/search/?navtype=05&cateid=5142
2011/11/9 9:29 FE6FE647CD8E4D79A7B878A57C954CE9 /0120958.html http://weiyi.sina.com/
2011/11/9 9:33 FE6FE647CD8E4D79A7B878A57C954CE9 /Content/21.html http://help.sina.com/Content/21.html
2011/11/9 9:32 FE6FE647CD8E4D79A7B878A57C954CE9 /Content/21.html http://help.sina.com/Content/38.html
2011/11/9 9:33 FE6FE647CD8E4D79A7B878A57C954CE9 /Content/21.html http://help.sina.com/Content/21.html
2011/11/9 23:54 1752F55AB02C4C79B50E375B3DAEB85E / http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7
2011/11/9 9:27 CCFDD3ED891D42F3A03055A975F4A2C1 /login/login.aspx NULL
2011/11/9 10:00 FE6FE647CD8E4D79A7B878A57C954CE9 / http://my.sina.com/Order/
2011/11/9 9:24 CCFDD3ED891D42F3A03055A975F4A2C1 /shopping/shopping.aspx http://help.sina.com/Content/418.html
2011/11/9 9:20 CCFDD3ED891D42F3A03055A975F4A2C1 /login/reg.aspx NULL
2011/11/9 23:44 1752F55AB02C4C79B50E375B3DAEB85E /0067878.html http://s.sina.com/2776.html
2011/11/9 23:49 1752F55AB02C4C79B50E375B3DAEB85E /1247.html http://s.sina.com/1247.html
2011/11/9 9:28 FE6FE647CD8E4D79A7B878A57C954CE9 / http://www.sina.com/
2011/11/9 9:58 FE6FE647CD8E4D79A7B878A57C954CE9 /Shopping/Shopping_Complete.aspx https://shopping.sina.com/shoppingcar/index
2011/11/9 9:58 FE6FE647CD8E4D79A7B878A57C954CE9 /Shopping/Shopping_Complete.aspx https://shopping.sina.com/shoppingcar/index
2011/11/9 9:14 CCFDD3ED891D42F3A03055A975F4A2C1 /0120958.html http://weiyi.sina.com/
2011/11/9 9:13 CCFDD3ED891D42F3A03055A975F4A2C1 / http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76
2011/11/9 23:59 1752F55AB02C4C79B50E375B3DAEB85E / http://fashion.sina.com/search/?navtype=05
2011/11/9 9:13 CCFDD3ED891D42F3A03055A975F4A2C1 / http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7
2011/11/9 23:45 1752F55AB02C4C79B50E375B3DAEB85E /0067878.html http://s.sina.com/2776.html
2011/11/9 9:32 FE6FE647CD8E4D79A7B878A57C954CE9 /Content/21.html http://help.sina.com/Content/38.html
2011/11/9 23:47 1752F55AB02C4C79B50E375B3DAEB85E /1247.html http://www.sina.com/channel/men.html
2011/11/9 9:27 CCFDD3ED891D42F3A03055A975F4A2C1 /login/reg.aspx NULL
2011/11/9 9:28 FE6FE647CD8E4D79A7B878A57C954CE9 / http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76
2011/11/9 23:55 1752F55AB02C4C79B50E375B3DAEB85E / http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76
2011/11/9 23:56 1752F55AB02C4C79B50E375B3DAEB85E /0159576.html http://fashion.sina.com/search/?navtype=02&cateid=5142
2011/11/9 23:50 1752F55AB02C4C79B50E375B3DAEB85E /1247.html http://s.sina.com/1247.html?p=2
2011/11/9 23:53 1752F55AB02C4C79B50E375B3DAEB85E /channel/men.html http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76
2011/11/9 9:37 FE6FE647CD8E4D79A7B878A57C954CE9 /shoppingcar/index NULL
2011/11/9 23:43 1752F55AB02C4C79B50E375B3DAEB85E /channel/men.html http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76
2011/11/9 23:57 1752F55AB02C4C79B50E375B3DAEB85E /search/ http://fashion.sina.com/
2011/11/9 9:41 FE6FE647CD8E4D79A7B878A57C954CE9 /category/transfer NULL
2011/11/9 9:24 CCFDD3ED891D42F3A03055A975F4A2C1 /Content/418.html NULL
2011/11/9 23:44 9F64D379138C43E083C589FDB9732FA9 /Login/Login.aspx NULL
2011/11/9 23:43 1752F55AB02C4C79B50E375B3DAEB85E /2776.html http://www.sina.com/channel/men.html
2011/11/9 23:48 1752F55AB02C4C79B50E375B3DAEB85E /0137106.html http://s.sina.com/1247.html
2011/11/9 23:55 1752F55AB02C4C79B50E375B3DAEB85E /0159607.html http://fashion.sina.com/search/?navtype=02&cateid=5142
2011/11/9 23:52 1752F55AB02C4C79B50E375B3DAEB85E / http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7
2011/11/9 23:55 1752F55AB02C4C79B50E375B3DAEB85E /search/ http://fashion.sina.com/
想要得到的结果：VISIT_TIME SESSION_ID REFERRER REGFLG
2011/11/9 9:13 CCFDD3ED891D42F3A03055A975F4A2C1 http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7 1
2011/11/9 9:28 FE6FE647CD8E4D79A7B878A57C954CE9 http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76 0
2011/11/9 23:42 1752F55AB02C4C79B50E375B3DAEB85E NULL 0
2011/11/9 23:44 9F64D379138C43E083C589FDB9732FA9 NULL 0

Answer 1

--思路是这样
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自己改。

Answer 2

select * from
(select no=row_number() over(partition by SESSION_ID order by VISIT_TIME),
        VISIT_TIME,SESSION_ID,REFERRER,
        REGFLG=case when URL='/login/reg.aspx' then 1 else 0 end
 from 表
) t where no=1

Answer 3

select *,case when url=''/login/reg.aspx' then '1'esle '0' end REGFLG
 from (select min(visit_time) ,session_id,referrerfrom group by session_id,referrer)a

Answer 4

select
 * 
from
(select
   no=row_number() over(partition by SESSION_ID order by VISIT_TIME),
   VISIT_TIME,SESSION_ID,REFERRER,
   REGFLG=case when charindex('/login/reg.aspx',url)>0 then 1 else 0 end
 from
   tb
) t 
where
 no=1

Answer 5

这个语句算出来的REGFLG都是0

Answer 6

select * from
(select no=row_number() over(partition by SESSION_ID order by VISIT_TIME),
        VISIT_TIME,SESSION_ID,REFERRER,
        REGFLG=case when URL like '%/login/reg.aspx%' then 1 else 0 end
 from 表
) t where no=1

Answer 7

select *,case when charindex('/login/reg.aspx',url)>0 then 1 else 0 end REGFLG
 from (select min(visit_time) ,session_id,referrerfrom group by session_id,referrer)a

Answer 8

这个语句算出来的REGFLG也都是0

Answer 9

大哥，跟like 和 = 没关系，是no=1的那条记录中REGFLG就是=0，
因为URL='/login/reg.aspx' 的记录不一定在第一条
怎么样把REGFLG SUM一下？

Answer 10

这个肯定不行，group只有session_id，没有referrer!

Answer 11

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

Answer 12

跟1楼的一样，这个语句算出来的REGFLG都是0

Answer 13

你所要求的数据没有满足url=...的怎么可能让REGFLG=1？
自己把数据都显示出来看看不就懂了？

Answer 14

疑惑,结果与楼主说的不一样,而且楼主的时间没有秒,使得同一ID一分钟内有很多访问没法区别:
create table tb(VISIT_TIME datetime,SESSION_ID varchar(40),URL varchar(100),REFERRER varchar(100))
insert into tb select '2011/11/9 9:28','FE6FE647CD8E4D79A7B878A57C954CE9','/login/login.aspx',NULL
insert into tb select '2011/11/9 9:19','CCFDD3ED891D42F3A03055A975F4A2C1','/Content/418.html','http://help.sina.com/payarea.html?page=/Content/21.html'
insert into tb select '2011/11/9 9:27','CCFDD3ED891D42F3A03055A975F4A2C1','/','http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7'
insert into tb select '2011/11/9 23:42','1752F55AB02C4C79B50E375B3DAEB85E','/Login/Login.aspx',NULL
insert into tb select '2011/11/9 9:37','FE6FE647CD8E4D79A7B878A57C954CE9','/shopping/shopping.aspx','http://item.sina.com/0120958.html'
insert into tb select '2011/11/9 10:00','FE6FE647CD8E4D79A7B878A57C954CE9','/Order/','http://my.sina.com/order/'
insert into tb select '2011/11/9 9:31','FE6FE647CD8E4D79A7B878A57C954CE9','/Content/38.html','http://help.sina.com/payarea.html?page=/Content/21.html'
insert into tb select '2011/11/9 9:59','FE6FE647CD8E4D79A7B878A57C954CE9','/order/',NULL
insert into tb select '2011/11/9 23:47','1752F55AB02C4C79B50E375B3DAEB85E','/','http://s.sina.com/2776.html'
insert into tb select '2011/11/9 23:45','1752F55AB02C4C79B50E375B3DAEB85E','/0095787.html','http://item.sina.com/0067878.html?ref=Search_Browse_1_0067878'
insert into tb select '2011/11/9 23:42','1752F55AB02C4C79B50E375B3DAEB85E','/','http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7'
insert into tb select '2011/11/9 23:56','1752F55AB02C4C79B50E375B3DAEB85E','/0159577.html','http://fashion.sina.com/search/?navtype=02&cateid=5142'
insert into tb select '2011/11/9 23:47','1752F55AB02C4C79B50E375B3DAEB85E','/channel/men.html','http://www.sina.com/'
insert into tb select '2011/11/9 23:58','1752F55AB02C4C79B50E375B3DAEB85E','/0159577.html','http://fashion.sina.com/search/?navtype=05&cateid=5142'
insert into tb select '2011/11/9 9:28','FE6FE647CD8E4D79A7B878A57C954CE9','/',NULL
insert into tb select '2011/11/9 23:59','1752F55AB02C4C79B50E375B3DAEB85E','/search/','http://fashion.sina.com/search/?navtype=05&cateid=5142'
insert into tb select '2011/11/9 9:29','FE6FE647CD8E4D79A7B878A57C954CE9','/0120958.html','http://weiyi.sina.com/'
insert into tb select '2011/11/9 9:33','FE6FE647CD8E4D79A7B878A57C954CE9','/Content/21.html','http://help.sina.com/Content/21.html'
insert into tb select '2011/11/9 9:32','FE6FE647CD8E4D79A7B878A57C954CE9','/Content/21.html','http://help.sina.com/Content/38.html'
insert into tb select '2011/11/9 9:33','FE6FE647CD8E4D79A7B878A57C954CE9','/Content/21.html','http://help.sina.com/Content/21.html'
insert into tb select '2011/11/9 23:54','1752F55AB02C4C79B50E375B3DAEB85E','/','http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7'
insert into tb select '2011/11/9 9:27','CCFDD3ED891D42F3A03055A975F4A2C1','/login/login.aspx',NULL
insert into tb select '2011/11/9 10:00','FE6FE647CD8E4D79A7B878A57C954CE9','/','http://my.sina.com/Order/'
insert into tb select '2011/11/9 9:24','CCFDD3ED891D42F3A03055A975F4A2C1','/shopping/shopping.aspx','http://help.sina.com/Content/418.html'
insert into tb select '2011/11/9 9:20','CCFDD3ED891D42F3A03055A975F4A2C1','/login/reg.aspx',NULL
insert into tb select '2011/11/9 23:44','1752F55AB02C4C79B50E375B3DAEB85E','/0067878.html','http://s.sina.com/2776.html'
insert into tb select '2011/11/9 23:49','1752F55AB02C4C79B50E375B3DAEB85E','/1247.html','http://s.sina.com/1247.html'
insert into tb select '2011/11/9 9:28','FE6FE647CD8E4D79A7B878A57C954CE9','/','http://www.sina.com/'
insert into tb select '2011/11/9 9:58','FE6FE647CD8E4D79A7B878A57C954CE9','/Shopping/Shopping_Complete.aspx','https://shopping.sina.com/shoppingcar/index'
insert into tb select '2011/11/9 9:58','FE6FE647CD8E4D79A7B878A57C954CE9','/Shopping/Shopping_Complete.aspx','https://shopping.sina.com/shoppingcar/index'
insert into tb select '2011/11/9 9:14','CCFDD3ED891D42F3A03055A975F4A2C1','/0120958.html','http://weiyi.sina.com/'
insert into tb select '2011/11/9 9:13','CCFDD3ED891D42F3A03055A975F4A2C1','/','http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76'
insert into tb select '2011/11/9 23:59','1752F55AB02C4C79B50E375B3DAEB85E','/','http://fashion.sina.com/search/?navtype=05'
insert into tb select '2011/11/9 9:13','CCFDD3ED891D42F3A03055A975F4A2C1','/','http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7'
insert into tb select '2011/11/9 23:45','1752F55AB02C4C79B50E375B3DAEB85E','/0067878.html','http://s.sina.com/2776.html'
insert into tb select '2011/11/9 9:32','FE6FE647CD8E4D79A7B878A57C954CE9','/Content/21.html','http://help.sina.com/Content/38.html'
insert into tb select '2011/11/9 23:47','1752F55AB02C4C79B50E375B3DAEB85E','/1247.html','http://www.sina.com/channel/men.html'
insert into tb select '2011/11/9 9:27','CCFDD3ED891D42F3A03055A975F4A2C1','/login/reg.aspx',NULL
insert into tb select '2011/11/9 9:28','FE6FE647CD8E4D79A7B878A57C954CE9','/','http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76'
insert into tb select '2011/11/9 23:55','1752F55AB02C4C79B50E375B3DAEB85E','/','http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76'
insert into tb select '2011/11/9 23:56','1752F55AB02C4C79B50E375B3DAEB85E','/0159576.html','http://fashion.sina.com/search/?navtype=02&cateid=5142'
insert into tb select '2011/11/9 23:50','1752F55AB02C4C79B50E375B3DAEB85E','/1247.html','http://s.sina.com/1247.html?p=2'
insert into tb select '2011/11/9 23:53','1752F55AB02C4C79B50E375B3DAEB85E','/channel/men.html','http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76'
insert into tb select '2011/11/9 9:37','FE6FE647CD8E4D79A7B878A57C954CE9','/shoppingcar/index',NULL
insert into tb select '2011/11/9 23:43','1752F55AB02C4C79B50E375B3DAEB85E','/channel/men.html','http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76'
insert into tb select '2011/11/9 23:57','1752F55AB02C4C79B50E375B3DAEB85E','/search/','http://fashion.sina.com/'
insert into tb select '2011/11/9 9:41','FE6FE647CD8E4D79A7B878A57C954CE9','/category/transfer',NULL
insert into tb select '2011/11/9 9:24','CCFDD3ED891D42F3A03055A975F4A2C1','/Content/418.html',NULL
insert into tb select '2011/11/9 23:44','9F64D379138C43E083C589FDB9732FA9','/Login/Login.aspx',NULL
insert into tb select '2011/11/9 23:43','1752F55AB02C4C79B50E375B3DAEB85E','/2776.html','http://www.sina.com/channel/men.html'
insert into tb select '2011/11/9 23:48','1752F55AB02C4C79B50E375B3DAEB85E','/0137106.html','http://s.sina.com/1247.html'
insert into tb select '2011/11/9 23:55','1752F55AB02C4C79B50E375B3DAEB85E','/0159607.html','http://fashion.sina.com/search/?navtype=02&cateid=5142'
insert into tb select '2011/11/9 23:52','1752F55AB02C4C79B50E375B3DAEB85E','/','http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7'
insert into tb select '2011/11/9 23:55','1752F55AB02C4C79B50E375B3DAEB85E','/search/','http://fashion.sina.com/'
goselect visit_time,session_id,referrer,(case when url='/login/reg.aspx' then 1 else 0 end) as REGFLG
from tb a 
where not exists(select 1 from tb where session_id=a.session_id and visit_time<a.visit_time)
/*
visit_time              session_id                               referrer                                                                                             REGFLG
----------------------- ---------------------------------------- ---------------------------------------------------------------------------------------------------- -----------
2011-11-09 09:28:00.000 FE6FE647CD8E4D79A7B878A57C954CE9         NULL                                                                                                 0
2011-11-09 23:42:00.000 1752F55AB02C4C79B50E375B3DAEB85E         NULL                                                                                                 0
2011-11-09 23:42:00.000 1752F55AB02C4C79B50E375B3DAEB85E         http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7                                                   0
2011-11-09 09:28:00.000 FE6FE647CD8E4D79A7B878A57C954CE9         NULL                                                                                                 0
2011-11-09 09:28:00.000 FE6FE647CD8E4D79A7B878A57C954CE9         http://www.sina.com/                                                                                 0
2011-11-09 09:13:00.000 CCFDD3ED891D42F3A03055A975F4A2C1         http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76                                                   0
2011-11-09 09:13:00.000 CCFDD3ED891D42F3A03055A975F4A2C1         http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7                                                   0
2011-11-09 09:28:00.000 FE6FE647CD8E4D79A7B878A57C954CE9         http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76                                                   0
2011-11-09 23:44:00.000 9F64D379138C43E083C589FDB9732FA9         NULL                                                                                                 0(9 行受影响)*/
go
drop table tb

Answer 15

本身的要求不严谨。
以字段SESSION_ID分组做GROUP，获取最小的VISIT_TIME,和最小VISIT_TIME对应的REFERRER
获取最小的VISIT_TIME可以有多个REFERRER

Answer 16

楼主算 0 和 1 的意思是这个:
select visit_time,session_id,referrer,(case when exists(select 1 from tb where session_id=a.session_id and url='/login/reg.aspx') then 1 else 0 end) as REGFLG
from tb a 
where not exists(select 1 from tb where session_id=a.session_id and visit_time<a.visit_time)
/*
visit_time              session_id                               referrer                                                                                             REGFLG
----------------------- ---------------------------------------- ---------------------------------------------------------------------------------------------------- -----------
2011-11-09 09:28:00.000 FE6FE647CD8E4D79A7B878A57C954CE9         NULL                                                                                                 0
2011-11-09 23:42:00.000 1752F55AB02C4C79B50E375B3DAEB85E         NULL                                                                                                 0
2011-11-09 23:42:00.000 1752F55AB02C4C79B50E375B3DAEB85E         http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7                                                   0
2011-11-09 09:28:00.000 FE6FE647CD8E4D79A7B878A57C954CE9         NULL                                                                                                 0
2011-11-09 09:28:00.000 FE6FE647CD8E4D79A7B878A57C954CE9         http://www.sina.com/                                                                                 0
2011-11-09 09:13:00.000 CCFDD3ED891D42F3A03055A975F4A2C1         http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76                                                   1
2011-11-09 09:13:00.000 CCFDD3ED891D42F3A03055A975F4A2C1         http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7                                                   1
2011-11-09 09:28:00.000 FE6FE647CD8E4D79A7B878A57C954CE9         http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76                                                   0
2011-11-09 23:44:00.000 9F64D379138C43E083C589FDB9732FA9         NULL                                                                                                 0(9 行受影响)*/
go
drop table tb

Answer 17

还真是,咋弄都不符!
create table tb(VISIT_TIME datetime,SESSION_ID varchar(40),URL varchar(100),REFERRER varchar(100))
insert into tb select '2011/11/9 9:28','FE6FE647CD8E4D79A7B878A57C954CE9','/login/login.aspx',NULL
insert into tb select '2011/11/9 9:19','CCFDD3ED891D42F3A03055A975F4A2C1','/Content/418.html','http://help.sina.com/payarea.html?page=/Content/21.html'
insert into tb select '2011/11/9 9:27','CCFDD3ED891D42F3A03055A975F4A2C1','/','http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7'
insert into tb select '2011/11/9 23:42','1752F55AB02C4C79B50E375B3DAEB85E','/Login/Login.aspx',NULL
insert into tb select '2011/11/9 9:37','FE6FE647CD8E4D79A7B878A57C954CE9','/shopping/shopping.aspx','http://item.sina.com/0120958.html'
insert into tb select '2011/11/9 10:00','FE6FE647CD8E4D79A7B878A57C954CE9','/Order/','http://my.sina.com/order/'
insert into tb select '2011/11/9 9:31','FE6FE647CD8E4D79A7B878A57C954CE9','/Content/38.html','http://help.sina.com/payarea.html?page=/Content/21.html'
insert into tb select '2011/11/9 9:59','FE6FE647CD8E4D79A7B878A57C954CE9','/order/',NULL
insert into tb select '2011/11/9 23:47','1752F55AB02C4C79B50E375B3DAEB85E','/','http://s.sina.com/2776.html'
insert into tb select '2011/11/9 23:45','1752F55AB02C4C79B50E375B3DAEB85E','/0095787.html','http://item.sina.com/0067878.html?ref=Search_Browse_1_0067878'
insert into tb select '2011/11/9 23:42','1752F55AB02C4C79B50E375B3DAEB85E','/','http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7'
insert into tb select '2011/11/9 23:56','1752F55AB02C4C79B50E375B3DAEB85E','/0159577.html','http://fashion.sina.com/search/?navtype=02&cateid=5142'
insert into tb select '2011/11/9 23:47','1752F55AB02C4C79B50E375B3DAEB85E','/channel/men.html','http://www.sina.com/'
insert into tb select '2011/11/9 23:58','1752F55AB02C4C79B50E375B3DAEB85E','/0159577.html','http://fashion.sina.com/search/?navtype=05&cateid=5142'
insert into tb select '2011/11/9 9:28','FE6FE647CD8E4D79A7B878A57C954CE9','/',NULL
insert into tb select '2011/11/9 23:59','1752F55AB02C4C79B50E375B3DAEB85E','/search/','http://fashion.sina.com/search/?navtype=05&cateid=5142'
insert into tb select '2011/11/9 9:29','FE6FE647CD8E4D79A7B878A57C954CE9','/0120958.html','http://weiyi.sina.com/'
insert into tb select '2011/11/9 9:33','FE6FE647CD8E4D79A7B878A57C954CE9','/Content/21.html','http://help.sina.com/Content/21.html'
insert into tb select '2011/11/9 9:32','FE6FE647CD8E4D79A7B878A57C954CE9','/Content/21.html','http://help.sina.com/Content/38.html'
insert into tb select '2011/11/9 9:33','FE6FE647CD8E4D79A7B878A57C954CE9','/Content/21.html','http://help.sina.com/Content/21.html'
insert into tb select '2011/11/9 23:54','1752F55AB02C4C79B50E375B3DAEB85E','/','http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7'
insert into tb select '2011/11/9 9:27','CCFDD3ED891D42F3A03055A975F4A2C1','/login/login.aspx',NULL
insert into tb select '2011/11/9 10:00','FE6FE647CD8E4D79A7B878A57C954CE9','/','http://my.sina.com/Order/'
insert into tb select '2011/11/9 9:24','CCFDD3ED891D42F3A03055A975F4A2C1','/shopping/shopping.aspx','http://help.sina.com/Content/418.html'
insert into tb select '2011/11/9 9:20','CCFDD3ED891D42F3A03055A975F4A2C1','/login/reg.aspx',NULL
insert into tb select '2011/11/9 23:44','1752F55AB02C4C79B50E375B3DAEB85E','/0067878.html','http://s.sina.com/2776.html'
insert into tb select '2011/11/9 23:49','1752F55AB02C4C79B50E375B3DAEB85E','/1247.html','http://s.sina.com/1247.html'
insert into tb select '2011/11/9 9:28','FE6FE647CD8E4D79A7B878A57C954CE9','/','http://www.sina.com/'
insert into tb select '2011/11/9 9:58','FE6FE647CD8E4D79A7B878A57C954CE9','/Shopping/Shopping_Complete.aspx','https://shopping.sina.com/shoppingcar/index'
insert into tb select '2011/11/9 9:58','FE6FE647CD8E4D79A7B878A57C954CE9','/Shopping/Shopping_Complete.aspx','https://shopping.sina.com/shoppingcar/index'
insert into tb select '2011/11/9 9:14','CCFDD3ED891D42F3A03055A975F4A2C1','/0120958.html','http://weiyi.sina.com/'
insert into tb select '2011/11/9 9:13','CCFDD3ED891D42F3A03055A975F4A2C1','/','http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76'
insert into tb select '2011/11/9 23:59','1752F55AB02C4C79B50E375B3DAEB85E','/','http://fashion.sina.com/search/?navtype=05'
insert into tb select '2011/11/9 9:13','CCFDD3ED891D42F3A03055A975F4A2C1','/','http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7'
insert into tb select '2011/11/9 23:45','1752F55AB02C4C79B50E375B3DAEB85E','/0067878.html','http://s.sina.com/2776.html'
insert into tb select '2011/11/9 9:32','FE6FE647CD8E4D79A7B878A57C954CE9','/Content/21.html','http://help.sina.com/Content/38.html'
insert into tb select '2011/11/9 23:47','1752F55AB02C4C79B50E375B3DAEB85E','/1247.html','http://www.sina.com/channel/men.html'
insert into tb select '2011/11/9 9:27','CCFDD3ED891D42F3A03055A975F4A2C1','/login/reg.aspx',NULL
insert into tb select '2011/11/9 9:28','FE6FE647CD8E4D79A7B878A57C954CE9','/','http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76'
insert into tb select '2011/11/9 23:55','1752F55AB02C4C79B50E375B3DAEB85E','/','http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76'
insert into tb select '2011/11/9 23:56','1752F55AB02C4C79B50E375B3DAEB85E','/0159576.html','http://fashion.sina.com/search/?navtype=02&cateid=5142'
insert into tb select '2011/11/9 23:50','1752F55AB02C4C79B50E375B3DAEB85E','/1247.html','http://s.sina.com/1247.html?p=2'
insert into tb select '2011/11/9 23:53','1752F55AB02C4C79B50E375B3DAEB85E','/channel/men.html','http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76'
insert into tb select '2011/11/9 9:37','FE6FE647CD8E4D79A7B878A57C954CE9','/shoppingcar/index',NULL
insert into tb select '2011/11/9 23:43','1752F55AB02C4C79B50E375B3DAEB85E','/channel/men.html','http://www.sina.com/?source=bdzqbtd56a1cce0ea3fe76'
insert into tb select '2011/11/9 23:57','1752F55AB02C4C79B50E375B3DAEB85E','/search/','http://fashion.sina.com/'
insert into tb select '2011/11/9 9:41','FE6FE647CD8E4D79A7B878A57C954CE9','/category/transfer',NULL
insert into tb select '2011/11/9 9:24','CCFDD3ED891D42F3A03055A975F4A2C1','/Content/418.html',NULL
insert into tb select '2011/11/9 23:44','9F64D379138C43E083C589FDB9732FA9','/Login/Login.aspx',NULL
insert into tb select '2011/11/9 23:43','1752F55AB02C4C79B50E375B3DAEB85E','/2776.html','http://www.sina.com/channel/men.html'
insert into tb select '2011/11/9 23:48','1752F55AB02C4C79B50E375B3DAEB85E','/0137106.html','http://s.sina.com/1247.html'
insert into tb select '2011/11/9 23:55','1752F55AB02C4C79B50E375B3DAEB85E','/0159607.html','http://fashion.sina.com/search/?navtype=02&cateid=5142'
insert into tb select '2011/11/9 23:52','1752F55AB02C4C79B50E375B3DAEB85E','/','http://www.baidu.com/s?wd=%B7%B2%BF%CD%B3%CF%C6%B7'
insert into tb select '2011/11/9 23:55','1752F55AB02C4C79B50E375B3DAEB85E','/search/','http://fashion.sina.com/'
go
;with cte as(
select *,row_number()over(order by (select 1))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 REGFLG
from 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:28:00.000 FE6FE647CD8E4D79A7B878A57C954CE9         NULL                                                                                                 0
2011-11-09 09:19:00.000 CCFDD3ED891D42F3A03055A975F4A2C1         http://help.sina.com/payarea.html?page=/Content/21.html                                              1
2011-11-09 23:42:00.000 1752F55AB02C4C79B50E375B3DAEB85E         NULL                                                                                                 0
2011-11-09 23:44:00.000 9F64D379138C43E083C589FDB9732FA9         NULL                                                                                                 0(4 行受影响)*/
go
drop table tb

Answer 18

这样最接近:
;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 REGFLG
from 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                                                   1
2011-11-09 09:28:00.000 FE6FE647CD8E4D79A7B878A57C954CE9         NULL                                                                                                 0
2011-11-09 23:42:00.000 1752F55AB02C4C79B50E375B3DAEB85E         NULL                                                                                                 0
2011-11-09 23:44:00.000 9F64D379138C43E083C589FDB9732FA9         NULL                                                                                                 0(4 行受影响)*/

Answer 19

不好意思啊，大家！
我这个时间应该是带毫秒的，不存在相同的VISIT_TIME。
数据里是有URL='/login/reg.aspx' 的记录的，但不是VISIT_TIME最小的那条记录中。
谢谢大家了

Answer 20

不好意思啊，我从EXCEL里拷出来的，没注意不带毫秒

Answer 21

还有个问题想请教一下，如果我同时还需要获得相同SESSION_ID的记录条数，该怎么写啊？

Answer 22

with cte as
(
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
)
select *,[count]=(select max(no) from cte where SESSION_ID=a.SESSION_ID)
 from cte a where no=1