CREATEVIEWv_t_invest_backacount_sumASSELECTb.investorASinvestor,b.borrowIdASborrowId,SUM(b.realAmount)ASrealAmount,SUM(TRUNCATE(((b.realAmount/d.borrowAmount)*c.forTotalS...
CREATE VIEW v_t_invest_backacount_sum AS
SELECT
b.investor AS investor,
b.borrowId AS borrowId,
SUM(b.realAmount) AS realAmount,
SUM(TRUNCATE(((b.realAmount / d.borrowAmount) * c.forTotalSum),4)) AS forTotalSum,
SUM(TRUNCATE(((b.realAmount / d.borrowAmount) * c.forHasSum),4)) AS forHasSum
FROM ((t_invest b
LEFT JOIN v_t_invest_backacount c
ON ((b.borrowId = c.borrowId)))
LEFT JOIN v_t_invest_borrow_list d --现有这张视图
ON ((b.borrowId = d.id)))
WHERE (c.borrowId IS NOT NULL)
GROUP BY b.investor,b.borrowId UNION ALL SELECT
b.investor AS investor,
b.borrowId AS borrowId,
SUM(b.realAmount) AS realAmount,
SUM(TRUNCATE(((b.realAmount / d.borrowAmount) * c.forTotalSum),4)) AS forTotalSum,
SUM(TRUNCATE(((b.realAmount / d.borrowAmount) * c.forHasSum),4)) AS forHasSum
FROM ((t_invest_history b
LEFT JOIN v_t_invest_backacount c
ON ((b.borrowId = c.borrowId)))
LEFT JOIN v_t_invest_borrow_list d
ON ((b.borrowId = d.id)))
WHERE (c.borrowId IS NOT NULL)
GROUP BY b.investor,b.borrowId
展开