sql - 从 SQL 中的下表中找出击球手获得了多少个帽子戏法 4

问题描述

CREATE TABLE 
cricket_scores ( 
Ball_No INT,Batsman varchar(30) NOT NULL, Bowler Varchar(30) NOT NULL, Runs INT, PRIMARY KEY(Ball_No)
);

INSERT INTO cricket_scores (ball_no,batsman,bowler,runs)
VALUES
(   101 ,   "Kohli" ,   "Anderson"  ,   1   )   ,
(   102 ,   "Sharma"    ,   "Anderson"  ,   1   )   ,
(   103 ,   "Kohli" ,   "Anderson"  ,   4   )   ,
(   104 ,   "Sharma"    ,   "Anderson"  ,   2   )   ,
(   105 ,   "Sharma"    ,   "Anderson"  ,   2   )   ,
(   106 ,   "Sharma"    ,   "Anderson"  ,   6   )   ,
(   201 ,   "Kohli" ,   "Robinson"  ,   1   )   ,
(   202 ,   "Sharma"    ,   "Robinson"  ,   1   )   ,
(   203 ,   "Kohli" ,   "Robinson"  ,   1   )   ,
(   204 ,   "Sharma"    ,   "Robinson"  ,   1   )   ,
(   205 ,   "Kohli" ,   "Robinson"  ,   2   )   ,
(   206 ,   "Kohli" ,   "Robinson"  ,   4   )   ,
(   301 ,   "Kohli" ,   "Curran"    ,   1   )   ,
(   302 ,   "Sharma"    ,   "Curran"    ,   1   )   ,
(   303 ,   "Sharma"    ,   "Curran"    ,   4   )   ,
(   304 ,   "Kohli" ,   "Curran"    ,   4   )   ,
(   305 ,   "Sharma"    ,   "Curran"    ,   4   )   ,
(   306 ,   "Kohli" ,   "Curran"    ,   6   )   ,
(   401 ,   "Sharma"    ,   "Ali"   ,   1   )   ,
(   402 ,   "Kohli" ,   "Ali"   ,   0   )   ,
(   403 ,   "Bumrah"    ,   "Ali"   ,   2   )   ,
(   404 ,   "Bumrah"    ,   "Ali"   ,   1   )   ,
(   405 ,   "Sharma"    ,   "Ali"   ,   4   )   ,
(   406 ,   "Bumrah"    ,   "Ali"   ,   4   )   ,
(   501 ,   "Sharma"    ,   "Root"  ,   4   )   ,
(   502 ,   "Bumrah"    ,   "Root"  ,   2   )   ,
(   503 ,   "Bumrah"    ,   "Root"  ,   2   )   ,
(   504 ,   "Bumrah"    ,   "Root"  ,   6   )   ,
(   505 ,   "Sharma"    ,   "Root"  ,   0   )   ,
(   506 ,   "Pandya"    ,   "Root"  ,   1   )   ,
(   601 ,   "Sharma"    ,   "Overton"   ,   2   )   ,
(   602 ,   "Sharma"    ,   "Overton"   ,   4   )   ,
(   603 ,   "Pandya"    ,   "Overton"   ,   2   )   ,
(   604 ,   "Pandya"    ,   "Overton"   ,   2   )   ,
(   605 ,   "Sharma"    ,   "Overton"   ,   1   )   ,
(   606 ,   "Pandya"    ,   "Overton"   ,   2   )   ,
(   701 ,   "Pandya"    ,   "Curran"    ,   0   )   ,
(   702 ,   "Ashwin"    ,   "Curran"    ,   2   )   ,
(   703 ,   "Ashwin"    ,   "Curran"    ,   2   )   ,
(   704 ,   "Pandya"    ,   "Curran"    ,   4   )   ,
(   705 ,   "Ashwin"    ,   "Curran"    ,   4   )   ,
(   706 ,   "Pandya"    ,   "Curran"    ,   4   )   ,
(   801 ,   "Ashwin"    ,   "Curran"    ,   2   )   ,
(   802 ,   "Ashwin"    ,   "Curran"    ,   2   )   ,
(   803 ,   "Ashwin"    ,   "Curran"    ,   6   )   ,
(   804 ,   "Pandya"    ,   "Curran"    ,   2   )   ,
(   805 ,   "Pandya"    ,   "Curran"    ,   2   )   ,
(   806 ,   "Pandya"    ,   "Curran"    ,   6   )   ,
(   901 ,   "Ashwin"    ,   "Anderson"  ,   4   )   ,
(   902 ,   "Pandya"    ,   "Anderson"  ,   4   )   ,
(   903 ,   "Ashwin"    ,   "Anderson"  ,   4   )   ,
(   904 ,   "Pandya"    ,   "Anderson"  ,   6   )   ,
(   905 ,   "Ashwin"    ,   "Anderson"  ,   6   )   ,
(   906 ,   "Pandya"    ,   "Anderson"  ,   0   )   ,
(   1001    ,   "Rahul" ,   "Anderson"  ,   6   )   ,
(   1002    ,   "Ashwin"    ,   "Anderson"  ,   6   )   ,
(   1003    ,   "Rahul" ,   "Anderson"  ,   6   )   ,
(   1004    ,   "Ashwin"    ,   "Anderson"  ,   1   )   ,
(   1005    ,   "Rahul" ,   "Anderson"  ,   2   )   ,
(   1006    ,   "Rahul" ,   "Anderson"  ,   4   )   ;

从下表中找出击球手得分了多少个帽子戏法 4（即 4 次连续得分 3 次）

请注意，balls_no 列包含组合的球数和球数，例如：101 = 1-over 01-ball, 205 = 2nd-over 05-ball, 1006 = 10th-over 06-ball

不是作业问题，这是我在面试时遇到的一个问题。

SQLite 或 MySQL，任何或两种迭代都是受欢迎的。

我能够找出答案，只需要更长的查询

SELECT overs, COUNT(BALLRANK)
FROM
(SELECT
    ball_no, overs, batsman, bowler,
    runs,
    DENSE_RANK () OVER ( 
      PARTITION BY overs
        ORDER BY ball_no
    ) BALLRANK    
FROM
    (SELECT ball_no, (CASE 
WHEN ball_no BETWEEN 100 AND 107 THEN "1st"
WHEN ball_no BETWEEN 200 AND 207 THEN "2nd"
WHEN ball_no BETWEEN 300 AND 307 THEN "3rd"
WHEN ball_no BETWEEN 400 AND 407 THEN "4th"
WHEN ball_no BETWEEN 500 AND 507 THEN "5th"
WHEN ball_no BETWEEN 600 AND 607 THEN "6th"
WHEN ball_no BETWEEN 700 AND 707 THEN "7th"
WHEN ball_no BETWEEN 800 AND 807 THEN "8th"
WHEN ball_no BETWEEN 900 AND 907 THEN "9th"
WHEN ball_no BETWEEN 1000 AND 1007 THEN "10th"
     ELSE 
        'NA' 
     END) as overs, batsman, bowler,
    runs
FROM cricket_scores
Where runs = 4)
    GROUP BY ball_no
    ORDER BY ball_no)
   GROUP BY overs
   HAVING COUNT(BALLRANK)=3;

标签： sqlsubquerydense-rank

sql - 从 SQL 中的下表中找出击球手获得了多少个帽子戏法 4

问题描述

解决方案

推荐阅读