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';