请教一个SQL语句
假设有A表如下:
id1 id2 id3 userid id5
2 100 4 15 12
2 100 4 15 13
2 100 4 15 14
2 101 4 15 12
2 102 4 15 13
2 101 4 15 14
--------
找出字段id1 ,id2, id3, userid四个字段值同时相等且 记录数小于3的 的 行,查询结果应该这样:
2 101 4 15 12
2 102 4 15 13
2 101 4 15 14
------解决方案--------------------create table xhb_ceshi (
id1 varchar(40) ,
id2 varchar(40) ,
id3 varchar(40) ,
userid varchar(40) ,
id5 varchar(40)
)
insert into xhb_ceshi
select '2 ', '100 ', '4 ', '15 ', '12 ' from dual
union
select '2 ', '100 ', '4 ', '15 ', '13 ' from dual
union
select '2 ', '100 ', '4 ', '15 ', '14 ' from dual
union
select '2 ', '101 ', '4 ', '15 ', '12 ' from dual
union
select '2 ', '102 ', '4 ', '15 ', '13 ' from dual
union
select '2 ', '101 ', '4 ', '15 ', '14 ' from dual
;
commit;
select a.* from xhb_ceshi a
, (select id1 ,id2, id3, userid, count( id5) as id5 from xhb_ceshi
group by id1 , id2 , id3 , userid
having count( id5) <3) b
where a.id1=b.id1
and a.id2=b.id2
and a.id3=b.id3
and a.userid=b.userid
;
------解决方案--------------------这样实现:
select a.id1,a.id2,a.id3,a.userid,a.id5
from A,
(
select count(id1||id2||id3||userid) as count,id1,id2,id3,userid
from A
group by id1,id2,id3
where count(id1||id2||id3||userid) < 3
) B
where a.id1 = b.id1
and a.id2 = b.id2
and a.id3 = b.id3
and a.userid = b.userid
------解决方案--------------------sysbase的数据库很烂啊,不到逼不得已不用它