Как создать представление SQL для отображения некоторой таблицы данных на основе другой таблицы
привет,
у меня есть три стола "people_data" , "account_main", "account_items".
people_data :
---------------------------------------------------- pepole_ID | pepole_CreditLimit | --- 1 | 1000 | --- 2 | 2000 | --- 3 | 5000 | --- 4 | 1000 | --- ----------------------------------------------------
account_main :
---------------------------------------------------- accm_ID | accitem_PeplID| --- 1 | 1 | --- 2 | 1 | --- 3 | 2 | --- 4 | 2 | --- ----------------------------------------------------
account_items :
---------------------------------------------------- accm_ID | accitem_Credit | accitem_Debit | --- 1 | 0 | 1000 | --- 1 | 0 | 500 | --- 2 | 100 | 0 | --- 2 | 100 | 0 | --- ----------------------------------------------------
я хочу получить сумму "accitem_Credit" и суммой "accitem_Debit" и сумма "баланса"
как :
---------------------------------------------------- pepole_ID | credit | debit | --- 1 | 0 | 1500 | --- 2 | 200 | 0 | --- ----------------------------------------------------
Что я уже пробовал:
CREATE OR REPLACE VIEW `view_customers_balance` as
SELECT c.pepole_ID,
c.pepole_Name,
c.pepole_Code,
c.pepole_Statues,
c.pepole_PayMethod,
c.pepole_CreditLimit,
COALESCE(ac.Amountcredit, 0) AS credit,
COALESCE(ac.Amountdebit,0) AS debit,
COALESCE(ac.Amountcredit, 0) - COALESCE(ac.Amountdebit,0) AS balance
FROM people_data c
LEFT JOIN (SELECT acm.accm_ID,
acm.accitem_PeplID
FROM account_main acm
GROUP BY acm.accitem_PeplID) acmain
ON acmain.accitem_PeplID = c.pepole_ID
LEFT JOIN (SELECT crs.accm_ID,
SUM(crs.accitem_Credit) AS Amountcredit,
SUM(crs.accitem_Debit) AS Amountdebit
FROM account_items crs
WHERE crs.accitem_AccID IN (10, 59)
GROUP BY crs.accm_ID) ac
ON acmain.accm_ID = ac.accm_ID;"
но только один результат для каждого клиента появляется не сумма