Question

我正在建立一个锦标查跟踪软件数据库,其中一部分涉及储存用户的身份证,作为日后参考的价值。为此,我使用2张表格;meer<>em>(主要钥匙player_ID),其中储存有关某一活动参与者的信息,如:user_ID。 (部分不相关的表格)及其所起到的对应作用,event,其中载列所有活动细节,以及所有16个活动参与者的me_ID。页: 1 ->player16

我的问题在于设法单独填补这些专栏。活动创建时,所有16个栏目均以数值Null开始,并将改为player_ID在 > 表格中创建的,在活动用户登记册上登记。

My first thought was to create individual queries for each of the columns to sequentially check if they contained a non-Null value, then update the first column to match this criteria and break out of the check, however it seems highly inefficient to have 32 queries to cover every option. Is there a way to compact this to a more reasonable size, and if so, how would I do so?

Answer 1

建议你考虑第三张表格,即地图(联系表格、参考表、制图表和其他术语),以在每场活动之外增加另一个参与者,而不是限制在16个(相对简单)。

Such a table supports a many-many relationship. That is an event could have many players and a player could be in many events.

Such a table consists of 2 primary columns, one to reference the event and the other to reference the player. The primary key should typically consist of both these columns (so a player cannot be in the same event).

Initially an event will have no players and players can be added. The only issue would be limiting to 16 players to an event, which simply means checking the number of players in the event (demo SELECT QUERY includes obtaining the count).

页: 1

/* 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

The result of the first query (i.e. nothing):-

The result of the second query (i.e. when events have participants) :-

首先,有三行,因为有<代码>。按关于活动_id的条款分类。即,所有有相同事件的行号——id值合并为一个单一输出行。

可以看出,行文为<代码>。 ORDERed by the number of participants in an event the low number being输出 first (as the Object Order is ASCending).

The number of players is exactly that and utilises the SQLite aggregate count function. see https://www.sqlite.org/lang_aggfunc.html#count

The players column again uses an SQLite aggregate function, this time though the group_concat function, which concatenates the value from the expression passed to it with all the other values for each row in the group. see https://www.sqlite.org/lang_aggfunc.html#group_concat

请注意,JOIN in the SlectT显示了两种具体栏目。一种形式是,表格名称与栏目按期分开,从而完全符合栏目名称。第二类只是一栏名。后一种形式较短,更容易BUT。
e.g. if the event_id and player_id columns were instead named just id then using the column name id would be ambiguos and result in an error.

Alternately

An alternative, not using a mapping table could be utilise CASE WHEN THEN END clauses.

Consider this demo, that progressively fills null columns in an event table. ***Noting that the exact same UPDATE is used throughout BUT that the player passed (hard coded but would be passed/bound upon invocation (as would the id of the event used in the WHERE clause)):-

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;

The results, in order, as per the SELECT queries, being:-

and finally after an attempt to add a seventh, with a player as 666 (i.e. the event is already full), that the player is not applied:-

use is made of a Common Table Expression (which suits the demo rather than hard coding the same value throughout, using the same bound value could circumvent this);
- see https://www.sqlite.org/lang_with.html

友情链接