注意下面的红色SQLPLUS语句,此语句仅在sqlplus中实现:drop table mytab purge; create table mytab(deptid varchar2(3), groupid varchar2(1), name varchar2(10));insert into mytab values('001','A','张三'); insert into mytab values('001','A','李四'); insert into mytab values('001','B','张x'); insert into mytab values('002','A','王五');BREAK ON deptidSELECT deptid, groupid, count(*) FROM mytab GROUP BY deptid, groupid;
create table t(x varchar2(10),y varchar2(10),z varchar2(10)); insert into t values('001','A','张三1'); insert into t values('001','A','张三2'); insert into t values('001','B','张三3'); insert into t values('002','A','张三4');SQL> SELECT decode(lag(x) over(ORDER BY x),x,NULL,x) x,y,COUNT(1) FROM t 2 GROUP BY x,y 3 ;
X Y COUNT(1) ---------- ---------- ---------- 001 A 2 B 1 002 A 1
create table mytab(deptid varchar2(3), groupid varchar2(1), name varchar2(10));insert into mytab values('001','A','张三');
insert into mytab values('001','A','李四');
insert into mytab values('001','B','张x');
insert into mytab values('002','A','王五');BREAK ON deptidSELECT deptid, groupid, count(*)
FROM mytab
GROUP BY deptid, groupid;
create table t(x varchar2(10),y varchar2(10),z varchar2(10));
insert into t values('001','A','张三1');
insert into t values('001','A','张三2');
insert into t values('001','B','张三3');
insert into t values('002','A','张三4');SQL> SELECT decode(lag(x) over(ORDER BY x),x,NULL,x) x,y,COUNT(1) FROM t
2 GROUP BY x,y
3 ;
X Y COUNT(1)
---------- ---------- ----------
001 A 2
B 1
002 A 1