SQL> alter table demo add (no int);
Table altered.
SQL> desc demo;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
NAME VARCHAR2(30)
ADDRESS VARCHAR2(60)
PHONE# VARCHAR2(12)
NO NUMBER(38)
SQL> update demo set no=rownum;
5 rows updated.
SQL> commit;
Commit complete.
SQL> select * from demo;
NAME ADDRESS PHONE# NO
------------------------------ ------------------------------------------------------------ ------------ ----------
10 asdf asdf 1
2 asa aaaa 2
3 aaa ffff 3
2 bbb ccc 4
9 bbb ddd 5
SQL> insert into demo select * from demo;
5 rows created.
SQL> /
10 rows created.
SQL> commit;
Commit complete.
SQL> select * from demo;
NAME ADDRESS PHONE# NO
------------------------------ ------------------------------------------------------------ ------------ ----------
10 asdf asdf 1
2 asa aaaa 2
3 aaa ffff 3
2 bbb ccc 4
9 bbb ddd 5
10 asdf asdf 1
2 asa aaaa 2
3 aaa ffff 3
2 bbb ccc 4
9 bbb ddd 5
10 asdf asdf 1
NAME ADDRESS PHONE# NO
------------------------------ ------------------------------------------------------------ ------------ ----------
2 asa aaaa 2
3 aaa ffff 3
2 bbb ccc 4
9 bbb ddd 5
10 asdf asdf 1
2 asa aaaa 2
3 aaa ffff 3
2 bbb ccc 4
9 bbb ddd 5
20 rows selected.
SQL> select count(*) from demo group by no;
COUNT(*)
----------
4
4
4
4
4
SQL> select no,count(*) from demo group by no;
NO COUNT(*)
---------- ----------
1 4
2 4
3 4
4 4
5 4
SQL> delete from demo where rownum<2;
1 row deleted.
SQL> commit
2 ;
Commit complete.
SQL> select no,count(*) from demo group by no;
NO COUNT(*)
---------- ----------
1 3
2 4
3 4
4 4
5 4
SQL> select no,count(*) from demo group by no having count(*)>3;
NO COUNT(*)
---------- ----------
2 4
3 4
4 4
5 4
SQL> select no,count(*) from demo group by no having no>2;
NO COUNT(*)
---------- ----------
3 4
4 4
5 4
SQL> select no,count(*) from demo group by no having no<2;
NO COUNT(*)
---------- ----------
1 3
SQL> select no,count(*) from demo group by no having no>1 and no<3;
NO COUNT(*)
---------- ----------
2 4
SQL> select no,count(*) from demo group by no having sum(no)>5;
NO COUNT(*)
---------- ----------
2 4
3 4
4 4
5 4
SQL> select no,count(*) from demo group by no having sum(no)>15;
NO COUNT(*)
---------- ----------
4 4
5 4
Table altered.
SQL> desc demo;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
NAME VARCHAR2(30)
ADDRESS VARCHAR2(60)
PHONE# VARCHAR2(12)
NO NUMBER(38)
SQL> update demo set no=rownum;
5 rows updated.
SQL> commit;
Commit complete.
SQL> select * from demo;
NAME ADDRESS PHONE# NO
------------------------------ ------------------------------------------------------------ ------------ ----------
10 asdf asdf 1
2 asa aaaa 2
3 aaa ffff 3
2 bbb ccc 4
9 bbb ddd 5
SQL> insert into demo select * from demo;
5 rows created.
SQL> /
10 rows created.
SQL> commit;
Commit complete.
SQL> select * from demo;
NAME ADDRESS PHONE# NO
------------------------------ ------------------------------------------------------------ ------------ ----------
10 asdf asdf 1
2 asa aaaa 2
3 aaa ffff 3
2 bbb ccc 4
9 bbb ddd 5
10 asdf asdf 1
2 asa aaaa 2
3 aaa ffff 3
2 bbb ccc 4
9 bbb ddd 5
10 asdf asdf 1
NAME ADDRESS PHONE# NO
------------------------------ ------------------------------------------------------------ ------------ ----------
2 asa aaaa 2
3 aaa ffff 3
2 bbb ccc 4
9 bbb ddd 5
10 asdf asdf 1
2 asa aaaa 2
3 aaa ffff 3
2 bbb ccc 4
9 bbb ddd 5
20 rows selected.
SQL> select count(*) from demo group by no;
COUNT(*)
----------
4
4
4
4
4
SQL> select no,count(*) from demo group by no;
NO COUNT(*)
---------- ----------
1 4
2 4
3 4
4 4
5 4
SQL> delete from demo where rownum<2;
1 row deleted.
SQL> commit
2 ;
Commit complete.
SQL> select no,count(*) from demo group by no;
NO COUNT(*)
---------- ----------
1 3
2 4
3 4
4 4
5 4
SQL> select no,count(*) from demo group by no having count(*)>3;
NO COUNT(*)
---------- ----------
2 4
3 4
4 4
5 4
SQL> select no,count(*) from demo group by no having no>2;
NO COUNT(*)
---------- ----------
3 4
4 4
5 4
SQL> select no,count(*) from demo group by no having no<2;
NO COUNT(*)
---------- ----------
1 3
SQL> select no,count(*) from demo group by no having no>1 and no<3;
NO COUNT(*)
---------- ----------
2 4
SQL> select no,count(*) from demo group by no having sum(no)>5;
NO COUNT(*)
---------- ----------
2 4
3 4
4 4
5 4
SQL> select no,count(*) from demo group by no having sum(no)>15;
NO COUNT(*)
---------- ----------
4 4
5 4
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-203932/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
查找数据库中未加索引的外键脚本
下一篇：
学习索引-1
请登录后发表评论
登录
全部评论
