统计合并来自3个表的数据的报告-连接或子查询

Question

我有三个表:tbl_Player, tbl_MatchDetails, tbl_MatchStat和一些数据:

tbl_Player

CREATE TABLE [dbo].[tbl_Player](
    [PlayerID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
 CONSTRAINT [PK_tbl_Player] PRIMARY KEY CLUSTERED 
(
    [PlayerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tbl_Player] ON
INSERT [dbo].[tbl_Player] ([PlayerID], [Name]) VALUES (1, N'L Messi')
INSERT [dbo].[tbl_Player] ([PlayerID], [Name]) VALUES (2, N'C Ronaldo')
INSERT [dbo].[tbl_Player] ([PlayerID], [Name]) VALUES (3, N'Neymar')
INSERT [dbo].[tbl_Player] ([PlayerID], [Name]) VALUES (4, N'Rooney')
INSERT [dbo].[tbl_Player] ([PlayerID], [Name]) VALUES (5, N'K Aguero')
INSERT [dbo].[tbl_Player] ([PlayerID], [Name]) VALUES (6, N'Gomez')
SET IDENTITY_INSERT [dbo].[tbl_Player] OFF

tbl_MatchDetails

CREATE TABLE [dbo].[tbl_MatchDetails](
    [MatchID] [int] IDENTITY(1,1) NOT NULL,
    [Tournament] [varchar](50) NULL,
    [TeamA] [varchar](50) NULL,
    [TeanB] [varchar](50) NULL,
 CONSTRAINT [PK_tbl_MatchDetails] PRIMARY KEY CLUSTERED 
(
    [MatchID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tbl_MatchDetails] ON
INSERT [dbo].[tbl_MatchDetails] ([MatchID], [Tournament], [TeamA], [TeanB]) VALUES (1, N'CL', N'Real', N'Barca')
INSERT [dbo].[tbl_MatchDetails] ([MatchID], [Tournament], [TeamA], [TeanB]) VALUES (2, N'CL', N'Barca', N'United')
INSERT [dbo].[tbl_MatchDetails] ([MatchID], [Tournament], [TeamA], [TeanB]) VALUES (3, N'League', N'Barca', N'Real')
INSERT [dbo].[tbl_MatchDetails] ([MatchID], [Tournament], [TeamA], [TeanB]) VALUES (4, N'CL', N'M City', N'Bayern')
INSERT [dbo].[tbl_MatchDetails] ([MatchID], [Tournament], [TeamA], [TeanB]) VALUES (5, N'League', N'Barca', N'Valencia')
INSERT [dbo].[tbl_MatchDetails] ([MatchID], [Tournament], [TeamA], [TeanB]) VALUES (6, N'League', N'Real', N'Betis')
SET IDENTITY_INSERT [dbo].[tbl_MatchDetails] OFF

tbl_MatchStat

CREATE TABLE [dbo].[tbl_MatchStat](
    [StatID] [int] IDENTITY(1,1) NOT NULL,
    [MatchID] [int] NULL,
    [PlayerID] [int] NULL,
    [Goals] [int] NULL,
    [Minutes] [int] NULL,
 CONSTRAINT [PK_tbl_MatchStat] PRIMARY KEY CLUSTERED 
(
    [StatID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tbl_MatchStat] ON
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (1, 1, 1, 3, 92)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (2, 1, 2, 1, 92)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (3, 2, 1, 2, 85)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (4, 2, 4, 2, 93)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (5, 3, 1, 1, 35)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (6, 3, 2, 0, 90)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (7, 4, 6, 2, 93)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (8, 4, 5, 0, 15)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (9, 5, 3, 2, 25)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (10, 5, 1, 4, 91)
INSERT [dbo].[tbl_MatchStat] ([StatID], [MatchID], [PlayerID], [Goals], [Minutes]) VALUES (11, 6, 2, 3, 88)
SET IDENTITY_INSERT [dbo].[tbl_MatchStat] OFF

从这些数据中，我想有一个select语句，根据锦标赛给出每个球员的摘要。例如，根据锦标赛'CL'选择数据，查询结果可能看起来像

<>之前比赛总进球最高得分分钟帽子戏法L梅西2 5 3 117 1C罗纳尔多1 1 1 92 0鲁尼1 2 2 93 0阿圭罗1 0 0 15 0戈麦斯1 2 2 93 0

Answer 1

Try

SELECT p.Name, 
       COUNT(m.MatchID) MatchPlayed, 
       SUM(s.Goals) TotalGoals,
       MAX(s.Goals) HighestScore,
       SUM(s.Minutes) Minutes,
       SUM(CASE WHEN s.Goals >= 3 THEN 1 ELSE 0 END) Hattrick
  FROM tbl_MatchDetails m JOIN tbl_MatchStat s
    ON m.MatchID = s.MatchID JOIN tbl_Player p
    ON s.PlayerID = p.PlayerID
 WHERE m.Tournament = 'CL'
 GROUP BY p.Name

输出:

<>之前姓名|出场次数|总进球数|最高得分|出场时间|帽子戏法----------------------------------------------------------------------------C罗| 1 | 1 | 1 | 92 | 0 |戈麦斯| 1 | 2 | 2 | 93 | 0 |阿圭罗| 1 | 0 | 0 | 15 | 0 |梅西| 2 | 5 | 3 | 177 | 1 |鲁尼| 1 | 2 | 2 | 93 | 0 |之前

这里是SQLFiddle demo