在我的 Oracle 資料庫中，我有兩個表，分別T1帶有主鍵k1和T2復合主鍵k1, k2。我想選擇所有列T1以及T2諸如T1.k1 = T2.k1.
看起來很簡單，但我不知道如何使用該COUNT函式來獲得這個結果，知道嗎？
uj5u.com熱心網友回復：
我沒有你的桌子，所以我會嘗試使用 Scott 的示例emp和 dept來說明它tables：
SQL> select * from dept t1 order by t1.deptno;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select deptno, empno, ename from emp order by deptno;
DEPTNO EMPNO ENAME
---------- ---------- ----------
10 7782 CLARK --> 3 employees in deptno 10
10 7839 KING
10 7934 MILLER
20 7566 JONES --> 5 employees in deptno 20
20 7902 FORD
20 7876 ADAMS
20 7369 SMITH
20 7788 SCOTT
30 7521 WARD --> 6 employees in deptno 30
30 7844 TURNER
30 7499 ALLEN
30 7900 JAMES
30 7698 BLAKE
30 7654 MARTIN
--> 0 employees in deptno 40
14 rows selected.
SQL>
您可以嘗試幾個選項：
相關子查詢：
SQL> select t1.*,
2 (select count(*) from emp t2 where t2.deptno = t1.deptno) cnt
3 from dept t1
4 order by t1.deptno;
DEPTNO DNAME LOC CNT
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK 3
20 RESEARCH DALLAS 5
30 SALES CHICAGO 6
40 OPERATIONS BOSTON 0
SQL>
（外）加入COUNT函式和GROUP BY子句：
SQL> select t1.*, count(t2.rowid) cnt
2 from dept t1 left join emp t2 on t2.deptno = t1.deptno
3 group by t1.deptno, t1.dname, t1.loc
4 order by t1.deptno;
DEPTNO DNAME LOC CNT
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK 3
20 RESEARCH DALLAS 5
30 SALES CHICAGO 6
40 OPERATIONS BOSTON 0
SQL>
COUNT（外）以決議形式加入函式：
SQL> select distinct t1.*,
2 count(t2.rowid) over (partition by t1.deptno) cnt
3 from dept t1 left join emp t2 on t2.deptno = t1.deptno
4 order by t1.deptno;
DEPTNO DNAME LOC CNT
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK 3
20 RESEARCH DALLAS 5
30 SALES CHICAGO 6
40 OPERATIONS BOSTON 0
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/478817.html