数据:
ID A B C D
k1 1 2 3 1
k2 1 2 3 2
k3 1 2 3 3
k4 1 2 3 4
-----------------------
k5 1 2 3 4
-----------------------
k6 1 2 3 4请教如何得到a、b、c相同 而d不相同的分组数据
如虚线分组的数据
ID A B C D
k1 1 2 3 1
k2 1 2 3 2
k3 1 2 3 3
k4 1 2 3 4
-----------------------
k5 1 2 3 4
-----------------------
k6 1 2 3 4请教如何得到a、b、c相同 而d不相同的分组数据
如虚线分组的数据
select id,a,b,c,d
from test t
where exists(
select 1 from test r
where t.a=r.a
and t.b=r.b
and t.c=r.c
and t.d!=r.d
)
select id,a,b,c,max(d)
from test
group by id,a,b,c
order by id
贴上建表语句
create table test
(
ID VARCHAR(10) ,
A VARCHAR(10),
B VARCHAR(10),
C VARCHAR(10),
D VARCHAR(10),
primary key (ID)
)
insert into test values('k1','1','2','3','1');
insert into test values('k2','1','2','3','2');
insert into test values('k3','1','2','3','3');
insert into test values('k4','1','2','3','4');
insert into test values('k5','1','2','3','4');
insert into test values('k6','1','2','3','4');
insert into test values('k7','2','2','3','1');
insert into test values('k8','2','2','3','2');
-----------------------------------------------------补充问题说明
想得到这样的结果
A、B、C相同,D不同的数据分成一组,以上数据分组得到以下4组数据'k1','1','2','3','1'
'k2','1','2','3','2'
'k3','1','2','3','3'
'k4','1','2','3','4'
------------------------
'k5','1','2','3','4'
-------------------------
'k6','1','2','3','4'
-------------------------
'k7','2','2','3','1'
'k8','2','2','3','2'
--你这个分组真的没神马意义......
SQL> with t as(
2 select 'k1' id,1 a,2 b,3 c,1 d from dual union all
3 select 'k2',1,2,3,2 from dual union all
4 select 'k3',1,2,3,3 from dual union all
5 select 'k4',1,2,3,4 from dual union all
6 select 'k5',1,2,3,4 from dual union all
7 select 'k6',1,2,3,4 from dual union all
8 select 'k7',2,2,3,1 from dual union all
9 select 'k8',2,2,3,2 from dual)
10 select id,a,b,c,d,
11 row_number() over (partition by a,b,c order by a,b,c) e
12 from t
13 /
ID A B C D E
-- ---------- ---------- ---------- ---------- ----------
k1 1 2 3 1 1
k2 1 2 3 2 2
k3 1 2 3 3 3
k4 1 2 3 4 4
k5 1 2 3 4 5
k6 1 2 3 4 6
k7 2 2 3 1 1
k8 2 2 3 2 2
8 rows selected
我想拿到的是分组出来的ID
比如查询出来是4条数据,将分好的数据ID放到一个字段中
第一条是k1、k2、k3、k4
第二条是k5
第三是k6
第四跳是k7、k8直接拿到分好组的数据ID值
select t.a, t.b, t.c, COUNT(1) from test t GROUP BY t.a, t.b, t.c
结果: a b c count(1)
2 2 3 2
1 2 3 6