Denne tråd er over 6 måneder gammel
Er du sikker på, at du har noget relevant at tilføje?
Mqsql data requstAf Gæst jess | 23-07-2017 21:07 | 8669 visninger | 3 svar, hop til seneste
Hej
Jeg har en tabel som nedstående.
Jeg ønsker at få ud læst den højeste værdi i temp1, idag, hver måned samt hvert år, ligeledes at få tidspunktet med ud, kan simpel hen ikke løse den selv.
Time er unix timestamp
ID TIME temp1 temp2
1 22-07-2017 15:20 25.8 33
2 22-07-2017 15:30 26.9 22
3 22-07-2017 15:40 20.1 11
4 12-07-2017 15:00 30.2 33
5 12-07-2017 15:30 22.1 44
6 12-07-2017 15:40 25.3 55
--
Gæstebruger, opret dit eget login og få din egen signatur.
Jeg skal lige forstå dit spørgsmål korrekt:
Er det selve SQL queryet der driller?
--
Sidst redigeret 24-07-2017 09:07 Kan sikkert laves mere elegant, but here goes:
DROP TABLE #temp;
CREATE TABLE #temp
(
ID INT
,Time DATETIME
,temp1 DECIMAL(4, 2)
,temp2 DECIMAL(4, 2)
);
INSERT INTO #temp
(ID, Time, temp1, temp2)
VALUES (1, '2017-07-22 15:20:00', 25.8, 33),
(2, '2017-07-22 15:30:00', 26.9, 22),
(3, '2017-07-22 15:40:00', 20.1, 11),
(4, '2017-07-12 15:00:00', 30.2, 33),
(5, '2017-07-12 15:30:00', 22.1, 44),
(6, '2017-07-12 15:40:00', 25.3, 55),
(7, '2016-05-12 15:40:00', 12.3, 55),
(8, '2015-11-13 15:40:00', 22.3, 55);
--Højeste temp1 pr år
WITH Years
AS (SELECT DISTINCT
DATEPART(YEAR, Time) AS year
FROM #temp),
maxTemp
AS (SELECT MAX(temp1) maxTemp1
,DATEPART(YEAR, Time) AS MaxYear
FROM #temp
INNER JOIN Years ON year = DATEPART(YEAR, Time)
GROUP BY DATEPART(YEAR, Time))
SELECT #temp.*
FROM #temp
INNER JOIN maxTemp ON maxTemp.maxTemp1 = temp1
AND maxTemp.MaxYear = DATEPART(YEAR, Time);
--Højeste temp1 pr måned
WITH monthsYears
AS (SELECT DISTINCT
DATEPART(MONTH, Time) AS month
,DATEPART(YEAR, Time) AS year
FROM #temp),
maxTemp
AS (SELECT MAX(temp1) maxTemp1
,DATEPART(MONTH, Time) AS MaxMonth
,DATEPART(YEAR, Time) AS MaxYear
FROM #temp
INNER JOIN monthsYears ON month = DATEPART(MONTH, Time)
AND year = DATEPART(YEAR, Time)
GROUP BY DATEPART(MONTH, Time)
,DATEPART(YEAR, Time))
SELECT #temp.*
FROM #temp
INNER JOIN maxTemp ON maxTemp.maxTemp1 = temp1
AND maxTemp.MaxMonth = DATEPART(MONTH, Time)
AND maxTemp.MaxYear = DATEPART(YEAR, Time);
--Højeste temp1 pr dato
WITH dates
AS (SELECT DISTINCT
CAST(Time AS DATE) AS date
FROM #temp),
maxTemp
AS (SELECT MAX(temp1) maxTemp1
,CAST(Time AS DATE) maxDate
FROM #temp
INNER JOIN dates ON date = CAST(Time AS DATE)
GROUP BY CAST(Time AS DATE))
SELECT #temp.*
FROM #temp
INNER JOIN maxTemp ON maxTemp.maxTemp1 = temp1
AND maxTemp.maxDate = CAST(Time AS DATE);
--
Som sagt kunne det gøres mere elegant:
DROP TABLE #temp;
CREATE TABLE #temp
(
ID INT
,Time DATETIME
,temp1 DECIMAL(4, 2)
,temp2 DECIMAL(4, 2)
);
INSERT INTO #temp
(ID, Time, temp1, temp2)
VALUES (1, '2017-07-22 15:20:00', 25.8, 33),
(2, '2017-07-22 15:30:00', 26.9, 22),
(3, '2017-07-22 15:40:00', 20.1, 11),
(4, '2017-07-12 15:00:00', 30.2, 33),
(5, '2017-07-12 15:30:00', 22.1, 44),
(6, '2017-07-12 15:40:00', 25.3, 55),
(7, '2016-05-12 15:40:00', 12.3, 55),
(8, '2015-11-13 15:40:00', 22.3, 55),
(9, '2017-06-12 15:40:00', 25.3, 55);
--Højeste temp1 pr år
SELECT 'År';
WITH maxYearTemp
AS (SELECT DATEPART(YEAR, Time) MaxYear
,MAX(temp1) MaxTemp
FROM #temp
GROUP BY DATEPART(YEAR, Time))
SELECT #temp.*
FROM #temp
INNER JOIN maxYearTemp ON MaxTemp = temp1
AND DATEPART(YEAR, Time) = MaxYear;
--Højeste temp1 pr måned
SELECT 'Måned';
WITH maxYearMonthTemp
AS (SELECT DATEPART(YEAR, Time) MaxYear
,DATEPART(MONTH, Time) AS Maxmonth
,MAX(temp1) MaxTemp
FROM #temp
GROUP BY DATEPART(YEAR, Time)
,DATEPART(MONTH, Time))
SELECT #temp.*
FROM #temp
INNER JOIN maxYearMonthTemp ON MaxTemp = temp1
AND DATEPART(YEAR, Time) = MaxYear
AND DATEPART(MONTH, Time) = Maxmonth;
--Højeste temp1 pr dato
SELECT 'Dato';
WITH maxDateTemp
AS (SELECT CAST(Time AS DATE) AS MaxDate
,MAX(temp1) MaxTemp
FROM #temp
GROUP BY CAST(Time AS DATE))
SELECT #temp.*
FROM #temp
INNER JOIN maxDateTemp ON MaxTemp = temp1
AND CAST(Time AS DATE) = MaxDate;
--
Grundet øget spam aktivitet fra gæstebrugere, er det desværre ikke længere muligt, at oprette svar som gæst.
Hvis du ønsker at deltage i debatten, skal du oprette en brugerprofil.
Opret bruger | Login
|
Du skal være logget ind for at tilmelde dig nyhedsbrev.
Hvilken udbyder har du til internet? 237 personer har stemt - Mit energiselskab (Ewii f.eks) 11%
|