SQL实例 - 胜平负

1，创建一个表，用来存储足球比赛胜平负的数据。

CREATE TABLE "WinEvenLoseData" (

    "日期"    TEXT,

    "分类"    TEXT,

    "主"    TEXT,

    "客"    TEXT,

    "受让"    INTEGER,

    "胜"    REAL,

    "平"    REAL,

    "负"    REAL,

    "比分"    TEXT,

    "结果"    TEXT

)

Table: WinEvenLoseData

2，从表中取出各个球队的名称

SELECT 主 AS Name FROM WinEvenLoseData WHERE [比分] IS NOT NULL

UNION

SELECT 客 FROM WinEvenLoseData WHERE [比分] IS NOT NULL

ORDER BY Name

3，创建一个表，存储球队名字。

CREATE TABLE "Teams" (

    "ID"    INTEGER,

    "Name"    TEXT NOT NULL UNIQUE,

    PRIMARY KEY("ID" AUTOINCREMENT)

)

4，将前面的球队查询结果加入到这个表。

INSERT INTO Teams

SELECT null, [主] AS Name FROM WinEvenLoseData WHERE [比分] IS NOT NULL

UNION

SELECT null, [客] FROM WinEvenLoseData WHERE [比分] IS NOT NULL

ORDER BY Name

5，计算各个球队的主场平均进球数和失球数。在Team表中加入一列HostAvgGoal和HostAvgLoss

CREATE TABLE "Teams" (

    "ID"    INTEGER,

    "Name"    TEXT NOT NULL UNIQUE,

    "HostAvgGoal"    REAL,

    PRIMARY KEY("ID" AUTOINCREMENT)

)

6，计算的过程：

SELECT  SUM(SUBSTR([比分], 1, INSTR([比分] , '-')-1))*1.0/COUNT(*) AS name1, SUM(SUBSTR([比分], INSTR([比分] , '-')+1, LENGTH(比分)))*1.0/COUNT(*) AS name2

FROM WinEvenLoseData AS A

INNER JOIN Teams AS B

ON B.Name = A.[主]

WHERE A.[比分] IS NOT NULL

AND A.[受让] = 0

GROUP BY B.ID