Mysql 将值代入子查询

发布于01月18日

我有一个问题:

SELECT DISTINCT P.game, 
(
SELECT AVG(dd.DUR) as median_val
FROM (
SELECT  S.val AS DUR, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
  FROM (SELECT TIMESTAMPDIFF(minute, M.date_start, M.date_finished) as val FROM matches M INNER JOIN tournaments T ON M.tournament_id=T.id
  WHERE M.tournament_id NOT IN (5,6) AND T.game = 'EXAMPLE'
  ORDER BY val) S, (SELECT @rownum:=0) r
) as dd
WHERE dd.row_number IN (FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2))
) AS duration
FROM matches M INNER JOIN tournaments P ON M.tournament_id=P.id WHERE P.id NOT IN (5,6) GROUP BY P.game

它可以工作，但所有第二列的值都是相同的，因为T.game = 'EXAMPLE' 如何将第一个SELECT中的P.Game值替换为Example常量？

桌上锦标赛

CREATE TABLE tournaments (id INT PRIMARY KEY, game VARCHAR(64));
INSERT INTO tournaments (`id`, `game`) VALUES ('1', 'EXAMPLE'), ('2', 'TETRIS'), ('3', 'MARIO'), ('4', 'EXAMPLE'), ('5', 'TETRIS'), ('6', 'MARIO');
id | game
-------------
 1    EXAMPLE
 2    TETRIS
 3    MARIO
 4    EXAMPLE
 5    TETRIS
 6    MARIO

表匹配项

CREATE TABLE matches (id INT PRIMARY KEY, tournament_id INT, date_start DATETIME, date_finished DATETIME);
INSERT INTO matches (`id`, `tournament_id`, `date_start`, `date_finished`) VALUES ('1', '1', '2024-01-18 01:10:00', '2024-01-18 01:12:00'), ('2', '2', '2024-01-18 01:10:00', '2024-01-18 01:18:00'), ('3', '3', '2024-01-18 01:10:00', '2024-01-18 01:16:00'), ('4', '3', '2024-01-18 01:10:00', '2024-01-18 01:22:00'), ('5', '2', '2024-01-18 01:10:00', '2024-01-18 01:31:00'), ('6', '1', '2024-01-18 01:10:00', '2024-01-18 01:19:00'), ('7', '2', '2024-01-18 01:10:00', '2024-01-18 01:45:00'), ('8', '4', '2024-01-18 01:10:00', '2024-01-18 01:28:00'), ('9', '4', '2024-01-18 01:10:00', '2024-01-18 01:54:00'), ('10', '5', '2024-01-18 01:10:00', '2024-01-18 01:12:00'), ('11', '6', '2024-01-18 01:10:00', '2024-01-18 01:18:00'), ('12', '7', '2024-01-18 01:10:00', '2024-01-18 01:16:00'), ('13', '7', '2024-01-18 01:10:00', '2024-01-18 01:22:00'), ('14', '6', '2024-01-18 01:10:00', '2024-01-18 01:31:00'), ('15', '5', '2024-01-18 01:10:00', '2024-01-18 01:19:00'), ('16', '6', '2024-01-18 01:10:00', '2024-01-18 01:45:00'), ('17', '6', '2024-01-18 01:10:00', '2024-01-18 01:28:00'), ('18', '5', '2024-01-18 01:10:00', '2024-01-18 01:54:00');

id | tournament_id |     date_start     |     date_finished     |
-----------------------------------------------------------------
1          1         2024-01-18 01:10:00   2024-01-18 01:12:00
2          2         2024-01-18 01:10:00   2024-01-18 01:18:00
3          3         2024-01-18 01:10:00   2024-01-18 01:16:00
4          3         2024-01-18 01:10:00   2024-01-18 01:22:00
5          2         2024-01-18 01:10:00   2024-01-18 01:31:00
6          1         2024-01-18 01:10:00   2024-01-18 01:19:00
7          2         2024-01-18 01:10:00   2024-01-18 01:45:00
8          4         2024-01-18 01:10:00   2024-01-18 01:28:00
9          4         2024-01-18 01:10:00   2024-01-18 01:54:00
10         5         2024-01-18 01:10:00   2024-01-18 01:12:00
11         6         2024-01-18 01:10:00   2024-01-18 01:18:00
12         7         2024-01-18 01:10:00   2024-01-18 01:16:00
13         7         2024-01-18 01:10:00   2024-01-18 01:22:00
14         6         2024-01-18 01:10:00   2024-01-18 01:31:00
15         5         2024-01-18 01:10:00   2024-01-18 01:19:00
16         6         2024-01-18 01:10:00   2024-01-18 01:45:00
17         6         2024-01-18 01:10:00   2024-01-18 01:28:00
18         5         2024-01-18 01:10:00   2024-01-18 01:54:00

我期待一个表，其中第二列的值与第一列的值不同.持续时间是时间差的中值- TIMESTAMPDIFF M.date

game     | duration
-------------------
EXAMPLE      13.5
TETRIS       21
MARIO        9

SELECT t.`tournament_id` , ta.game , avg(`val`) as `median` FROM ( SELECT `tournament_id`, `val`, (SELECT count(*) FROM `matches` `t2` WHERE `t2`.`tournament_id` = `t3`.`tournament_id`) as `ct`, `seq`, (SELECT count(*) FROM `matches` `t2` WHERE `t2`.`tournament_id` < `t3`.`tournament_id`) as `delta` FROM (SELECT `tournament_id`, `val`, @rownum := @rownum + 1 as `seq` FROM (SELECT `tournament_id`, (TIMESTAMPDIFF(minute, date_start, date_finished)) `val` FROM `matches` ORDER BY `tournament_id`, `val`) as `t2` CROSS JOIN (SELECT @rownum := 0) as `x` ORDER BY `tournament_id`, `seq`) as `t3` HAVING (`ct`%2 = 0 and `seq`-`delta` between floor((`ct`+1)/2) and floor((`ct`+1)/2) +1) or (`ct`%2 <> 0 and `seq`-`delta` = (`ct`+1)/2) ) as `t` JOIN tournaments ta ON ta.id = t.tournament_id GROUP BY `tournament_id`,ta.game ORDER BY `tournament_id`;

tournament_id	game	median
1	EXAMPLE	5.5000
2	TETRIS	21.0000
3	MARIO	15.0000

tournament_id

game

median

EXAMPLE

5.5000

TETRIS

21.0000

MARIO

15.0000

SELECT t.game , avg(`val`) as `median` FROM ( SELECT `game`, `val`, (SELECT count(*) FROM `matches` `t2` JOIN tournaments ta ON ta.id = t2.tournament_id WHERE `ta`.`game` = `t3`.`game` AND ta.id NOT IN (5, 6)) as `ct`, `seq`, (SELECT count(*) FROM `matches` `t2` JOIN tournaments ta ON ta.id = t2.tournament_id WHERE `ta`.`game` < `t3`.`game` AND ta.id NOT IN (5, 6)) as `delta` FROM (SELECT `game`, `val`, @rownum := @rownum + 1 as `seq` FROM (SELECT `game`, (TIMESTAMPDIFF(minute, date_start, date_finished)) `val` FROM `matches` m JOIN tournaments ta ON ta.id = m.tournament_id WHERE ta.id NOT IN (5, 6) ORDER BY `game`, `val`) as `t2` CROSS JOIN (SELECT @rownum := 0) as `x` ORDER BY `game`, `seq`) as `t3` HAVING (`ct`%2 = 0 and `seq`-`delta` between floor((`ct`+1)/2) and floor((`ct`+1)/2) +1) or (`ct`%2 <> 0 and `seq`-`delta` = (`ct`+1)/2) ) as `t` GROUP BY t.game ORDER BY t.game;

game	median
EXAMPLE	13.5000
MARIO	9.0000
TETRIS	21.0000

game

median

EXAMPLE

13.5000

MARIO

9.0000

TETRIS

21.0000

Mysql 将值代入子查询

推荐答案

Mysql相关问答推荐

SQL-从一个字段中 Select 值，但另一个字段中不包含零值

我如何才能从MySQL过程中获取LARAVEL端的数据？

MySQL滑动窗口动态间隔？

无法连接到扩展坞MySQL Unix套接字

Python - 执行原始 SQL 时获取更新查询 (Mysql) 的结果

如何本地化 MySQL 表中的实体？

仅当 SELECT 语句在 MySQL 中给出空集时才执行 UPDATE 语句

java.lang.NullPointerException：无法调用com.proj.my.repository.OrderRepository.save(Object)，因为this.orderRepository为空

MySql部分匹配基于部分查询代码

添加一个日期字段大于另一个日期字段的要求

如何根据特定条件从mysql数据库中 Select 查询

如何在 Windows 上访问 xampp 的命令行

如何在mysql select查询中获取两个日期之间的日期列表

docker-entrypoint-initdb 中的 MySQL 脚本未执行

让 MySQL 在 OSX 10.7 Lion 上运行

Drupal 的默认密码加密方法是什么？

在mysql中增量更新值

由于在 MySQL 中使用保留字作为表名或列名导致的语法错误

Sequelize：销毁/删除表中的所有记录

带有 WHERE 子句的 MySql 内连接