请教一个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的数据库很烂啊，不到逼不得已不用它
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(xhb_ceshi.id5) <3) b
where a.id1=b.id1
and a.id2=b.id2
and a.id3=b.id3
and a.userid=b.userid
;
在having count()的时候，加上字段的属主就可以了。
