如何将数据存储在SQLite中的下一个值列中?

nhjlsmyf  于 2023-10-23  发布在  SQLite
关注(0)|答案(1)|浏览(159)

我正在为一个锦标赛跟踪软件创建一个数据库,其中一部分涉及将用户的ID存储为稍后参考的值。为了做到这一点,我使用了两张table;player(主键 player_ID),存储事件中玩家的信息,例如他们的 user_ID(不相关表的一部分)和他们所玩的比赛的结果,以及 event,存储所有事件细节,以及所有16个事件参与者的 player_ID。这些列被命名为 * player 1 * -> * player 16 *
我的问题是试图找到一种方法来分别填充这些列。当事件被创建时,所有16列开始都以值“0”开始,并且当用户注册事件时将被更改为在 player 表中创建的 player_ID
我的第一个想法是为每个列创建单独的查询,以顺序检查它们是否包含非重复的值,然后更新第一列以匹配此条件并打破检查,但是使用32个查询来覆盖每个选项似乎非常低效。有没有办法把它压缩到一个更合理的尺寸,如果有,我会怎么做?

axr492tv

axr492tv1#

建议您考虑第三个表,这是一个Map(关联表,参考表,Map表和其他术语),用于将玩家Map到事件,并且在将另一个玩家添加到事件时没有问题/并发症,而不是限制为每个事件16个(相对简单)。
这样的表支持many-many关系。这就是说,一个事件可以有许多球员,一个球员可以在许多事件。
这样的表由2个主要列组成,一个引用事件,另一个引用玩家。主键通常应该由这两列组成(因此玩家不能处于同一事件中)。
一个事件最初将没有球员,球员可以添加。唯一的问题是限制16名球员参加一个事件,这只是意味着检查事件中的球员数量(演示SELECT QUERY包括获得计数)。
下面是一个demo:

/* just in case cleanup the environment */
DROP TABLE IF EXISTS event_participant;
DROP TABLE IF EXISTS player;
DROP TABLE IF EXISTS event;
CREATE TABLE IF NOT EXISTS player (player_id INTEGER PRIMARY KEY, player_name TEXT);
CREATE TABLE IF NOT EXISTS event (event_id INTEGER PRIMARY KEY, event_name TEXT /* etc */);
/* THE MAPPING TABLE */
CREATE TABLE IF NOT EXISTS event_participant (
    event_id_map INTEGER REFERENCES event(event_id) ON DELETE CASCADE ON UPDATE CASCADE, 
    player_id_map INTEGER REFERENCES player(player_id) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (event_id_map,player_id_map));
    /* Note FOREIGN KEY (aka REFERENCES) is optional but suggested as it enforces referential integrity */
        /* ON DELETE and ON UPDATE are optional but help to maintain referential integrity again suggested */
/* SUGGESTED INDEX ON the 2nd column, will liekly improve efficiency */
CREATE INDEX ix_event_participant_playermap ON event_participant (player_id_map);
/* ADD SOME DATA */
INSERT OR IGNORE INTO player (player_name) VALUES('Mary'),('Jane'),('Tom'),('Fred'),('Anne'),('Bert'),('Alan'),('Beth');
INSERT OR IGNORE INTO event (event_name) VALUES('E1'),('E2'),('E3'),('E4');
/* NOTHING SELECTED as there are currently no events with participants */
SELECT 
    event_name,
    group_concat(player_name,'::') AS players,
    count(*) AS num_of_players 
FROM event
    JOIN event_participant ON event.event_id = event_participant.event_id_map
    JOIN player ON player_id_map = player_id
GROUP BY event_id
ORDER BY num_of_players
/* Add some participants to some events */
;
INSERT OR IGNORE INTO event_participant VALUES
    (1,2),(1,4),(1,8) /* Jane, Fred and Beth in event 1*/,
    (2,1),(2,3),(2,5),(2,7) /* Mary, Tom, Anne and Alan in event 2 */,
    (3,1),(3,4),(3,8),(3,6),(3,2),(3,7),(3,5),(3,3) /* all in event 3 note any order */,
    /*!!!!!!!!!! OOOPS ON PURPOSE !!!!!!!!!!*/ (1,2),(1,4),(1,8) /* Jane, Fred and Beth in event 1??????????*/
    /* ooops because they already exist as participants in the event */ 
;
/* Now there are participants then the exact same query as above will yield results */
SELECT 
    event_name,
    group_concat(player_name,'::') AS players,
    count(*) AS num_of_players 
FROM event
    JOIN event_participant ON event.event_id = event_participant.event_id_map
    JOIN player ON player_id_map = player_id
GROUP BY event_id
ORDER BY num_of_players
;
/* Cleanup demo environment */
DROP TABLE IF EXISTS event_participant;
DROP TABLE IF EXISTS player;
DROP TABLE IF EXISTS event;

当运行时,消息日志显示:-

/* just in case cleanup the environment */
DROP TABLE IF EXISTS event_participant
> OK
> Time: 0.323s

DROP TABLE IF EXISTS player
> OK
> Time: 0.041s

DROP TABLE IF EXISTS event
> OK
> Time: 0.024s

CREATE TABLE IF NOT EXISTS player (player_id INTEGER PRIMARY KEY, player_name TEXT)
> OK
> Time: 0.024s

CREATE TABLE IF NOT EXISTS event (event_id INTEGER PRIMARY KEY, event_name TEXT /* etc */)
> OK
> Time: 0.024s

/* THE MAPPING TABLE */
CREATE TABLE IF NOT EXISTS event_participant (
    event_id_map INTEGER REFERENCES event(event_id) ON DELETE CASCADE ON UPDATE CASCADE, 
    player_id_map INTEGER REFERENCES player(player_id) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (event_id_map,player_id_map))
> OK
> Time: 0.024s

/* Note FOREIGN KEY (aka REFERENCES) is optional but suggested as it enforces referential integrity */
        /* ON DELETE and ON UPDATE are optional but help to maintain referential integrity again suggested */
/* SUGGESTED INDEX ON the 2nd column, will liekly improve efficiency */
CREATE INDEX ix_event_participant_playermap ON event_participant (player_id_map)
> OK
> Time: 0.024s

/* ADD SOME DATA */
INSERT OR IGNORE INTO player (player_name) VALUES('Mary'),('Jane'),('Tom'),('Fred'),('Anne'),('Bert'),('Alan'),('Beth')
> Affected rows: 8
> Time: 0.024s

INSERT OR IGNORE INTO event (event_name) VALUES('E1'),('E2'),('E3'),('E4')
> Affected rows: 4
> Time: 0.024s

/* NOTHING SELECTED as there are currently no events with participants */
SELECT 
    event_name,
    group_concat(player_name,'::') AS players,
    count(*) AS num_of_players 
FROM event
    JOIN event_participant ON event.event_id = event_participant.event_id_map
    JOIN player ON player_id_map = player_id
GROUP BY event_id
ORDER BY num_of_players
/* Add some participants to some events */
> OK
> Time: 0s

INSERT OR IGNORE INTO event_participant VALUES
    (1,2),(1,4),(1,8) /* Jane, Fred and Beth in event 1*/,
    (2,1),(2,3),(2,5),(2,7) /* Mary, Tom, Anne and Alan in event 2 */,
    (3,1),(3,4),(3,8),(3,6),(3,2),(3,7),(3,5),(3,3) /* all in event 3 note any order */,
    /*!!!!!!!!!! OOOPS ON PURPOSE !!!!!!!!!!*/ (1,2),(1,4),(1,8) /* Jane, Fred and Beth in event 1??????????*/
    /* ooops because they already exist as participants in the event */
> Affected rows: 15
> Time: 0.024s

/* Now there are participants then the exact same query as above will yield results */
SELECT 
    event_name,
    group_concat(player_name,'::') AS players,
    count(*) AS num_of_players 
FROM event
    JOIN event_participant ON event.event_id = event_participant.event_id_map
    JOIN player ON player_id_map = player_id
GROUP BY event_id
ORDER BY num_of_players
> OK
> Time: 0s

/* Cleanup demo environment */
DROP TABLE IF EXISTS event_participant
> OK
> Time: 0.023s

DROP TABLE IF EXISTS player
> OK
> Time: 0.024s

DROP TABLE IF EXISTS event
> OK
> Time: 0.052s

第一个查询的结果(即无):-

第二次查询的结果(即当事件有参与者时):-

首先有3行,因为event_id上有GROUP BY子句。即,具有相同event_id值的所有行被分组在一起作为单个输出行。
可以看出,行按事件中参与者的数量进行ORDER艾德,较低的数字首先输出(默认顺序是ASC结束)。

玩家数量就是这个数字,并使用SQLite聚合count函数。请参阅https://www.sqlite.org/lang_aggfunc.html#count
players列再次使用SQLite聚合函数,这次使用的是group_concat函数,该函数将传递给它的表达式中的值与组中每行的所有其他值连接起来。请参阅https://www.sqlite.org/lang_aggfunc.html#group_concat

  • 注意,SELECT中的JOIN演示了指定列的两种形式。一种形式是用句点将表名与列名分隔开,从而完全限定列名。第二种形式只是列名。后一种形式更短,更容易**,但**容易产生歧义。
  • 例如,如果“event_id”和“player_id”列改为仅命名为id,则使用列名id将是歧义的并且导致错误。
    交替

另一种不使用Map表的方法是使用CASE WHEN THEN END子句。
考虑这个演示,它逐步填充事件表中的空列。* 注意,整个过程中使用了完全相同的UPDATE,但播放器通过了(硬编码,但在调用时会被传递/绑定(就像WHERE子句中使用的事件的id一样)):

DROP TABLE IF EXISTS player;
DROP TABLE IF EXISTS event;
CREATE TABLE IF NOT EXISTS player (player_id INTEGER PRIMARY KEY, player_name TEXT);
CREATE TABLE IF NOT EXISTS event (event_id INTEGER PRIMARY KEY, event_name TEXT, 
    p1 INTEGER DEFAULT NULL,
    p2 INTEGER DEFAULT NULL,
    p3 INTEGER DEFAULT NULL,
    p4 INTEGER DEFAULT NULL,
    p5 INTEGER DEFAULT NULL,
    p6 INTEGER DEFAULT NULL
);
INSERT INTO player VALUES (1,'Fred'),(2,'Mary'),(3,'Anne'),(4,'Tom'),(5,'Jane'),(6,'Alan'),(7,'Joe'),(8,'Sue'),(9,'Jan');
INSERT INTO event (event_id,event_name) VALUES (10,'E1'),(11,'E2');

WITH cte(new_player) AS (SELECT 1 /* id of new player to add to the event passed by invocation */)
UPDATE event
    SET 
        p1 = CASE WHEN p1 IS NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p1 END,
        p2 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p2 END,
        p3 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p3 END,
        p4 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NOT NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p4 END,
        p5 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NOT NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p5 END,
        p6 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NOT NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p6 END
WHERE event_id = 10 /* value would be set (bound) by invocation */
;
SELECT * FROM event;

WITH cte(new_player) AS (SELECT 5 /* id of new player to add to the event passed by invocation */)
UPDATE event
    SET 
        p1 = CASE WHEN p1 IS NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p1 END,
        p2 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p2 END,
        p3 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p3 END,
        p4 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NOT NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p4 END,
        p5 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NOT NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p5 END,
        p6 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NOT NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p6 END
WHERE event_id = 10 /* value would be set (bound) by invocation */
;
SELECT * FROM event;

WITH cte(new_player) AS (SELECT 3 /* id of new player to add to the event passed by invocation */)
UPDATE event
    SET 
        p1 = CASE WHEN p1 IS NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p1 END,
        p2 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p2 END,
        p3 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p3 END,
        p4 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NOT NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p4 END,
        p5 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NOT NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p5 END,
        p6 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NOT NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p6 END
WHERE event_id = 10 /* value would be set (bound) by invocation */
;
SELECT * FROM event;

WITH cte(new_player) AS (SELECT 2 /* id of new player to add to the event passed by invocation */)
UPDATE event
    SET 
        p1 = CASE WHEN p1 IS NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p1 END,
        p2 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p2 END,
        p3 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p3 END,
        p4 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NOT NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p4 END,
        p5 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NULL AND p4 IS NOT NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p5 END,
        p6 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NOT NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p6 END
WHERE event_id = 10 /* value would be set (bound) by invocation */
;
SELECT * FROM event;

WITH cte(new_player) AS (SELECT 1 /* id of new player to add to the event passed by invocation */)
UPDATE event
    SET 
        p1 = CASE WHEN p1 IS NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p1 END,
        p2 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p2 END,
        p3 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p3 END,
        p4 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NOT NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p4 END,
        p5 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NOT NULL AND p4 IS NOT NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p5 END,
        p6 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NOT NULL AND p4 IS NOT NULL AND p5 IS NOT NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p6 END
WHERE event_id = 10 /* value would be set (bound) by invocation */
;
SELECT * FROM event;

WITH cte(new_player) AS (SELECT 99 /* id of new player to add to the event passed by invocation */)
UPDATE event
    SET 
        p1 = CASE WHEN p1 IS NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p1 END,
        p2 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p2 END,
        p3 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p3 END,
        p4 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NOT NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p4 END,
        p5 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NOT NULL AND p4 IS NOT NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p5 END,
        p6 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NOT NULL AND p4 IS NOT NULL AND p5 IS NOT NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p6 END
WHERE event_id = 10 /* value would be set (bound) by invocation */
;
SELECT * FROM event;

WITH cte(new_player) AS (SELECT 666 /* id of new player to add to the event passed by invocation */)
UPDATE event
    SET 
        p1 = CASE WHEN p1 IS NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p1 END,
        p2 = CASE WHEN p1 IS NOT NULL AND p2 IS NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p2 END,
        p3 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p3 END,
        p4 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NOT NULL AND p4 IS NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p4 END,
        p5 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NOT NULL AND p4 IS NOT NULL AND p5 IS NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p5 END,
        p6 = CASE WHEN p1 IS NOT NULL AND p2 IS NOT NULL AND p3 IS NOT NULL AND p4 IS NOT NULL AND p5 IS NOT NULL AND p6 IS NULL THEN (SELECT * FROM cte) ELSE p6 END
WHERE event_id = 10 /* value would be set (bound) by invocation */
;
SELECT * FROM event;
DROP TABLE IF EXISTS player;
DROP TABLE IF EXISTS event;

按照SELECT查询的顺序,结果是:

最后在尝试添加第七个之后,玩家为666(即,该事件已满),该球员是不适用:-

  • 使用CommonTableE表达式(适合演示,而不是硬编码相同的值,使用相同的绑定值可以避免这一点);
  • 参见https://www.sqlite.org/lang_with.html
    稍微复杂一点,但更紧凑一点又名多CTE

这个例子进一步使用多个CTE,所以更复杂,但不太容易出现编码错误(使用IS NULL/IS NOT NULL编码)。
该示例基于与先前使用和加载的相同的2个表eventplayer
SQL是:

WITH 
    cte_values(eid,pid) AS (SELECT 10 /*<<<<< event id to update */,1 /* player id to apply to first NULL column */),
    cte_current_state(ord,flag) AS (
        SELECT 1,coalesce(p1,0) FROM event WHERE event_id = (SELECT eid FROM cte_values)
            UNION SELECT 2,coalesce(p2,0) FROM event WHERE event_id = (SELECT eid FROM cte_values)
            UNION SELECT 3,coalesce(p3,0) FROM event WHERE event_id = (SELECT eid FROM cte_values)
            UNION SELECT 4,coalesce(p4,0) FROM event WHERE event_id = (SELECT eid FROM cte_values)
            UNION SELECT 5,coalesce(p5,0) FROM event WHERE event_id = (SELECT eid FROM cte_values)
            UNION SELECT 6,coalesce(p6,0) FROM event WHERE event_id = (SELECT eid FROM cte_values)
        ),
    cte_free_column AS (SELECT min(ord) AS ord FROM cte_current_state WHERE flag = 0)
UPDATE event SET 
    p1 = CASE WHEN (SELECT ord FROM cte_free_column) = 1 THEN (SELECT pid FROM cte_values) ELSE p1 END,
    p2 = CASE WHEN (SELECT ord FROM cte_free_column) = 2 THEN (SELECT pid FROM cte_values) ELSE p2 END,
    p3 = CASE WHEN (SELECT ord FROM cte_free_column) = 3 THEN (SELECT pid FROM cte_values) ELSE p3 END,
    p4 = CASE WHEN (SELECT ord FROM cte_free_column) = 4 THEN (SELECT pid FROM cte_values) ELSE p4 END,
    p5 = CASE WHEN (SELECT ord FROM cte_free_column) = 5 THEN (SELECT pid FROM cte_values) ELSE p5 END,
    p6 = CASE WHEN (SELECT ord FROM cte_free_column) = 6 THEN (SELECT pid FROM cte_values) ELSE p6 END
WHERE event_id = (SELECT eid FROM cte_values);
  • 请注意,第一个CTE(cte_values)提供了要更新的事件的ID和要应用的播放器的ID(同样仅当有空闲列时)。
  • 第二个CTEcte_current_state生成一个表,在事件表中每列一行(因为是硬编码的,所以16列将需要另外10个UNION SELECT)。生成的表有两列ord,这是列顺序(即p1是1,p2是2,以此类推)和flag,其为0(指示事件表行中相应列中的NULL,用于正在处理/更新的事件id)。因此,在示例/演示中,将有6行输出。
  • 第三个CTEcte_free_columncte_current_state的单行中提取ord(列指示符)。单行是ord值是flag为0的那些行中最低的行(即相应列为空)。

第一个CTE(通过eid列更新的事件和设置相应列的播放器id)和第三个CTE(用于确定要更新哪个列(如果有的话))用于驱动更新。
使用多个调用来逐步填充事件(类似于前面的例子),在每个结果之后使用SELECT(注意,使用的玩家ID是不同的,但是在所有列都被填充之后仍然使用666)。选择的结果是:

  • 在创建偶数表之后和UPDATE

    之前立即执行

  • 第一次更新后

  • 第二次更新后

    x 1c 13d1xx 1c 14d1xx 1c 15d1xx 1c 16d1x

  • 所有列都已设置,因此尝试溢出:-

  • 由于事件已经满,所以没有应用播放器ID 666。

这里有一个DB Fiddle的链接,其中包含最后一个示例的完整SQL代码-https://www.db-fiddle.com/f/i8BtXgUwBX6T5wEJpbYWYd/0

相关问题