查找最近的日期
问题描述:
记录这是我的原始数据(匿名):查找最近的日期
id usage verified date
1 4000 Y 2015-03-20
2 5000 N 2015-06-20
3 6000 N 2015-07-20
4 7000 Y 2016-09-20
原始查询:
SELECT
me.usage,
mes.verified,
mes.date
FROM
Table1 me,
Table2 mes,
Table3 m,
Table4 mp
WHERE
me.theFk=mes.id
AND mes.theFk=m.id
AND m.theFk=mp.id
我怎么会去选择最近的验证和非验证?
所以我会留下:
id usage verified date
1 6000 N 2015-07-20
2 7000 Y 2016-09-20
我使用Microsoft SQL Server 2012
答
首先,不要使用隐式连接。这已经在10多年前停止了。
其次,拥抱CTE的力量,在条款和ROW_NUMBER:
with CTE as
(
select
me.usage,
mes.verified,
mes.date,
row_number() over (partition by Verified order by Date desc) as CTEOrd
from Table1 me
inner join Table2 mes
on me.theFK = mes.id
where mes.theFK in
(
select m.id
from Table3 m
inner join Table4 mp
on mp.id = m.theFK
)
)
select CTE.*
from CTE
where CTEOrd = 1
答
您可以选择按日期为验证= N下令TOP 1,与TOP 1 union'd按日期排序验证= Y。
或者伪SQL:
SELECT TOP 1 ...fields ...
FROM ...tables/joins...
WHERE Verified = 'N'
ORDER BY Date DESC
UNION
SELECT TOP 1 ...fields ...
FROM ...tables/joins...
WHERE Verified = 'Y'
ORDER BY Date DESC
答
drop table #stack2
CREATE TABLE #stack2
([id] int, [usage] int, [verified] varchar(1), [date] datetime)
;
INSERT INTO #stack2
([id], [usage], [verified], [date])
VALUES
(1, 4000, 'Y', '2015-03-20 00:00:00'),
(2, 5000, 'N', '2015-06-20 00:00:00'),
(3, 6000, 'N', '2015-07-20 00:00:00'),
(4, 7000, 'Y', '2016-09-20 00:00:00')
;
;with cte as (select verified,max(date) d from #stack2 group by verified)
select row_number() over(order by s2.[verified]),s2.[usage], s2.[verified], s2.[date] from #stack2 s2 join cte c on c.verified=s2.verified and c.d=s2.date
按显示我写的查询的数据。
您的方案,这将是采用全
WITH cte1
AS (SELECT me.usage,
mes.verified,
mes.date
FROM Table1 me,
Table2 mes,
Table3 m,
Table4 mp
WHERE me.theFk = mes.id
AND mes.theFk = m.id
AND m.theFk = mp.id),
cte
AS (SELECT verified,
Max(date) d
FROM cte1
GROUP BY verified)
SELECT Row_number()
OVER(
ORDER BY s2.[verified]),
s2.[usage],
s2.[verified],
s2.[date]
FROM cte1 s2
JOIN cte c
ON c.verified = s2.verified
AND c.d = s2.date
答
您可以按以下Without join
。
-- Mock data
DECLARE @Tbl TABLE (id INT, usage INT, verified CHAR(1), date DATETIME)
INSERT INTO @Tbl
VALUES
(1, 4000 ,'Y', '2015-03-20'),
(2, 5000 ,'N', '2015-06-20'),
(3, 6000 ,'N', '2015-07-20'),
(4, 7000 ,'Y', '2016-09-20')
SELECT
A.id ,
A.usage ,
A.verified ,
A.MaxDate
FROM
(
SELECT
id ,
usage ,
verified ,
date,
MAX(date) OVER (PARTITION BY verified) MaxDate
FROM
@Tbl
) A
WHERE
A.date = A.MaxDate
结果:
id usage verified MaxDate
----------- ----------- -------- ----------
3 6000 N 2015-07-20
4 7000 Y 2016-09-20
答
CREATE TABLE #Table (ID INT ,usage INT, verified VARCHAR(10), _date DATE)
INSERT INTO #Table (ID , usage , verified , _date)
SELECT 1,4000 , 'Y','2015-03-20' UNION ALL
SELECT 2, 5000 , 'N' ,'2015-06-20' UNION ALL
SELECT 3, 6000 , 'N' ,'2015-07-20' UNION ALL
SELECT 4, 7000 , 'Y' ,'2016-09-20'
SELECT ROW_NUMBER() OVER(ORDER BY usage) ID,usage , A.verified , A._date
FROM #Table
JOIN
(
SELECT verified , MAX(_date) _date
FROM #Table
GROUP BY verified
) A ON #Table._date = A._date
标记您的DBMS,请 – JohnHC