时间戳

首页 > 解决方案 > SQL 查询查找 2012 年所有锦标赛的参赛人数？

mysql - SQL 查询查找 2012 年所有锦标赛的参赛人数？

问题描述

我的架构是：

CREATE TABLE 条目（MemberID int NOT NULL，TourID int NOT NULL，Year int NOT NULL，PRIMARY KEY (MemberID,TourID,Year)）；

        INSERT INTO entry VALUES (118,24,2011),(228,24,2012),(258,24,2011),(286,24,2010), 
        (286,24,2011),(286,24,2012),(415,24,2012),(228,25,2012),(239,25,2012),(415,25,2010), 
        (228,36,2012),(415,36,2011),(415,36,2012),(235,38,2010),(235,38,2012),(258,38,2011), 
        (415,38,2010),(415,38,2012),(235,40,2011),(235,40,2012),(239,40,2010),(415,40,2010), 
        (415,40,2011),(415,40,2012);

      CREATE TABLE tournament (
      TourID int NOT NULL,
      TourName varchar(20) DEFAULT NULL,
      TourType varchar(20) DEFAULT NULL,
      Active char(1) DEFAULT NULL,
      PRIMARY KEY (TourID)
      );

     INSERT INTO tournament VALUES (24,'London','Social','Y'),(25,'Leeds','Social','Y'), 
     (36,'Bath','Open','Y'),(38,'Liverpool','Open','N'),(40,'Birminigham','Open','Y');

我想获得 2012 年所有锦标赛的锦标赛 ID 和参赛人数？

到目前为止，我已经尝试过以下代码：

      SELECT tourid, COUNT(year) FROM entry WHERE year = 2012 GROUP BY tourid;


            

             |  tourid  |  count  |
             ______________________ 
             |    24    |   3     |
             |    25    |   2     |
             |    36    |   2     |
             |    38    |   2     |
             |    40    |   2     |
             ______________________

并获得上述值......我不确定它是否正确。有人请帮助我。谢谢

标签： mysqlsql

解决方案

对于显示锦标赛名称的情况，请尝试以下操作：

 SELECT entry.tourid, tournament.tourname, COUNT(year) 
 FROM entry 
 JOIN tournament ON entry.tourid=tournament.tourid
 WHERE year = 2012 
 GROUP BY entry.tourid, tournament.tourname;

在相同的列上使用JOIN 。这里它的TourID.

您可以通过为表分配别名来使其更好一些（更短/更容易编写）。

SELECT A.tourid, B.tourname, 
       COUNT(*) -- columns in SELECT also can assign alias like "COUNT(*) AS Cn" etc.
 FROM entry AS A -- you can either assign table alias "entry AS A" or simply "entry A" 
 JOIN tournament AS B 
   ON A.tourid=B.tourid
 WHERE A.year = 2012 
 GROUP BY A.tourid, B.tourname;

这是一个演示

推荐阅读