我正在嘗試查找顯示每個至少輸掉一場比賽的球員的輸球率的查詢。在SQLITE中

我需要幫助找出每個輸掉至少一場比賽的球員的輸球率。輸球率是輸球次數與比賽次數之比。對于至少輸掉一場比賽的每個玩家，只顯示他的識別符號 (id) 和他的失敗率。

以下是表格：

    CREATE TABLE champions
    (
        id_champion INT PRIMARY KEY NOT NULL,
        name VARCHAR(20),
        title VARCHAR(20),
        attack INT,
        defense INT,
        magic INT,
        difficulty INT
    );
    
    CREATE TABLE players
    (
        id_player INT PRIMARY KEY NOT NULL,
        name VARCHAR(20),
        country VARCHAR(20)
    );
    
    CREATE TABLE matchs
    (
        id_match INT,
        player INT,
        champion INT,
        number INT,
        gold INT,
        team INT,
        position VARCHAR(10),
        PRIMARY KEY (id_match, player),
        FOREIGN KEY(id_match) REFERENCES matchsmeta(id_match) ON DELETE SET NULL,
        FOREIGN KEY(player) REFERENCES players(id_player) ON DELETE SET NULL,
        FOREIGN KEY(champion) REFERENCES champions(id_champion) ON DELETE SET NULL
    
    );
    
    CREATE TABLE matchsmeta
    (
        id_match INT PRIMARY KEY NOT NULL,
        time INT,
        victory INT,
        date DATE,
        game_mode VARCHAR(10)
    );

這是我嘗試過的：

SELECT a.id_player, 
       ( (c.victory * 1.0) / COUNT(c.id_match)) AS "Taux de Defaite" FROM players AS a 
INNER JOIN matchs AS b 
INNER JOIN matchsmeta AS c ON a.id_player = player 
AND b.id_match = c.id_match;

但問題是它沒有向我展示我想要的東西。

這是一個非常簡短的資料：

INSERT INTO champions VALUES(1, 'Dragon', 'Drag', 1000, 1000, 'Feu', 10);
INSERT INTO champions VALUES(2, 'Slime', 'Slime', 100, 100, 'Translucide', 1);
INSERT INTO champions VALUES(3, 'Demon', 'Demon', 10000, 5000, 'DarkSide', 10);

INSERT INTO players VALUES(1, 'Nobody1', 'France');
INSERT INTO players VALUES(2, 'Nobody2', 'France');
INSERT INTO players VALUES(3, 'Nobody3', 'France');
INSERT INTO players VALUES(4, 'Nobody4', 'France');
INSERT INTO players VALUES(5, 'Nobody5', 'France');

INSERT INTO matchs VALUES(1, 1, 1, 1, 1000, 100, 'TOP');
INSERT INTO matchs VALUES(1, 2, 2, 2, 1000, 100, 'JUNGLE');
INSERT INTO matchs VALUES(1, 3, 3, 3, 1000, 100, 'MID');
INSERT INTO matchs VALUES(3, 4, 3, 4, 1000, 200, 'SUPPORT');
INSERT INTO matchs VALUES(1, 5, 3, 5, 1000, 200, 'BOTTOM');

INSERT INTO matchsmeta VALUES(1, 60, 100, '2022-01-01', 'CLASSIC');
INSERT INTO matchsmeta VALUES(2, 90, 100, '2022-02-01', 'CLASSIC');
INSERT INTO matchsmeta VALUES(3, 60, 100, '2022-03-01', 'CLASSIC');

uj5u.com熱心網友回復：

加入表matchs并matchsmeta使用條件聚合：

SELECT m.player, 
       AVG(mm.victory <> m.team) [Taux de Defaite] 
FROM matchs m INNER JOIN matchsmeta mm 
ON mm.id_match = m.id_match 
GROUP BY m.player
HAVING [Taux de Defaite] > 0;

players不需要該表，因為您只希望結果中存在該表中存在的玩家 id matchs。

請參閱演示。

uj5u.com熱心網友回復：

如果我正確理解了資料，您的查詢應該如下所示：

SELECT player
, COUNT(*) AS MatchesPlayed
, SUM(CASE WHEN m.team = mm.victory THEN 1 ELSE 0 END) AS MatchesWon
, SUM(CASE WHEN m.team != mm.victory THEN 1 ELSE 0 END) AS MatchesLost
, SUM(CASE WHEN m.team != mm.victory THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS LossRatio
FROM matchs m
INNER JOIN matchsmeta mm ON m.id_match = mm.id_match
GROUP BY m.player

基本上，如果你想找到每個球員的資料，你應該按球員分組，計算你的總和（上場，輸球）并劃分它們。

uj5u.com熱心網友回復：

在 SQLITE 中，使用像 COUNT() 這樣的聚合函式會自動暗示聚合。如果您不指定 GROUP BY 子句，它將聚合所有行。您可能還想按損失率排序并過濾掉那些沒有損失的。

嘗試這個：

SELECT a.id_player,(SUM(c.victory * 1.0) / COUNT(c.id_match)) AS "Taux de Defaite" FROM players AS a 
INNER JOIN matchs AS b 
INNER JOIN matchsmeta AS c ON a.id_player = player AND b.id_match = c.id_match
GROUP BY a.id_player
HAVING (SUM(c.victory * 1.0) / COUNT(c.id_match)) > 0
ORDER BY (SUM(c.victory * 1.0) / COUNT(c.id_match));

標籤：sql sqlite

上一篇：如何在文本旁邊寫下確切的文本并替換其中的某些字母？SQLite

下一篇：顫振sqflite如何接收“待辦事項”物件以顯示值？