在MySQL中使用2个表进行计算-PHP中文网问答

文章专题学习下载问答编程词典手游最近更新

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

在MySQL中使用2个表进行计算

P粉821231319 2024-03-30 11:32:48

337

我有 2 张桌子游戏和交易我在 Games 表中使用此公式 sum(EntryFee * Rake/(100 Rake)*TotalEntry) 来获取值

我在事务表计数（不同的用户ID）中使用此查询来获取值

现在我想将 [sum(EntryFee * Rake/(100 Rake)*TotalEntry)] 的值除以 [count(distinct UserID)] 的值

例如 sum(EntryFee * Rake/(100 Rake)*TotalEntry) = 90 且 count(distinct UserID) = 3 那么 90/3 =30 我怎样才能在 MYSQL 中做到这一点

P粉821231319

全部回复 (2)

P粉2956161702024-03-31 09:37:42 2 楼

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 | +----------+------+------------+-----------+--------------+

示例查询