tableC有id、tableA_id、tableB_id?
LEFT JOIN是sql92标准写法FROM tableA,tableB是非sql92写法,两种写法混合使用容易出错的。
Oracle的写法是
SELECT tableA.id,tableB.id,count(tableC.id)
FROM tableA,tableB,tableC
WHERE tableC.tableA_id=talbeA.id(+)
AND tableC.tableB_id=tableB.id
GROUP BY tableA.id,tableB.id
LEFT JOIN是sql92标准写法FROM tableA,tableB是非sql92写法,两种写法混合使用容易出错的。
Oracle的写法是
SELECT tableA.id,tableB.id,count(tableC.id)
FROM tableA,tableB,tableC
WHERE tableC.tableA_id=talbeA.id(+)
AND tableC.tableB_id=tableB.id
GROUP BY tableA.id,tableB.id
+---------+--------+
| id | name |
+---------+--------+
| 1001 | eric |
| 1002 | daniel |
| 1003 | paul |
+---------+--------+tableB
+---------+--------+
| id | role |
+---------+--------+
| 0001 | doctor |
| 0002 | teacher|
| 0003 | student|
+---------+--------+tableC
+---------+---------+---------+
| id |tableAid |tableBid |
+---------+---------+---------+
| 1 | 1001 | 0001 |
| 2 | 1001 | 0002 |
| 3 | 1002 | 0003 |
| 4 | 1002 | 0002 |
| 5 | 1003 | 0001 |
| 6 | 1003 | 0001 |
| 7 | 1003 | 0003 |
+---------+---------+---------+我想要的结果是
+---------+---------+----------------+
|tableAid |tableBid |count(tableC.id)|
+---------+---------+----------------+
| 1001 | 0001 | 1 |
| 1001 | 0002 | 1 |
| 1001 | 0003 | 0 |
| 1002 | 0001 | 0 |
| 1002 | 0002 | 1 |
| 1002 | 0003 | 1 |
| 1003 | 0001 | 2 |
| 1003 | 0002 | 0 |
| 1003 | 0003 | 1 |
+---------+---------+----------------+
SELECT talbeA.id,tableB.id,count(tableC.id)
FROM talbeA,talbeB
LEFT JOIN tableC ON tableC.tableAid=talbeA.id AND tableC.tableBid=tableB.id
GROUP BY talbeA.id,tableB.id
在oracle下就不可以