如何创建SQL查询，根据另一列的值获取每个组的列的惟一值

First of all I'm sorry for confusing name of my question, I didn't know how to ask it correctly.

首先，我很抱歉我的问题名字弄混了，我不知道如何正确地问它。

I will show the example of what I'm trying to do. Let's imagine we have 3 tables: 'footballer', 'team' and 'score' in a database (I'm using MySQL 5.5 as DBMS). They are very straightforward:

我将展示我要做的事情的例子。假设我们在数据库中有3个表:“足球运动员”、“团队”和“得分”(我使用MySQL 5.5作为DBMS)。他们非常简单:

CREATE TABLE footballer (
  fb_pk      INT(8) NOT NULL AUTO_INCREMENT,
  first_name CHAR(40),
  last_name  CHAR(40),
  PRIMARY KEY (fb_pk)
);

CREATE TABLE team (
  team_pk INT(8) NOT NULL AUTO_INCREMENT,
  name    CHAR(40),
  PRIMARY KEY (team_pk)
);

CREATE TABLE score (
  score_pk      INT(8) NOT NULL AUTO_INCREMENT,
  goal_count    INT(3) NOT NULL,
  team_fk       INT(8) NOT NULL,
  fb_fk         INT(8) NOT NULL,
  goal_distance ENUM('LONG', 'SHORT'),
  PRIMARY KEY (score_pk),
  CONSTRAINT fk_team FOREIGN KEY (team_fk) REFERENCES team (team_pk),
  CONSTRAINT fk_footballer FOREIGN KEY (fb_fk) REFERENCES footballer (fb_pk)
);

'score' table stores information about goals made by teams & football players, where:

“score”表存储球队和足球运动员的进球信息，其中:

'goal_count' - is a number of goals made,

“goal_count”是一系列的进球，

'team_fk' - is a foreign key for the team,

“team_fk”是团队的外键，

'fb_fk' - is a foreign key for a footballer and

fb_fk是足球运动员使用的外国钥匙

'goal_distance' - is a type of goal(s) were made (long or short).

“目标距离”是一种目标类型(长或短)。

We have the following data in the tables:

我们在表格中有如下数据:

INSERT INTO footballer (first_name, last_name)
VALUES
  ('Footballer', 'One'),
  ('Footballer', 'Two'),
  ('Footballer', 'Three'),
  ('Footballer', 'Four'),
  ('Footballer', 'Five');

INSERT INTO team (name)
VALUES
  ('Team 1'),
  ('Team 2'),
  ('Team 3');

INSERT INTO score (goal_count, team_fk, fb_fk, goal_distance)
VALUES
  (2, 1, 1, 'SHORT'), -- Two goals | by team #1 | by player #1 | short goal
  (3, 1, 1, 'LONG'),
  (1, 1, 2, 'SHORT'),
  (1, 2, 1, 'SHORT'),
  (2, 2, 2, 'SHORT'),
  (2, 2, 4, 'LONG');

Row (2, 1, 1, 'SHORT') means that 2 short goals were made by player 1 from team 1.

第一行(2,1,1，" SHORT ")表示1队的球员1进了2个短球。

Now I want to collect the statistics data for the 'score' table: how much short / long / total goals were made by each team and how much players within the each team made the goals. Here is my query with comments:

现在我想收集“分数”表的统计数据:每个队的短/长/总进球数是多少，每个队有多少球员进球数。以下是我的提问:

SELECT
  -- Team name
  t.name,
  -- How much short goals
  (
    SELECT
      SUM(s1.goal_count)
    FROM score s1
    WHERE s1.team_fk = s.team_fk AND s1.goal_distance = 'SHORT'
  ) short_count,
  -- How much long goals
  (
    SELECT
      SUM(s1.goal_count)
    FROM score s1
    WHERE s1.team_fk = s.team_fk AND s1.goal_distance = 'LONG'
  ) long_count,
  -- Total goals
  (SELECT short_count + long_count) total,
  -- How much players made goals
  (
    SELECT COUNT(*)
    FROM (
      SELECT DISTINCT
        team_fk, fb_fk
      FROM score
    ) s1
    WHERE s1.team_fk = s.team_fk
  ) goal_player_count
FROM score s
INNER JOIN team t
  ON s.team_fk = t.team_pk
GROUP BY s.team_fk;

Query returns the following result:

查询返回以下结果:

It works as expected, but I think there is a better way to get the count of 'players within the each team that made the goals'.

这和预期的一样，但我认为有一种更好的方法来统计“每个球队中达成目标的球员数量”。

To become more clear how this value must be calculated, here is an example:

为了更清楚地说明这个值是如何计算的，这里有一个例子:

There are 3 rows in a 'score' table for team #1:

团队1的“得分”表中有3行:

(2, 1, 1, 'SHORT'),
(3, 1, 1, 'LONG'),
(1, 1, 2, 'SHORT'),

Player 1 made 2 short goals,

球员1进2个短球，

Player 1 made 3 long goals,

球员1进了3个长球，

Player 2 made 1 short goal.

球员2进了一个短球。

In total, there are 2 players who made goals for this team (Player 1 and Player 2).

总共有两名球员为球队进球(球员1和球员2)。

Currently, this peace of query is responsible for calculating that value:

目前，这种查询平静负责计算该值:

(
    SELECT COUNT(*)
    FROM (
      SELECT DISTINCT
        team_fk, fb_fk
      FROM score
    ) s1
    WHERE s1.team_fk = s.team_fk
) goal_player_count

Maybe there is another (better) way to achieve the same result? Because this part of query looks ugly/unusual for me (SELECT inside the SELECT inside the SELECT).

也许还有其他(更好的)方法来达到同样的结果?因为查询的这一部分对我来说很难看/不寻常(在SELECT内部选择)。

1 个解决方案

#1

I think you can get the information you want in a much smaller query:

我认为你可以通过一个小得多的查询得到你想要的信息:

  SELECT t.name,
         SUM((s.goal_distance = 'SHORT') * s.goal_count) short_count,
         SUM((s.goal_distance = 'LONG')  * s.goal_count) long_count,
         SUM(s.goal_count) total,
         COUNT(DISTINCT f.fb_pk) goal_player_count
    FROM team t
    JOIN score s
      ON s.team_fk = t.team_pk
    JOIN footballer f
      ON f.fb_pk = s.fb_fk
GROUP BY t.team_pk

SQLFiddle Demo

SQLFiddle演示

This makes use of the fact that in MySQL a comparison, e.g. s.goal_distance = 'SHORT' will return 0 for false or 1 for true (or NULL) thus allowing counting under different conditions. Also the DISTINCT option for COUNT() is useful in this case.

这利用了在MySQL中进行比较的事实，例如s。goal_distance = 'SHORT将返回0为false, 1为true(或NULL)，因此允许在不同条件下计数。在这种情况下，COUNT()的独特选项也很有用。

I am surprised that you chose not to record each goal in a separate tuple. This would allow individual stats, such as the time of the goal, to be recorded without much additional workload to this query and would allow for much more flexible reporting.

我很惊讶你没有将每个目标记录在一个单独的元组中。这将允许记录单个统计数据(如目标的时间)，而无需为该查询增加太多工作量，并允许更灵活的报告。

#1