表结构如下:
CREATE OR REPLACE TABLE SKSJB
(
XH CHAR(15) PRIMARY KEY;
XM VARCHAR2(30);
ZC NUMBER;
XQ NUMBER;
JC1 NUMBER;
JC2 NUMBER;
ZT NUMBER;
)
数据如下:
INSERT INTO SKSJB values('012100349', 'Tom', 10, 3, 3, 4, 0);
INSERT INTO SKSJB values('012100349', 'Tom', 10, 4, 3, 4, 0);
INSERT INTO SKSJB values('012100349', 'Tom', 11, 5, 7, 8, 0);
INSERT INTO SKSJB values('012100349', 'Tom', 12, 6, 5, 6, 0);
INSERT INTO SKSJB values('012100349', 'Tom', 17, 6, 5, 6, 1);
INSERT INTO SKSJB values('012100350', 'Kim', 10, 3, 3, 4, 0);
INSERT INTO SKSJB values('012100350', 'Kim', 10, 4, 3, 4, 0);
INSERT INTO SKSJB values('012100350', 'Kim', 11, 5, 7, 8, 0);
INSERT INTO SKSJB values('012100350', 'Kim', 12, 6, 5, 6, 0);
INSERT INTO SKSJB values('012100350', 'Kim', 19, 6, 5, 6, 1);
INSERT INTO SKSJB values('012100351', 'James', 10, 3, 3, 4, 0);
INSERT INTO SKSJB values('012100351', 'James', 10, 4, 3, 4, 0);
INSERT INTO SKSJB values('012100351', 'James', 11, 5, 7, 8, 0);
INSERT INTO SKSJB values('012100351', 'James', 12, 6, 5, 6, 0);
INSERT INTO SKSJB values('012100352', 'Ray', 10, 3, 3, 4, 0);
INSERT INTO SKSJB values('012100352', 'Ray', 10, 4, 3, 4, 0);
INSERT INTO SKSJB values('012100352', 'Ray', 11, 5, 7, 8, 0);
INSERT INTO SKSJB values('012100352', 'Ray', 12, 6, 5, 6, 0);
我想做的事情是选出ZC, XQ, JC1, JC2, ZT均不重复的数据，并且统计出个数
结果应该如下
ZC XQ JC1 JC2 ZT COUNT(*)
10 3 3 4 0 4
10 4 3 4 0 4
11 5 7 8 0 4
12 6 5 6 0 4
17 6 5 6 1 1
19 6 5 6 1 1
------解决方案--------------------------------------------------------
LZ不都自己写出来啦
select xx,xx,xx,xx,count(*) from table group by xx,xx,xx,xx
xx为不重复的列
------解决方案--------------------------------------------------------
select ZC,XQ,JC1,JC2,ZT,count(*) from SKSJB
group by ZC,XQ,JC1,JC2,ZT
order by ZC,XQ,JC1,JC2,ZT
------解决方案--------------------------------------------------------
select ZC, XQ, JC1, JC2, ZT,max(id) from (
select ZC, XQ, JC1, JC2, ZT,row_number() over(partition by ZC, XQ, JC1, JC2, ZT order by ZT) id from SKSJB