问题:
I have the following query that throws a result like in the example:
SELECT P.IdArt, P.IdAdr, P.gDate, P.Price
FROM dbo.T_PriceData AS P INNER JOI...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
I have the following query that throws a result like in the example:
SELECT P.IdArt, P.IdAdr, P.gDate, P.Price
FROM dbo.T_PriceData AS P INNER JOIN
dbo.T_Adr AS A ON P.IdAdr = A.IdAdr INNER JOIN
dbo.T_Stat AS S ON A.IdStat = S.IdStat
GROUP BY P.IdArt, P.IdAdr, P.gDate, P.Price
IdArt IdAdr gDate Price
1 10 01/01/2018 1.25
1 10 02/01/2018 1.17
1 10 03/01/2018 1.18
2 15 01/01/2018 1.03
2 18 10/01/2018 0.12
3 25 12/01/2018 0.98
3 25 28/01/2018 1.99
4 30 15/01/2018 2.55
5 35 08/01/2018 0.11
The final result I want is:
- When the IdArt and IdAdr are the same, there should be only one row with the highest date of all rows (CASE IdArt 1)
- When IdArt is the same but IdAdr is different, there should be a row with each IdAdr with the highest date for each IdAdr. (CASE IdArt 2)
- Price doens't affect anything.
So the final table I would like to have is:
IdArt IdAdr gDate Price
1 10 03/01/2018 1.18
2 15 01/01/2018 1.03
2 18 10/01/2018 0.12
3 25 28/01/2018 1.99
4 30 15/01/2018 2.55
5 35 08/01/2018 0.11
How can I do that? I tried with a having clausule selecting by MAX(gDate) but, of course, I only get one row with the max date from the whole database.
回答1:
There are lots of answers out there on how to do this, however, this gets you what you are after:
SELECT TOP 1 WITH TIES
P.IdArt,
P.IdAdr,
P.gDate,
P.Price
FROM dbo.T_PriceData P
--INNER JOIN dbo.T_Adr A ON P.IdAdr = A.IdAdr --You don't reference this in the SELECT or WHERE. Why is it here?
--INNER JOIN dbo.T_Stat S ON A.IdStat = S.IdStat --You don't reference this in the SELECT or WHERE. Why is it here?
ORDER BY ROW_NUMBER() OVER (PARTITION BY P.IdArt, P.IdAdr ORDER BY P.gDate DESC);
Edit: If the JOIN
s are there to ensure that there are rows in the other tables, then as per the comments I would use EXISTS
. If you just use JOIN
, and only returning rows from the first table, then you could end up with duplicate rows.
SELECT TOP 1 WITH TIES
P.IdArt,
P.IdAdr,
P.gDate,
P.Price
FROM dbo.T_PriceData P
WHERE EXISTS (SELECT 1
FROM dbo.T_Adr A
WHERE P.IdAdr = A.IdAdr)
AND EXISTS (SELECT 1
FROM dbo.T_Stat S
WHERE A.IdStat = S.IdStat)
ORDER BY ROW_NUMBER() OVER (PARTITION BY P.IdArt, P.IdAdr ORDER BY P.gDate DESC);
回答2:
You want the highest date for each IdArt
/IdAdr
combination. Window functions are tempting, but the most efficient method is often a correlated subquery.
Your query is only selecting from T_PriceData
, so the rest of the query (the joins and group by
) do not seem necessary -- unless the joins are filtering the data which seems unlikely because the joins are to reference tables.
So I would recommend:
SELECT P.IdArt, P.IdAdr, P.gDate, P.Price
FROM dbo.T_PriceData P
WHERE P.gDate = (SELECT MAX(P2.gDate)
FROM dbo.T_PriceData P2
WHERE P2.IdArt = P.IdArt AND
P2.IAdr = P.IdAdr
);
For performance you want indexes on (IdArt, IdAdr, gDate)
.
回答3:
You can use ROW_Number()
:
SELECT
q.IdArt
, q.IdArt
, q.IdADr
, q.gDate
, q.Price
FROM (
SELECT
t.IdArt
, t.IdADr
, t.gDate
, t.Price
, ROW_NUMBER() OVER (PARTITION BY t.IdArt, t.IdADr ORDER BY t.gDate DESC) rn
FROM dbo.T_PriceData t
) q
WHERE q.rn = 1