有表table1uid 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不明白?我再解释~~~
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不明白?我再解释~~~
yu 2 --这里面的两个人是uid:1和2
io 1 --uid:3
uid 为2的用户没有去过th啊??
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
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
*/
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 @tableinsert @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
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 tselect 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
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