27,580
社区成员
发帖
与我相关
我的任务
分享
1#,2#的测试有问题!第二行手机识别码及日期数据错了
create table #(主叫号码 bigint, 被叫号码 bigint, 类型 varchar(8), 手机识别码 varchar(8), 日期 datetime)
truncate table #
insert into #
select 13755190211, 13755190212, 'MOC', '111', '2010-3-1' union all
select 13755190211, 13755190212, 'MTC', '111', '2010-3-4' union all
select 13755190211, 13755190212, 'MOC', '222', '2010-3-3' union all
select 13755190212, 13755190211, 'MTC', '121', '2010-3-1' union all
select 13755190212, 13755190211, 'MOC', '122', '2010-3-3' union all
select 13755190212, 13755190211, 'MTCSMS', '', '2010-3-3'
GO
select 主叫号码,手机识别码,日期 from (
select *,id=ROW_NUMBER()over(partition by 主叫号码 order by 日期 desc)
from
(select 主叫号码=case when 类型='MOC' then 主叫号码 when 类型='MTC' then 被叫号码 end,
手机识别码,
日期
from # where 类型 in('MOC','MTC')
) t
) m where m.id=1
主叫号码 手机识别码 日期
------------ -------- -----------------------
13755190211 222 2010-03-03 00:00:00.000
13755190212 111 2010-03-04 00:00:00.000
(2 行受影响)
create table tb(主叫号码 varchar(20),被叫号码 varchar(20),类型 varchar(20),手机识别码 varchar(10),日期 datetime)
insert into tb values('13755190211', '13755190212', 'MOC', '111', '2010-3-1')
insert into tb values('13755190211', '13755190212', 'MTC', '111', '2010-3-4')
insert into tb values('13755190211', '13755190212', 'MOC', '222', '2010-3-3')
insert into tb values('13755190212', '13755190211', 'MTC', '121', '2010-3-1')
insert into tb values('13755190212', '13755190211', 'MOC', '122', '2010-3-3')
insert into tb values('13755190212', '13755190211', 'MTCSMS','' ,'2010-3-3')
go
select t.* from tb t where 类型 in ('MOC','MTC') and 日期 = (select max(日期) from tb where 类型 in ('MOC','MTC') and (主叫号码 = t.主叫号码 or 主叫号码 = t.被叫号码))
/*
主叫号码 被叫号码 类型 手机识别码 日期
-------------------- -------------------- -------------------- ---------- ------------------------------------------------------
13755190211 13755190212 MTC 111 2010-03-04 00:00:00.000
(所影响的行数为 1 行)
*/
select t.* from tb t where 类型 in ('MOC','MTC') and 日期 = (select max(日期) from tb where 类型 in ('MOC','MTC') and (主叫号码 = t.主叫号码 or 被叫号码 = t.被叫号码))
/*
主叫号码 被叫号码 类型 手机识别码 日期
-------------------- -------------------- -------------------- ---------- ------------------------------------------------------
13755190211 13755190212 MTC 111 2010-03-04 00:00:00.000
13755190212 13755190211 MOC 122 2010-03-03 00:00:00.000
(所影响的行数为 2 行)
*/
drop table tb
select t.* from tb t where 类型 in ('MOC','MTC') and 日期 = (select max(日期) from tb where 类型 in ('MOC','MTC') and (主叫号码 = t.主叫号码 or 主叫号码 = t.被叫号码))
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(主叫号码 bigint, 被叫号码 bigint, 类型 varchar(8), 手机识别码 varchar(8), 日期 datetime)
insert into #
select 13755190211, 13755190212, 'MOC', '111', '2010-3-1' union all
select 13755190211, 13755190212, 'MOC', '111', '2010-3-2' union all
select 13755190211, 13755190212, 'MOC', '222', '2010-3-3' union all
select 13755190212, 13755190211, 'MTC', '121', '2010-3-1' union all
select 13755190212, 13755190211, 'MTC', '122', '2010-3-3' union all
select 13755190212, 13755190211, 'MTCSMS', '', '2010-3-3'
;with cte as
(
select 主叫号码 phone, 日期 date, 手机识别码 mark from # where 类型='MOC'
union all
select 被叫号码 phone, 日期 date, 手机识别码 mark from # where 类型='MTC'
),
cte2 as
(
select row=row_number()over(partition by phone order by date), * from cte
)
select * from cte2 t where not exists (select 1 from cte2 where phone=t.phone and row>t.row)
/*
row phone date mark
-------------------- -------------------- ----------------------- --------
5 13755190211 2010-03-03 00:00:00.000 122
*/
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(主叫号码 bigint, 被叫号码 bigint, 类型 varchar(8), 手机识别码 varchar(8), 日期 datetime)
insert into #
select 13755190211, 13755190212, 'MOC', '111', '2010-3-1' union all
select 13755190211, 13755190212, 'MOC', '111', '2010-3-2' union all
select 13755190211, 13755190212, 'MOC', '222', '2010-3-3' union all
select 13755190212, 13755190211, 'MTC', '121', '2010-3-1' union all
select 13755190212, 13755190211, 'MTC', '122', '2010-3-3' union all
select 13755190212, 13755190211, 'MTCSMS', '', '2010-3-3'
;with cte as
(
select 主叫号码 phone, 日期 date, 手机识别码 mark from # where 类型='MOC'
union all
select 被叫号码 phone, 日期 date, 手机识别码 mark from # where 类型='MTC'
)
select * from cte t where not exists (select 1 from cte where phone=t.phone and date>t.date)
/*
phone date mark
-------------------- ----------------------- --------
13755190211 2010-03-03 00:00:00.000 222
13755190211 2010-03-03 00:00:00.000 122
*/