总结一个SQL数据库 [英] Summarize an SQL database

问题描述

我有一个数据库，我想在下面总结一下。



GAME_ID GOALIEID SAVE

2001 5 Y

2001 4 Y

2001 5 N

2001 4 Y

2001 5 N



基本上我试图通过game_id和goalieid来展示哪个守门员进行了最多的扑救以及守门员在那场比赛中有多少扑救。我只在这里放了一个游戏来使这个例子变得非常简单。



我想得到一个sql结果可以产生这个

< br $>
GAME_ID GOALIEID SAVES

2001 4 2



我的尝试： < br $>


SELECT GAME_ID，GOALIEID，COUNT（SAVE）

来自GIRAFFE.MLS

WHERE SAVE ='N'

GROUP BY GAME_ID

按次数排序（DIDSCORE）DESC



但是，这只是所有保存的总和而且并不特定于1守门员的数量。

解决方案

请注意，此解决方案适用于SQL服务器，因为我没有MySQL，但我希望解决方案是相似的。我不得不将SAVE字段的名称更改为SAVEFLAG，因为SAVE是SQL服务器中的保留字。

 create table #Temp（
 GAME_ID INT，
 GOALIEID INT，
 SAVEFLAG VARCHAR（1）
）
 
 INSERT INTO #Temp SELECT 2001,5，'Y'
 INSERT INTO #Temp SELECT 2001,4，'Y'
 INSERT INTO #Temp SELECT 2001,5，'N'
 INSERT INTO #Temp SELECT 2001,4，'Y'
 INSERT INTO #Temp SELECT 2001,5，'N'
 
  - 允许最多进球的守门员
 SELECT top 1 GAME_ID，GOALIEID，COUNT（SAVEFLAG）
 FROM #Temp 
 WHERE SAVEFLAG ='N'
 GROUP BY GAME_ID，GOALIEID 
 ORDER BY 3 DESC 
 
  - 守门员保存最多
 SELECT top 1 GAME_ID， GOALIEID，COUNT（SAVEFLAG）保存
来自#Temp 
 WHERE SAVEFLAG ='Y'
 GROUP BY GAME_ID，GOALIEID 
 ORDER BY 3 DESC 

I have a database that I am trying to summarize below.

GAME_ID GOALIEID SAVE
2001 5 Y
2001 4 Y
2001 5 N
2001 4 Y
2001 5 N

Essentially I am trying to show by game_id and goalieid in which game did which goalie make the most saves in and how many saves did the goalie have in that game. I only put one game here to make the example very simple.

I am trying to get an sql result that can produce this

GAME_ID GOALIEID SAVES
2001 4 2

What I have tried:

SELECT GAME_ID, GOALIEID, COUNT(SAVE)
FROM GIRAFFE.MLS
WHERE SAVE = 'N'
GROUP BY GAME_ID
ORDER BY COUNT (DIDSCORE) DESC

However, this is only totaling all saves and not specific to the amount the 1 goalie made.

解决方案

Please note, this solution is for SQL server, as I don't have MySQL, but I would expect the solutions to be similar. I had to change the name of the SAVE field to SAVEFLAG because SAVE is a reserved word in SQL server.

create table #Temp (
	GAME_ID INT,
	GOALIEID INT,
	SAVEFLAG VARCHAR(1)
	)

	INSERT INTO #Temp SELECT 2001, 5,'Y'
INSERT INTO #Temp SELECT 2001, 4,'Y'
INSERT INTO #Temp SELECT 2001, 5,'N'
INSERT INTO #Temp SELECT 2001, 4,'Y'
INSERT INTO #Temp SELECT 2001, 5,'N'

-- Goalie that allowed the most goals
SELECT top 1 GAME_ID, GOALIEID, COUNT(SAVEFLAG) 
FROM #Temp
WHERE SAVEFLAG = 'N'
GROUP BY GAME_ID, GOALIEID
ORDER BY 3 DESC

-- Goalie with the most saves
SELECT top 1 GAME_ID, GOALIEID, COUNT(SAVEFLAG) AS SAVES
FROM #Temp
WHERE SAVEFLAG = 'Y'
GROUP BY GAME_ID, GOALIEID
ORDER BY 3 DESC

这篇关于总结一个SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！