sql查询,牛人快进

[sdwbcvbrtcwwww]

有表table1

uid   time    url
 1     1      th
 1     2      th
 3     2      io
 2     3      yu
 4     1      th
 1     8      io
 2     9      yu
 1     7      yu

用户 1 从 th 出来的时间是1和2，然后又在时间 7 时去了yu ,在时间 8 时去了io，要查出他从1出来后最早去了那儿？结果就是yu。统计从th出来去其他url的人数并排名！！
最终结果：

url '人数'
 yu   2     --这里面的两个人是uid:1和2
 io   1     --uid：3

不明白？我再解释~~~

[jackyxfl]

不明白，没有看明白什么意思

[昵称被占用了]

io 1 --uid：3

3并没有访问th,为什么统计上了

[jwdream2008]

url '人数'
 yu   2     --这里面的两个人是uid:1和2
 io   1     --uid：3

uid 为2的用户没有去过th啊？？

[永生天地]

谁知道怎么算出来，怎么算进去

[昵称被占用了]

select url,count(distinct uid) as '人数'
from table1 a
where exists (
select 1 from table1
where url='th'
and time < a.time
and uid=a.uid
)
group by url

[昵称被占用了]

结果应该是
url '人数'
yu 1 --uid:1
io 1 --uid：1

[guguda2008]

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(uid INT,   [time] INT,    url VARCHAR(20))
INSERT INTO TB
SELECT 1,     1,      'th' UNION ALL
 SELECT 1,     2,      'th' UNION ALL
 SELECT 3,     2,      'io' UNION ALL
 SELECT 2,     3,      'yu' UNION ALL
 SELECT 4,     1,      'th' UNION ALL
 SELECT 1,     8,      'io' UNION ALL
 SELECT 2,     9,      'yu' UNION ALL
SELECT  1,     7,      'yu'

;WITH MU AS (
SELECT UID,MAX([TIME]) 'MAXTIME' FROM TB WHERE URL='th' GROUP BY UID
) 
,MU2 AS (
SELECT TB.* FROM TB
INNER JOIN MU ON TB.UID=MU.UID AND TB.[TIME]>MU.MAXTIME
UNION ALL
SELECT * FROM TB WHERE TB.UID NOT IN (SELECT UID FROM MU)
)
SELECT URL,COUNT(1)
FROM MU2 T1 WHERE NOT EXISTS(
SELECT * FROM MU2 T2 WHERE T2.UID=T1.UID AND T2.[TIME]<T1.[TIME]
)
GROUP BY URL
/*
io	1
yu	2
*/

[guguda2008]

guess or no score

[lon123]

declare @table Table ([UserID] int, [Time] int, [Place] nvarchar(50))
Declare @tablePeopleBeenTh table([UserID] int)

insert @table
select 1,1,'th'

insert @table
select 1,2,'th'

insert @table
select 2,2,'th'

insert @table
select 3,2,'io'

insert @table
select 2,3,'yu'

insert @table
select 4,1,'th'

insert @table
select 1,8,'io'

insert @table
select 2,9,'yu'

insert @table
select 1,7,'yu'


select * from @table

insert @tablePeopleBeenTh
select UserID from @table where Place = 'th'

select Place,Count(UserID) from @table
where Place <> 'th' and (UserID in (select * from @tablePeopleBeenTh))
Group by Place
order by Count(UserID) desc

[php_boy]

create table table_1(
uid int, time int, url nvarchar(max)
)

insert table_1(uid, time, url)
select 1,1,'th' union all
select 1,2,'th' union all
select 3,2,'io' union all
select 2,3,'yu' union all
select 4,1,'th' union all
select 1,8,'io' union all
select 2,9, 'yu' union all
select 1,7,'yu'



select * from table_1

;with t as
(
	select distinct(uid) from table_1
	where url='th'
)
--select * from t

select table_1.url,count(*) as '人数'
from table_1, t
where table_1.uid=t.uid and  table_1.url <> 'th'
group by table_1.url

[sdwbcvbrtcwwww]

uid         time        web
----------- ----------- ----------
1           1           th        
1           2           th        
1           3           rg        
2           5           th        
3           2           rg        
3           6           io        
3           5           th        
4           7           th        
4           9           rg        
1           2           io 

不好意思~~之前的例子不好！！！试试这个~~~同一个道理
要的结果：

io 2
rg 1

[lon123]

[Quote=引用 9 楼 lon123 的回复:]
declare @table Table ([UserID] int, [Time] int, [Place] nvarchar(50))
Declare @tablePeopleBeenTh table([UserID] int)

insert @table
select 1,1,'th'

insert @table
select 1,2,'th'

insert @tab……
[/Quote]

我的就可行了