表A
字段:fileid,contentcode,filetype,....
唯一索引:contentcode,filetype表中数据如下:
fileid contentcode filetype ........
10000 74000000000 1 ........
10000 74000000000 2 ........
10000 74000000000 3 ........
10000 74000000001 1 ........
10001 74000000002 2 ........
10001 74000000002 3 ........
10001 74000000002 1 ........
10002 74000000001 1 ........
10002 74000000002 1 ........
..... ..... ... ........我想要的结果是:
fileid contentcode filetype ........
10000 74000000000 1 ........
10000 74000000000 2 ........
10000 74000000000 3 ........
10001 74000000002 1 ........
10001 74000000002 2 ........
10001 74000000002 3 ........
..... ..... ... ........就是想要查询得到:fileid = fileid and contentcode = contentcode 的一个集合。请各位大侠支招!谢谢!
字段:fileid,contentcode,filetype,....
唯一索引:contentcode,filetype表中数据如下:
fileid contentcode filetype ........
10000 74000000000 1 ........
10000 74000000000 2 ........
10000 74000000000 3 ........
10000 74000000001 1 ........
10001 74000000002 2 ........
10001 74000000002 3 ........
10001 74000000002 1 ........
10002 74000000001 1 ........
10002 74000000002 1 ........
..... ..... ... ........我想要的结果是:
fileid contentcode filetype ........
10000 74000000000 1 ........
10000 74000000000 2 ........
10000 74000000000 3 ........
10001 74000000002 1 ........
10001 74000000002 2 ........
10001 74000000002 3 ........
..... ..... ... ........就是想要查询得到:fileid = fileid and contentcode = contentcode 的一个集合。请各位大侠支招!谢谢!
听起来太抽象
从这个结果里只能看出:按filedid和contentcode分组,含有filetype为1,2,3的记录
select * from (
select a.*,count(distinct filetype)over(partition by fileid,contentcode)c from a)
where c=3
(select fileid,contentcode from a group by fileid,contentcode having count(1) >= 2) m
where m.fileid = t.fileid and m.contentcode = t.contentcode)
insert into a values('10000' , '74000000000' , 1)
insert into a values('10000' , '74000000000' , 2)
insert into a values('10000' , '74000000000' , 3)
insert into a values('10000' , '74000000001' , 1)
insert into a values('10001' , '74000000002' , 2)
insert into a values('10001' , '74000000002' , 3)
insert into a values('10001' , '74000000002' , 1)
insert into a values('10002' , '74000000001' , 1)
insert into a values('10002' , '74000000002' , 1)
goselect t.* from a t where exists(select 1 from
(select fileid,contentcode from a group by fileid,contentcode having count(1) >= 2) m
where m.fileid = t.fileid and m.contentcode = t.contentcode)
order by t.fileid , t.contentcode , t.filetypedrop table a/*
fileid contentcode filetype
---------- -------------------- -----------
10000 74000000000 1
10000 74000000000 2
10000 74000000000 3
10001 74000000002 1
10001 74000000002 2
10001 74000000002 3
*/
insert into a values('10000' , '74000000000' , 1)
insert into a values('10000' , '74000000000' , 2)
insert into a values('10000' , '74000000000' , 3)
insert into a values('10000' , '74000000001' , 1)
insert into a values('10001' , '74000000002' , 2)
insert into a values('10001' , '74000000002' , 3)
insert into a values('10001' , '74000000002' , 1)
insert into a values('10002' , '74000000001' , 1)
insert into a values('10002' , '74000000002' , 1)
goselect t.* from a t where exists(select 1 from
(select fileid,contentcode from a group by fileid,contentcode having count(1) >= 2) m
where m.fileid = t.fileid and m.contentcode = t.contentcode)
order by t.fileid , t.contentcode , t.filetypedrop table a/*
fileid contentcode filetype
---------- -------------------- -----------
10000 74000000000 1
10000 74000000000 2
10000 74000000000 3
10001 74000000002 1
10001 74000000002 2
10001 74000000002 3
*/
学习了
在这里我要特别感谢wildwave和dawugui大侠!谢谢!
呵呵!