请教一个SQL语句

[lovelihp]

假设有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

[lovelihp]

这个试过的，一样的错误。
郁闷哦 。。
再顶顶。

[wxh163000]

可能是输入法问题？

[jdsnhan]

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()的时候，加上字段的属主就可以了。

[lovelihp]

也不是

[HelloWorld_001]

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
;

[HelloWorld_001]

应该是符合你的要求，也用你的数据测试了下

[letqqqgo]

这样实现：
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

[lovelihp]

2个人一摸一样嘛

[lovelihp]

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
这个语句在ORACLE下执行没问题，奇怪的是在SYBASE下报错了，语法错误在'('

[DragonBill]

count( id5)
改成
count(id5)

试试

[gaoninggao]

select B.* from xhb_ceshi B inner join (select distinct id1,id2,id3,userid from xhb_ceshi group by id1,id2,id3,userid having count(*)<3) C
on b.id1=c.id1 and b.id2 =c.id2 and b.id3=c.id3 and c.userid =b.userid