590
社区成员
发帖
与我相关
我的任务
分享视图查询多张表，其中一张表中work字段为1时，查询出时间字段中与work为1时间最近的work为0 的时间
表t1
id work date MId
1 1 2017-1 1
2 0 2017-2 1
3 0 2017-3 1
4 1 2017-7 2
5 0 2017-8 2
6 0 2017-9 2
表t2
id name
1 哈哈
2 嘻嘻
表t1与t2关联，查询出的记录为
id name wrok data Mid t1.id
1 哈哈 0 2017-2 1 2
2 嘻嘻 0 2017-8 2 5
id work date MId
1 1 2017-1 1
2 0 2017-2 1
3 0 2017-3 1
4 1 2017-7 2
5 0 2017-8 2
6 0 2017-9 2
表t2
id name
1 哈哈
2 嘻嘻
表t1与t2关联，查询出的记录为
id name wrok data Mid t1.id
1 哈哈 0 2017-2 1 2
2 嘻嘻 0 2017-8 2 5
...全文
请发表友善的回复…
发表回复
RINK_1 2017-09-22
- 打赏
- 举报
SELECT C.*,B.* FROM T1 A
CROSS APPLY (SELECT TOP 1 * FROM T1 WHERE A.MId=MId AND work=0 ORDER BY ABS(DATEDIFF(DD,A.[date],[DATE]))) AS B
JOIN T2 C ON A.MId=C.id
WHERE A.work =1
听雨停了 2017-09-22
- 打赏
- 举报
如果2017-2的work为1，其他为0 是不是就出现 两条记录为
id NAME WORK DATE
----------- ---------------------- ----------- --------------------
1 哈哈 0 2017-1
2 哈哈 0 2017-3
[/quote]
row_number和rank都是排名函数，它们的区别就在于像下面这样：
ROW_NUMBER() RANK()
分数 排名 分数 排名
100 1 100 1
100 2 100 1
99 3 99 3
rank区别就在当分数一样时排名也是一样的
听雨停了 2017-09-22
- 打赏
- 举报
如果2017-2的work为1，其他为0 是不是就出现 两条记录为
id NAME WORK DATE
----------- ---------------------- ----------- --------------------
1 哈哈 0 2017-1
2 哈哈 0 2017-3
[/quote]
如果你要实现这种效果的话，只需要把上面代码的row_number()改成rank()就可以了。像下面这样
WITH cte AS (
SELECT b.*,a.id AS mid,a.[work],a.[date] FROM #T1 a
INNER JOIN #t2 b ON a.MId=b.id
),
cte2 AS (
SELECT a.*,RANK() over(PARTITION BY a.id ORDER BY abs(CAST(REPLACE(b.[date],'-','') AS INT)-CAST(REPLACE(a.[date],'-','') AS INT))) AS num
FROM cte a
INNER JOIN cte b ON a.id=b.id
WHERE a.[work]='0' AND b.[work]='1'
)
SELECT id,NAME,WORK,[DATE] FROM cte2 WHERE num=1
id NAME WORK DATE
----------- ---------------------- ----------- --------------------
1 哈哈 0 2017-1
1 哈哈 0 2017-3
(2 行受影响)
i2u1314 2017-09-22
- 打赏
- 举报
如果2017-2的work为1，其他为0 是不是就出现 两条记录为
id NAME WORK DATE
----------- ---------------------- ----------- --------------------
1 哈哈 0 2017-1
2 哈哈 0 2017-3
听雨停了 2017-09-22
- 打赏
- 举报
date为varchar类型的写法
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([id] int,[work] int,[date] VARCHAR(20),[MId] int)
Insert #T1
select 1,1,N'2017-1',1 union all
select 2,0,N'2017-2',1 union all
select 3,0,N'2017-3',1 union all
select 4,1,N'2017-7',2 union all
select 5,0,N'2017-8',2 union all
select 6,0,N'2017-9',2
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([id] int,[name] nvarchar(22))
Insert #T2
select 1,N'哈哈' union all
select 2,N'嘻嘻'
Go
--测试数据结束
WITH cte AS (
SELECT b.*,a.id AS mid,a.[work],a.[date] FROM #T1 a
INNER JOIN #t2 b ON a.MId=b.id
),
cte2 AS (
SELECT a.*,row_number() over(PARTITION BY a.id ORDER BY abs(CAST(REPLACE(b.[date],'-','') AS INT)-CAST(REPLACE(a.[date],'-','') AS INT))) AS num
FROM cte a
INNER JOIN cte b ON a.id=b.id
WHERE a.[work]='0' AND b.[work]='1'
)
SELECT id,NAME,WORK,[DATE] FROM cte2 WHERE num=1
id NAME WORK DATE
----------- ---------------------- ----------- --------------------
1 哈哈 0 2017-2
2 嘻嘻 0 2017-8
(2 行受影响)
二月十六 2017-09-22
- 打赏
- 举报
date字段是date类型吗？按照date类型写的
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([id] int,[work] int,[date] DATE,[MId] int)
Insert #T1
select 1,1,N'2017-1-1',1 union all
select 2,0,N'2017-2-1',1 union all
select 3,0,N'2017-3-1',1 union all
select 4,1,N'2017-7-1',2 union all
select 5,0,N'2017-8-1',2 union all
select 6,0,N'2017-9-1',2
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([id] int,[name] nvarchar(22))
Insert #T2
select 1,N'哈哈' union all
select 2,N'嘻嘻'
Go
--测试数据结束
;WITH cte AS (
SELECT #T2.*,b.work,b.date,b.mid,b.id AS t1id,
ROW_NUMBER() OVER ( PARTITION BY #T2.id ORDER BY ABS(DATEDIFF(MONTH,
a.date, b.date)) ) AS num
FROM #T2
JOIN #T1 a ON a.Mid = #T2.id
AND a.work = 1
JOIN #T1 b ON b.MId = a.MId
AND b.work = 0
)
SELECT id ,
name ,
work ,
date ,
mid ,
t1id
FROM cte
WHERE num = 1
