<script>function StorePage(){d=document;t=d.selection?(d.selection.type!='None'?d.selection.createRange().text:''):(d.getSelection?d.getSelection():'');void(keyit=window.open('http://www.365key.com/storeit.aspx?t='+escape(d.title)+'&u='+escape(d.location.href)+'&c='+escape(t),'keyit','scrollbars=no,width=475,height=575,left=75,top=20,status=no,resizable=yes'));keyit.focus();}</script>
CREATE
TABLE
t1
(
c1 NUMBER ,
c2 VARCHAR2 ( 10 )
);
INSERT INTO t1 (C1, C2) VALUES ( 1 , ' a ' );
INSERT INTO t1 (C1, C2) VALUES ( 1 , ' b ' );
INSERT INTO t1 (C1, C2) VALUES ( 1 , ' c ' );
INSERT INTO t1 (C1, C2) VALUES ( 1 , ' d ' );
INSERT INTO t1 (C1, C2) VALUES ( 2 , ' a ' );
INSERT INTO t1 (C1, C2) VALUES ( 2 , ' b ' );
INSERT INTO t1 (C1, C2) VALUES ( 2 , ' c ' );
INSERT INTO t1 (C1, C2) VALUES ( 2 , ' e ' );
INSERT INTO t1 (C1, C2) VALUES ( 3 , ' a ' );
INSERT INTO t1 (C1, C2) VALUES ( 3 , ' b ' );
INSERT INTO t1 (C1, C2) VALUES ( 3 , ' e ' );
INSERT INTO t1 (C1, C2) VALUES ( 4 , ' a ' );
INSERT INTO t1 (C1, C2) VALUES ( 4 , ' e ' );
COMMIT ;
(
c1 NUMBER ,
c2 VARCHAR2 ( 10 )
);
INSERT INTO t1 (C1, C2) VALUES ( 1 , ' a ' );
INSERT INTO t1 (C1, C2) VALUES ( 1 , ' b ' );
INSERT INTO t1 (C1, C2) VALUES ( 1 , ' c ' );
INSERT INTO t1 (C1, C2) VALUES ( 1 , ' d ' );
INSERT INTO t1 (C1, C2) VALUES ( 2 , ' a ' );
INSERT INTO t1 (C1, C2) VALUES ( 2 , ' b ' );
INSERT INTO t1 (C1, C2) VALUES ( 2 , ' c ' );
INSERT INTO t1 (C1, C2) VALUES ( 2 , ' e ' );
INSERT INTO t1 (C1, C2) VALUES ( 3 , ' a ' );
INSERT INTO t1 (C1, C2) VALUES ( 3 , ' b ' );
INSERT INTO t1 (C1, C2) VALUES ( 3 , ' e ' );
INSERT INTO t1 (C1, C2) VALUES ( 4 , ' a ' );
INSERT INTO t1 (C1, C2) VALUES ( 4 , ' e ' );
COMMIT ;
C1代表用户id,C2是该用户所访问的文章标题,C1=1的用户访问了a b c d四篇文章,现在要查出访问文章和用户1重叠数超过3(包含3)的用户id,C1=2的用户访问了a b c e四篇文章,两个都访问了a b c,重叠数是3,则相对于1号用户来说2号用户是符合要求的,其实用一句话说,就是:对每个用户查找与之有相同偏好的用户,相同偏好的定义为访问文章重叠数>=a。
SELECT
c1
FROM t1
WHERE c2 IN ( SELECT c2
FROM t1
WHERE c1 = 1 ) AND c1 != 1
GROUP BY c1
HAVING COUNT ( 1 ) > 3 ;
FROM t1
WHERE c2 IN ( SELECT c2
FROM t1
WHERE c1 = 1 ) AND c1 != 1
GROUP BY c1
HAVING COUNT ( 1 ) > 3 ;
发表于 @ 2007年07月31日 09:48:00|评论(0 <script type=text/javascript>AddFeedbackCountStack("1718251")</script> )|编辑