我有两个桌游& &;事务我在游戏表中使用这个公式,sum(EntryFee * Rake/(100 + Rake)*TotalEntry)得到一个值
我在Transaction table count(distinct UserID)中使用这个查询来获得一个值
现在我想将[sum(EntryFee * Rake/(100 + Rake)*TotalEntry)]的值与[count(distinct UserID)]的值相除
for eg sum(EntryFee * Rake/(100 + Rake)*TotalEntry) = 90 and count(distinct UserID) = 3然后90/3 =30如何在MYSQL
中执行此操作?CREATE TABLE Games (EntryFee INT, Rake INT, TotalEntry INT);
CREATE TABLE Transaction1 (UserID VARCHAR(25));
INSERT INTO Games VALUES
(30,16,150),(45,20,100),(15,5,50),(25,20,300),(10,8,270);
INSERT INTO Transaction1 VALUES ('Daniel'),('David'),('John'),('Martha');
SELECT Games.EntryFee, Games.Rake, Games.TotalEntry, COUNT(distinct Transaction1.UserID) AS CountUser,
(Games.EntryFee * Games.Rake / (100 + Games.Rake) * Games.TotalEntry / COUNT(distinct Transaction1.UserID))
AS Calculate
FROM Games JOIN Transaction1 GROUP BY Games.EntryFee, Games.Rake, Games.TotalEntry;
结果:
+==========+======+============+===========+==============+
| EntryFee | Rake | TotalEntry | CountUser | Calculate |
+==========+======+============+===========+==============+
| 10 | 8 | 270 | 4 | 50.00000000 |
+----------+------+------------+-----------+--------------+
| 15 | 5 | 50 | 4 | 8.92857500 |
+----------+------+------------+-----------+--------------+
| 25 | 20 | 300 | 4 | 312.50000000 |
+----------+------+------------+-----------+--------------+
| 30 | 16 | 150 | 4 | 155.17242500 |
+----------+------+------------+-----------+--------------+
| 45 | 20 | 100 | 4 | 187.50000000 |
+----------+------+------------+-----------+--------------+
查询样例SELECT (
SELECT sum(EntryFee * Rake/(100 + Rake)*TotalEntry) FROM Games
)/(
SELECT count(distinct UserID) FROM Transaction
) MyResult