简单sql查询

A表对A表本身做关联处理。

select
ROW_NUMBER() over(partition by DPP_REGID order by CREATEDATE) odb1,
ROW_NUMBER() over(partition by DPP_REGID order by CREATEDATE desc) odb2,
t1.*
into #temp_01
from TEST_POINT t1
where exists
(
SELECT 1
FROM TEST_POINT t2
where t2.DPP_REGID = t1.DPP_REGID
group by t2.DPP_REGID
having COUNT(distinct t2.DPP_ISUSED) > 1
)

主要用到EXISTS关键字做处理。

--新建非聚集索引
CREATE NONCLUSTERED INDEX index_0420_0507 ON tmp_zjuser_0420_0507
(
DPO_DRE_ID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO