SQLzoo练习题

20190725update

20190519更新

20190510更新 

每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。

SELECT m.matchid,mdate,m.num
  FROM(
SELECT go.matchid,COUNT(CASE WHEN go.teamid = 'GER' THEN 1 ELSE NULL END) AS num
  FROM game AS ga
  JOIN goal AS go
    ON ga.id = go.matchid
 WHERE (ga.team1 = 'GER' OR ga.team2 = 'GER')
 GROUP BY go.matchid) AS m,game AS ga
 WHERE ga.id = m.matchid
 
 ###错误表示1(这里没有考虑到万一德国进球为0咋办）

SELECT m.matchid,mdate,m.num
  FROM(
SELECT go.matchid,COUNT(go.player) AS num
  FROM game AS ga
  JOIN goal AS go
    ON ga.id = go.matchid
 WHERE go.teamid = 'GER'
 GROUP BY go.matchid) AS m,game AS ga
 WHERE ga.id = m.matchid
########错误表示2 groupby里面没有mdate但是select有这是个大错
SELECT go.matchid,ga.mdate,COUNT(go.player) AS num
  FROM game AS ga
  JOIN goal AS go
    ON ga.id = go.matchid
 WHERE go.teamid = 'GER'
 GROUP BY go.matchid

做法：创建数据集

CREATE TABLE game
 (
 id            INT(11)              NOT NULL,
 mdate         DATE                 NOT NULL,
 team1         VARCHAR(20)          NOT NULL,
 team2         VARCHAR(20)          NOT NULL
 )	;

DROP TABLE goal;
CREATE TABLE goal
 (
 matchid        INT(11)             NOT NULL,
teamid        VARCHAR(20)          NOT NULL,
gtime         INT                  NOT NULL
 )	;

INSERT INTO game VALUES(1001,'2019-05-01','POL','GRE');
INSERT INTO game VALUES(1002,'2019-05-02','RUS','CZE');
INSERT INTO game VALUES(1003,'2019-05-03','GRE','CZE');
INSERT INTO game VALUES(1004,'2019-05-04','MM','GRE');

INSERT INTO goal VALUES(1001,'POL',17);
INSERT INTO goal VALUES(1001,'POL',18);
INSERT INTO goal VALUES(1001,'GRE',20);
INSERT INTO goal VALUES(1001,'GRE',22);
INSERT INTO goal VALUES(1002,'RUS',50);
INSERT INTO goal VALUES(1002,'RUS',53);
INSERT INTO goal VALUES(1003,'CZE',80);
INSERT INTO goal VALUES(1004,'GRE',15);

 容易bug处

SELECT DISTINCT matchid
  FROM goal
 WHERE teamid = 'GRE'; -- 会选出来GRE进球的场次 但是GRE不进球的场次却忽略了 
 
SELECT id FROM game
 WHERE team1 = 'GRE' OR team2 = 'GRE'; -- 选出GRE参加的所有场次 

我的做法

SELECT ga.id,ga.mdate,
       (SELECT COUNT(*)
          FROM goal go
	 WHERE go.matchid = ga.id
           AND go.teamid = 'GRE') AS num
  FROM game ga
 WHERE id IN (SELECT id 
                FROM game
               WHERE team1 = 'GRE' OR team2 = 'GRE');

 首先确定id在GRE参加的所有场次里面（WHERE先执行）

然后去select 保证id和matchid一样 而且进球的是gre 看看有多少记录（COUNT）

最后结果是这样 

当然之前的做法也对思路是这样：

SELECT m.matchid,mdate,m.num
  FROM(
SELECT go.matchid,COUNT(CASE WHEN go.teamid = 'GRE' THEN 1 ELSE 0 END) AS num
  FROM game AS ga
  JOIN goal AS go
    ON ga.id = go.matchid
 WHERE (ga.team1 = 'GRE' OR ga.team2 = 'GRE')
 GROUP BY go.matchid) AS m,game AS ga
 WHERE ga.id = m.matchid;

看看先连接的后果是这样 就是先连接两个表，然后过滤game只有GRE的，

这个时候不用担心GRE没进球的不算，因为过滤的时候是对ga.team过滤的不是对go.team过滤的！！！！！！！！

然后对id进行group by

最后在两个表连接一下就行了 

SELECT t1.id,t1.mdate,COUNT(*)
  FROM (

SELECT ga.id,ga.mdate,go.teamid
  FROM goal go
  JOIN game ga
    ON go.matchid = ga.id
 WHERE ga.team1 = 'GRE'
    OR ga.team2 = 'GRE') t1
    WHERE t1.teamid = 'GRE'
 GROUP BY t1.id,t1.mdate;
 

SELECT t1.id,t1.mdate,COUNT(CASE WHEN t1.teamid = 'GRE' THEN 1 ELSE NULL END)
  FROM (
SELECT ga.id,ga.mdate,go.teamid
  FROM goal go
  JOIN game ga
    ON go.matchid = ga.id
 WHERE ga.team1 = 'GRE'
    OR ga.team2 = 'GRE') t1
 GROUP BY t1.id,t1.mdate;
 
SELECT COUNT(NULL)

第一个返回的时候没有0，第二个是正解有0

要注意SELECT COUNT（NULL）；返回0

##############0725更新 这样做就好了啊 不用那么麻烦

SELECT ga.id,ga.mdate,
       (SELECT SUM(CASE WHEN go.teamid = 'GRE' then 1 else 0 end)
          FROM goal go
	 WHERE go.matchid = ga.id
           ) AS num
  FROM game ga
 WHERE team1 = 'GRE' OR team2 = 'GRE';