sql - 将每场比赛 3 名玩家的分数保存到 PostgreSQL 中

Question

我正在尝试保存 card game 的分数匹配(总是有 3 个玩家)到 PotsgreSQL 8.4.9

我几乎拥有所有东西(请在下面查看)，只缺少 2 个小部件。

1. 首先，我尝试创建一个 PL/pgSQL 过程来保存分数
2. 然后我需要一个 join select 语句来检索比赛日期、玩家姓名、性别、头像、分数以及玩家是否过早退出游戏

我创建了 3 个 SQL 表:

    create table pref_users (
            uid varchar(32) primary key,
            first_name varchar(64),
            female boolean,
            avatar varchar(128)
    }

    create table pref_games {
            gid serial,
            rounds integer not null,
            finished timestamp default current_timestamp
    }

    create table pref_scores (
            uid varchar(32) references pref_users,
            gid serial references pref_games,  /* XXX serial ok here? */
            money integer not null,
            quit boolean
    );

这是我的 PL/pgSQL 程序，我需要一些帮助:

    create or replace function pref_insert_scores(
        _uid0 varchar, _money0 integer, _quit0 boolean,
        _uid1 varchar, _money1 integer, _quit1 boolean,
        _uid2 varchar, _money2 integer, _quit2 boolean,
        _rounds integer) returns void as $BODY$
            begin

            insert into pref_games (rounds) values (_rounds);

            -- XXX how do I get the _gid of this new game?

            insert into pref_scores (uid, gid, money, quit)
                values(_uid0, _gid, _money0, _quit0);

            insert into pref_scores (uid, gid, money, quit)
                values(_uid1, _gid, _money1, _quit1);

            insert into pref_scores (uid, gid, money, quit)
                values(_uid2, _gid, _money2, _quit2);
            end;
    $BODY$ language plpgsql;

然后我需要一些帮助来将分数与第一个表中的名字、女性、头像数据结合起来——这样我就可以在网页的表格中显示过去 7 天玩过的游戏列表:

01.12.2011  Alice $10        Bob $20 Charlie -$30  17 rounds
01.12.2011  Alice $0 (quit)  Bob $20 Charlie -$20  5  rounds

更新:

在 mu 太短 的帮助下，我现在的表格中充满了数据，但仍然无法弄清楚如何列出玩家进行的所有游戏 - 以及他的 2 个对手和他们的分数。

我有一张包含所有游戏的表格:

# select * from pref_games limit 5;
 gid | rounds |          finished
-----+--------+----------------------------
  1 |     10 | 2011-10-26 14:10:35.46725
  2 |     12 | 2011-10-26 14:34:13.440868
  3 |     12 | 2011-10-26 14:34:39.279883
  4 |     14 | 2011-10-26 14:35:25.895376
  5 |     14 | 2011-10-26 14:36:56.765978

然后在这里我有所有参加游戏 #3 的 3 名玩家(和他们的分数):

# select * from pref_scores where gid=3;
         uid           | gid | money | quit
-----------------------+-----+-------+------
 OK515337846127        |   3 |   -37 | f
 OK40798070412         |   3 |   -75 | f
 MR2871175175044094219 |   3 |   112 | f

这些都是uid = DE9411的玩家玩的游戏:

# select * from pref_scores where id='DE9411';
  uid   | gid | money | quit
--------+-----+-------+------
 DE9411 |  43 |    64 | f
 DE9411 | 159 |  -110 | f
 DE9411 | 224 |    66 | f
 DE9411 | 297 |   -36 | f
 DE9411 | 385 |    29 | f
 DE9411 | 479 |   -40 | f
 DE9411 | 631 |   -14 | f
 DE9411 | 699 |   352 | f
 DE9411 | 784 |   -15 | f
 DE9411 | 835 |   242 | f

但是我如何在上面的结果集中列出其他 2 个玩家及其分数？

Answer 1

你不需要pref_scores中的serial，只是int:

create table pref_scores (
        uid varchar(32) references pref_users,
        gid int references pref_games,  /* XXX serial ok here? */
        money integer not null,
        quit boolean
);

您想使用INSERT ... RETURNING ... INTO:

create or replace function pref_insert_scores(
    _uid0 varchar, _money0 integer, _quit0 boolean,
    _uid1 varchar, _money1 integer, _quit1 boolean,
    _uid2 varchar, _money2 integer, _quit2 boolean,
    _rounds integer) returns void as $BODY$
declare
    _gid int;
begin
    insert into pref_games (rounds) values (_rounds) returning gid into _gid;
    -- etc...