6ibQKKX5

1USE master
2GO 
3
4IF  EXISTS (
5	SELECT name 
6		FROM sys.databases 
7		WHERE name = N'#Shchukina'
8)
9ALTER DATABASE #Valitov set single_user with rollback immediate
10GO
11
12IF  EXISTS (
13	SELECT name 
14		FROM sys.databases 
15		WHERE name = N'#Shchukina'
16)
17DROP DATABASE #Valitov
18GO
19
20CREATE DATABASE #Valitov
21GO
22
23USE #Valitov
24GO
25
26CREATE SCHEMA Football 
27GO
28
29CREATE TABLE Football.Teams(
30	TeamName nvarchar(20) PRIMARY KEY
31)
32GO
33
34CREATE TABLE Football.Game(
35	GameId int IDENTITY(1,1) PRIMARY KEY,
36	FirstCommand nvarchar(20) FOREIGN KEY REFERENCES Football.Teams(TeamName),
37	SecondCommand nvarchar(20) FOREIGN KEY REFERENCES Football.Teams(TeamName),
38	GameDate datetime
39)
40GO
41
42CREATE TABLE Football.Goals(
43	GoalId int IDENTITY(1,1) PRIMARY KEY,
44	FK_Team_Scored nvarchar(20) FOREIGN KEY REFERENCES Football.Teams(TeamName),
45	Forward nvarchar(30),
46	FK_Team_Conceded nvarchar(20) FOREIGN KEY REFERENCES Football.Teams(TeamName),
47	GoalKeeper nvarchar(30),
48	FK_Game int FOREIGN KEY REFERENCES Football.Game(GameId)
49)
50GO
51
52Insert Into Football.Teams values
53(N'Спартак'),(N'Урал'),(N'Рубин')
54GO
55
56INSERT INTO Football.Game values
57(N'Спартак',N'Урал','20170501'), --> 1
58(N'Урал',N'Рубин','20170520'),--> 2
59(N'Спартак',N'Рубин','20170604')--> 3
60GO
61
62INSERT INTO Football.Goals values
63(N'Спартак',N'Иванов Иван', N'Урал',N'Гребнев Павел',1),
64(N'Урал', N'Левин Семён',N'Спартак',N'Злобин Валерий',1),
65(N'Урал', N'Петров Иван',N'Спартак',N'Злобин Валерий',1),
66(N'Спартак', N'Иванов Иван', N'Урал',N'Гребнев Павел',1),
67(N'Спартак', N'Семенов Игорь', N'Урал',N'Гребнев Павел',1),
68(N'Рубин',N'Петров Пётр',N'Урал', N'Гребнев Павел',2),
69(N'Урал', N'Левин Семён',N'Рубин', N'Васильев Рома',2),
70
71(N'Спартак',N'Иванов Иван',N'Рубин',N'Васильев Рома',3),
72(N'Рубин',N'Петров Пётр',N'Спартак',N'Злобин Валерий',3),
73(N'Спартак',N'Семенов Игорь',N'Рубин',N'Васильев Рома',3)
74GO
75
76CREATE FUNCTION Football.GetRatingTable(@date datetime)
77RETURNS @tabl TABLE (teamName nvarchar(20), points int, scored int, conceded int)
78AS
79BEGIN
80	declare @games TABLE (idGame int)
81	declare @gameStat TABLE (teamName nvarchar(20),goals int)
82	insert into @games
83				Select GameId From Football.Game Where GameDate <= @date
84
85	DECLARE @game int = 0
86	WHILE(1 = 1)
87	BEGIN
88		SET  @game = (SELECT MIN(idGame)
89			FROM  @games WHERE idGame > @game)
90		IF @game IS NULL BREAK
91		BEGIN
92			DELETE FROM @gameStat
93			insert into @gameStat SELECT FK_Team_Scored, COUNT(FK_Team_Scored) 
94				FROM Football.Goals  WHERE FK_Game = @game 
95				GROUP BY FK_Team_Scored
96		
97			DECLARE @team1 nvarchar(20) = (SELECT TOP(1)  teamName From @gameStat)
98			DECLARE @team2 nvarchar(20) = (SELECT teamName From @gameStat WHERE teamName != @team1)
99			DECLARE @goal1 int = (SELECT goals FROM @gameStat WHERE teamName = @team1)
100			DECLARE @goal2 int = (SELECT goals FROM @gameStat WHERE teamName = @team2)
101	
102		
103
104		IF @goal1 > @goal2  --Победила команда в топ(1)	
105			BEGIN
106				--Добавляем первую команду
107				IF NOT EXISTS(Select * from @tabl where teamName = @team1) 
108					insert into @tabl values(@team1,3, @goal1, @goal2)
109				ELSE 
110					update @tabl Set points = points+3,
111					scored = scored + @goal1,
112					conceded = conceded+@goal2 Where teamName = @team1
113				--Добавляем вторую команду
114				IF NOT EXISTS(Select * from @tabl where teamName = @team2) 
115					insert into @tabl values(@team2,0, @goal2, @goal1)
116				ELSE 
117					update @tabl Set points = points,
118					scored =  scored + @goal2,
119					conceded = conceded + @goal1 Where teamName = @team2
120			END
121		ELSE IF @goal1 < @goal2  --Победила команда в топ(2)	
122			BEGIN
123				--Добавляем первую команду
124				IF NOT EXISTS(Select * from @tabl where teamName = @team2) 
125					insert into @tabl values(@team2,3, @goal2, @goal1)
126				ELSE 
127					update @tabl Set points = points+3,
128					scored = scored + @goal2,
129					conceded = conceded+@goal1 Where teamName = @team2
130				--Добавляем вторую команду
131				IF NOT EXISTS(Select * from @tabl where teamName = @team1) 
132					insert into @tabl values(@team1,0, @goal1, @goal2)
133				ELSE 
134					update @tabl Set points = points,
135					scored =  scored + @goal1,
136					conceded = conceded + @goal2 Where teamName = @team1
137			END
138		ELSE -- ЕСЛИ НИЧЬЯ
139			BEGIN
140			--Добавляем первую команду
141				IF  NOT EXISTS (Select * from @tabl where teamName = @team1)
142					insert into @tabl values(@team1,1, @goal1, @goal2)
143				ELSE 
144					update @tabl Set points = points+1,
145					scored = scored + @goal1,
146					conceded = conceded + @goal2 Where teamName = @team1
147				--Добавляем вторую команду
148				IF  NOT EXISTS(Select * from @tabl where teamName = @team2) 
149					insert into @tabl values(@team2,1, @goal2, @goal1)
150				ELSE 
151					update @tabl Set points = points+1,
152					scored = scored + @goal2, 
153					conceded = conceded + @goal1 Where teamName = @team2
154			END
155		END
156	END
157	RETURN
158End
159GO
160
161
162
163
164
165
166---------------------GET RATING TABLE
167Create VIEW Football.Raiting
168AS
169SELECT row_number() OVER(ORDER BY points desc, scored Desc, scored-conceded) as 'Место',
170	teamName as 'Команда',
171	points as 'Очки',
172	scored as 'Забили',
173	conceded as 'Пропустили'
174	FROM Football.GetRatingTable('20170801')
175GO
176
177SELECT * FROM Football.Raiting
178GO
179----------------------------END GET RATING
180
181---------------------------
182CREATE VIEW Football.Forwards
183AS
184SELECT  Forward, FK_Team_Scored, Count(Forward) as point FROM Football.Goals
185Group By Forward,FK_Team_Scored
186Order By Count(Forward) desc OFFSET 0 ROWS
187GO
188
189SELECT * FROM Football.Forwards 
190GO
191
192CREATE VIEW Football.GoalKeepers
193AS
194SELECT  GoalKeeper, FK_Team_Conceded, Count(GoalKeeper) as point FROM Football.Goals
195Group By GoalKeeper,FK_Team_Conceded
196Order By Count(GoalKeeper) OFFSET 0 ROWS
197GO
198
199SELECT * FROM Football.GoalKeepers 
200GO
201
202
203
204DROP VIEW Football.Forwards, Football.Raiting, Football.GoalKeepers
205GO
206
207
208
209
210
211CREATE TABLE #res (" " nvarchar(20),team nvarchar(20), score varchar(40));
212insert into #res
213SELECT FK_Team_Scored,
214		FK_Team_Conceded,
215		Concat((SELECT Count(FK_Team_Scored) FROM Football.Goals WHERE FK_Game = GameID 
216				and Football.Goals.FK_Team_Scored = Football.Game.FirstCommand),':',
217				(SELECT Count(FK_Team_Scored) FROM Football.Goals WHERE FK_Game = GameID 
218				and Football.Goals.FK_Team_Scored = Football.Game.SecondCommand))
219FROM Football.Game
220Inner Join  Football.Goals ON GameId = FK_Game
221WHERE FK_Team_Scored = FirstCommand and FK_Team_Conceded = SecondCommand
222GROUP BY FK_Team_Scored, FK_Team_Conceded,GameId, FirstCommand, SecondCommand
223Union
224SELECT FK_Team_Scored, FK_Team_Conceded,Concat((SELECT Count(FK_Team_Scored) FROM Football.Goals WHERE FK_Game = GameID 
225				and Football.Goals.FK_Team_Scored = Football.Game.SecondCommand),':',
226				(SELECT Count(FK_Team_Scored) FROM Football.Goals WHERE FK_Game = GameID 
227				and Football.Goals.FK_Team_Scored = Football.Game.FirstCommand))
228FROM Football.Game
229Inner Join  Football.Goals ON GameId = FK_Game
230WHERE FK_Team_Conceded = FirstCommand and FK_Team_Scored = SecondCommand
231GROUP BY FK_Team_Scored, FK_Team_Conceded, FirstCommand, SecondCommand, GameId
232
233SELECT * FROM  #res
234
235declare @cols NVARCHAR(MAX) = (SELECT substring(
236		(
237			Select ','+ TeamName  AS [text()]
238			From Football.Teams 
239			For XML PATH ('')
240		), 2, 1000) [Teams])
241
242declare @query nvarchar(MAX) = N'SELECT * FROM #res PIVOT(
243max(score)
244For team in('+@cols+N')
245			) p '
246exec sp_executesql @query
247
248DROP TABLE #res