求oracle达人[200分求助]
id A B C--列
ab 1 8 8
ab 2 7 9
ab 1 7 8
ab 1 8 8
ab 2 7 9
ab 2 7 8
ab 1 7 8
找出C=8的 记录中 A相同的 B=7的记录数[count]不大于3的 所有信息
结果
ab 1 7 8
ab 2 7 8
ab 1 7 8
求oracle的存储过程或sql语句或思路.
[解决办法]
没看懂，
你就三个字段，其中B=7 C=8,而且A又相同，那不就是重复项么？
话说你的结果又不像查找重复项，ab 2 7 8 只出现一次，就上榜了。
你这个结果可以说直接 where B=7 and C=8 就行了
[解决办法]
linq就简单了
var list = new List<ABC> { new ABC(1, 8, 8), new ABC(2, 7, 9), new ABC(1, 7, 8), new ABC(1, 8, 8), new ABC(2, 7, 9), new ABC(2, 7, 8), new ABC(1, 7, 8) };
var q = list.Where(a => a.B == 7)
.GroupBy(a => a.A)
.Where(g => g.Count() <= 3);
[解决办法]
select *
from table1
where C=8 and B=7 and A in (select A from (select count(*) as num,A from Table1 where B=7 group by A) z where z.num <= 3)
Create table mytable
(
id VARCHAR2(10) not null,
A int,
B int,
C int
);
insert into mytable
select 'ab',3,7,8 from dual
union all
select 'ab',2,7,9 from dual
union all
select 'ab',1,7,8 from dual
union all
select 'ab',1,8,8 from dual
union all
select 'ab',2,7,9 from dual
union all
select 'ab',2,7,8 from dual
union all
select 'ab',1,7,8 from dual
select t.* from
mytable t where t.C=8 and t.A in (select A from mytable where A=t.A and B=7)
and t.B=7
