如何改进这个mysql视图?
我认为没有必要执行两个子查询,它们使用相同的 WHERE CLAUSE
= d.donation_method = m.donation_method
但不同的 SELECT CLAUSEL
访问同一个表 jc_donation_method
. 但我不知道要避免这种情况 .
donation_id
是 jc_donation
中的主键和 jc_donation_method
中的外键 .
CREATE
OR REPLACE
ALGORITHM = MERGE
VIEW jc_donation_total AS
SELECT
d.donation_method,
(SELECT
m.method_name
FROM
`jc_donation_method` m
WHERE
d.donation_method = m.donation_method
LIMIT 1) method_name,
CAST(SUM(d.donation_amount-
(SELECT
m.method_fee_nonrecurring
FROM
`jc_donation_method` m
WHERE
d.donation_method = m.donation_method
LIMIT 1)
- d.donation_amount*(
(SELECT
m.method_fee_percent
FROM
`jc_donation_method` m
WHERE
d.donation_method = m.donation_method
LIMIT 1))
) as decimal(12,4)) donation_total
FROM
`jc_donation` d
LEFT JOIN
`jc_user` u
ON
d.user_id = u.user_id
GROUP BY
d.donation_method
HAVING
COUNT(u.user_id) > 0
基本上我想知道活动用户每次捐赠所做的所有整体捐赠方法:金额 - 非经常性费用 - 百分比费用%(每次捐赠) .
先决条件:
CREATE TABLE `jc_user` (
`user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User credentials';
CREATE TABLE `jc_donation_method` (
`donation_method` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`method_name` varchar(32) NOT NULL,
`method_fee_percent` decimal(6,4) NOT NULL DEFAULT 0.00,
`method_fee_nonrecurring` decimal(5,2) NOT NULL DEFAULT 0.00,
PRIMARY KEY (`donation_method`) USING BTREE,
UNIQUE KEY `method_name` (`method_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Donation methods and fees';
INSERT INTO
`jc_donation_method` (`donation_method`, `method_name`,
`method_fee_percent`, `method_fee_nonrecurring`)
VALUES
(NULL, 'Transfer',0.000,0.00),
(NULL, 'PayPal',0.0190,0.35);
CREATE TABLE `jc_donation` (
`donation_id` int(10) NOT NULL AUTO_INCREMENT,
`user_id` int(10) UNSIGNED NOT NULL,
`donation_method` int(10) UNSIGNED NOT NULL DEFAULT '1',
`donation_amount` decimal(12,4) NOT NULL,
PRIMARY KEY (`donation_id`) USING BTREE,
FOREIGN KEY (user_id) REFERENCES jc_user(user_id),
FOREIGN KEY (donation_method) REFERENCES jc_donation_method(donation_method)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Donations';
1 回答
正如我在最初的评论中所说,我不确定为什么用户是相关的,但这应该是最简单的查询来获得你想要的:
下面的版本应该像原始版本一样考虑用户 .