· 9 years ago · Oct 15, 2016, 07:40 AM
1USE master
2GO
3
4IF EXISTS (
5 SELECT name
6 FROM sys.databases
7 WHERE name = N'_Starkov' )
8ALTER DATABASE [_Starkov] set single_user with rollback immediate
9GO
10
11IF EXISTS (
12 SELECT name
13 FROM sys.databases
14 WHERE name = N'_Starkov' )
15DROP DATABASE [_Starkov]
16GO
17
18CREATE DATABASE [_Starkov]
19GO
20
21USE [_Starkov]
22GO
23
24IF OBJECT_ID('_Starkov.Meteo', 'U') IS NOT NULL
25 DROP TABLE _Starkov.Meteo
26GO
27
28CREATE TABLE Meteo (
29 id TINYINT,
30 Name nvarchar(20),
31 CONSTRAINT PK_m_id PRIMARY KEY (id)
32)
33GO
34
35IF OBJECT_ID('_Starkov.MeasureTypes', 'U') IS NOT NULL
36 DROP TABLE _Starkov.MeasureTypes
37GO
38
39CREATE TABLE MeasureTypes (
40 id TINYINT,
41 Name nvarchar(20),
42 Measure nvarchar(20),
43 CONSTRAINT PK_t_id PRIMARY KEY (id)
44)
45GO
46
47INSERT INTO Meteo (id, Name) VALUES
48(1, N'Ð¯Ð½Ð´ÐµÐºÑ ÐŸÐ¾Ð³Ð¾Ð´Ð°'),
49(2, N'Гугл Погода'),
50(3, N'Майл Погода')
51GO
52
53INSERT INTO MeasureTypes (id, Name, Measure) VALUES
54(1, N'ВлажноÑÑ‚ÑŒ', N'г/м3'),
55(2, N'Температура', N'градуÑов'),
56(3, N'Давление', N'Па')
57GO
58
59IF OBJECT_ID('_Starkov.Measures', 'U') IS NOT NULL
60 DROP TABLE _Starkov.Measures
61GO
62
63CREATE TABLE Measures (
64 Station_id tinyint NOT NULL,
65 Measure_id tinyint NOT NULL,
66 CatchTime DATE NOT NULL,
67 Measure_count tinyint NOT NULL,
68 Value float NOT NULL,
69 CONSTRAINT FK_m_id FOREIGN KEY (Station_id) REFERENCES Meteo(id) ON UPDATE CASCADE,
70 CONSTRAINT FK_t_id FOREIGN KEY (Measure_id) REFERENCES MeasureTypes(id) ON UPDATE CASCADE,
71)
72GO
73
74INSERT INTO Measures (Station_id, Measure_id, CatchTime, Measure_Count, Value) VALUES
75 (1, 1, '20160912', 1, 30),
76 (2, 1, '20160912', 1, 25),
77 (3, 1, '20160912', 1, 20),
78 (1, 2, '20160913', 2, 30),
79 (2, 2, '20160913', 2, 20),
80 (3, 2, '20160913', 2, 21),
81 (1, 3, '20160914', 3, 30),
82 (2, 3, '20160914', 3, 32),
83 (3, 3, '20160914', 3, 31.1),
84 (1, 3, '20160915', 4, 30.2),
85 (2, 2, '20160915', 4, 19),
86 (3, 1, '20160915', 4, 30),
87 (1, 1, '20160912', 1, 32),
88 (2, 1, '20160912', 1, 33),
89 (3, 1, '20160912', 1, 34),
90 (1, 2, '20160913', 2, 36),
91 (2, 2, '20160913', 2, 39),
92 (3, 2, '20160913', 2, 37),
93 (1, 3, '20160914', 3, 30.7),
94 (2, 3, '20160914', 3, 30.9),
95 (3, 3, '20160914', 3, 30.1),
96 (1, 3, '20160915', 4, 30.6),
97 (2, 2, '20160915', 4, 19),
98 (3, 1, '20160915', 4, 30)
99GO
100
101SELECT t.Name as "Тип измерениÑ",
102 s.Name as "МетеоÑтанциÑ",
103 CONVERT(varchar, m.CatchTime, 106) as "Ð’Ñ€ÐµÐ¼Ñ Ð¸Ð·Ð¼ÐµÑ€ÐµÐ½Ð¸Ñ",
104 ROUND(AVG(m.Value*m.Measure_count), 1) as "Среднее значение",
105 t.Measure as "Единицы"
106FROM Measures m
107INNER JOIN MeasureTypes t
108 ON t.id = m.Measure_id
109INNER JOIN Meteo s
110 ON s.id = m.Station_id
111GROUP BY m.CatchTime, t.Name, t.Measure, s.Name
112GO
113
114SELECT Value as "Температура"
115FROM Measures m
116WHERE m.Measure_id = 2
117GO
118
119SELECT ROUND(AVG(m.Value), 1) as "СреднÑÑ Ñ‚ÐµÐ¼Ð¿ÐµÑ€Ð°Ñ‚ÑƒÑ€Ð°"
120FROM Measures m
121WHERE m.Measure_id = 2
122GO