我對sql有點了解，只是基本的，現在我需要創建一個分析查詢但還不能這樣做。
我的資料庫 oracle、客戶端和考試中有 2 個表：
我嘗試了很多方法來獲得客戶的考試平均值，但還沒有成功。4
預期的結果是：
考試 = 13
客戶 = 6
13/6= 2.166666666...7
我怎樣才能做到這一點？
uj5u.com熱心網友回復：
如果您的客戶沒有參加任何考試，那么您希望：
SELECT AVG(COUNT(e.nu_ordem)) AS avg_exames_by_client
FROM cliente c
LEFT OUTER JOIN exames e
ON (c.id = e.id_cliente)
GROUP BY c.id;
或者：
SELECT (SELECT COUNT(*) FROM exames) / (SELECT COUNT(*) FROM cliente)
AS avg_exames_by_client
FROM DUAL;
其中，對于樣本資料：
CREATE TABLE cliente (id PRIMARY KEY) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL UNION ALL
SELECT 4 FROM DUAL UNION ALL
SELECT 5 FROM DUAL UNION ALL
SELECT 6 FROM DUAL;
CREATE TABLE exames (nu_ordem PRIMARY KEY, id_cliente) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 5 FROM DUAL UNION ALL
SELECT 3, 5 FROM DUAL UNION ALL
SELECT 4, 2 FROM DUAL UNION ALL
SELECT 5, 6 FROM DUAL UNION ALL
SELECT 6, 1 FROM DUAL UNION ALL
SELECT 7, 1 FROM DUAL UNION ALL
SELECT 8, 4 FROM DUAL UNION ALL
SELECT 9, 5 FROM DUAL UNION ALL
SELECT 10, 3 FROM DUAL UNION ALL
SELECT 11, 6 FROM DUAL UNION ALL
SELECT 12, 2 FROM DUAL UNION ALL
SELECT 13, 1 FROM DUAL;
兩個輸出：
AVG_EXAMES_BY_CLIENT 2.166666666666666667
如果您隨后添加了幾個客戶但沒有更多的考試：
INSERT INTO cliente (id)
SELECT 7 FROM DUAL UNION ALL
SELECT 8 FROM DUAL
那么平均值為：
AVG_EXAMES_BY_CLIENT 1.625
db<>在這里擺弄
uj5u.com熱心網友回復：
您可以嘗試以下公式來獲得結果 -
SELECT COUNT(*)/COUNT(DISTINCT id_cliente)
FROM exams;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/407685.html
標籤：