34,838
社区成员
发帖
与我相关
我的任务
分享SQL查询很多字段，按照某一个字段去重复(100分送上)
现在从Ultiums的表里面查询一些流程数据，现在只要筛选不同的步骤。所以只需要筛选STEPID不同的项目，
表的结构如下
尝试过
WHERE EXISTS
的方法 不过效率比较低，大概存储10w的数据 又没有一些效率的方法呢,.?不能修改表结构,不能加索引
表的结构如下
尝试过
WHERE EXISTS
的方法 不过效率比较低，大概存储10w的数据 又没有一些效率的方法呢,.?不能修改表结构,不能加索引
...全文
请发表友善的回复…
发表回复
ZMAK_Chun 2012-12-14
- 打赏
- 举报
奥奥- -我理解错误了.....
就是给出的数据都要查询出来，但是StepId相同的只需要一条就够了
以学习为目的 2012-12-14
- 打赏
- 举报
select * from tb where not exists(select 1 from tb a where a.col=b.col)
开着拖拉机泡妞 2012-12-14
- 打赏
- 举报
我是说 重复了的[STEPID] 你要取出哪些数据啊？？？你怎么不肯给出你的结果 你楼上说重复 也没说咯处理方法
ZMAK_Chun 2012-12-14
- 打赏
- 举报
类似我刚才贴的结果吧。。
一个流程(processName)有N个步骤(STEPID)存在这张表里面。
大概类似这样的数据有20W左右
ZMAK_Chun 2012-12-14
- 打赏
- 举报
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[STEPID] nvarchar(64),[PROCESSNAME] nvarchar(256),[STEPLABEL] nvarchar(256),[INCIDENT] nvarchar(64))
Insert #T
select 1,N'011656',N'流程1',N'步骤A',N'实例1' union all
select 2,N'011656',N'流程1',N'步骤A',N'实例2' union all
select 3,N'011656',N'流程1',N'步骤A',N'实例3' union all
select 4,N'011656',N'流程1',N'步骤A',N'实例4' union all
select 5,N'011657',N'流程2',N'步骤B',N'实例1' union all
select 6,N'011657',N'流程2',N'步骤B',N'实例2' union all
select 7,N'011657',N'流程2',N'步骤B',N'实例3' union all
select 8,N'011657',N'流程2',N'步骤B',N'实例4' union all
select 9,N'011657',N'流程2',N'步骤B',N'实例5'
Go
我腫了 2012-12-14
- 打赏
- 举报
select * from Ultiums As a
Where Not exists(select 1 from Ultiums As x
Where x.STEPID=a.STEPID And x.STARTTIME>a.STARTTIME
)
黄_瓜 2012-12-14
- 打赏
- 举报
联合索引建的太多了。
开着拖拉机泡妞 2012-12-14
- 打赏
- 举报
你贴出你的测试数据 不要图片的 然后给出你预期的结果
ZMAK_Chun 2012-12-14
- 打赏
- 举报
我正在看 谢谢斑竹大人吖~~
是更具STEPID去掉重复，但是上面表中的其他字段都是要查询出来的
有几条ULTIMUS自带的索引
/****** 对象: Index [INCCON3] 脚本日期: 12/14/2012 09:52:27 ******/
ALTER TABLE [dbo].[TASKS] ADD CONSTRAINT [INCCON3] PRIMARY KEY CLUSTERED
(
[TASKID] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
/****** 对象: Index [TASKS01] 脚本日期: 12/14/2012 09:52:48 ******/
CREATE NONCLUSTERED INDEX [TASKS01] ON [dbo].[TASKS]
(
[PROCESSNAME] ASC,
[PROCESSVERSION] ASC,
[INCIDENT] ASC,
[STEPID] ASC,
[TASKID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
/****** 对象: Index [TASKS02] 脚本日期: 12/14/2012 09:53:05 ******/
CREATE NONCLUSTERED INDEX [TASKS02] ON [dbo].[TASKS]
(
[PROCESSNAME] ASC,
[INCIDENT] ASC,
[STEPID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [TASKS03] ON [dbo].[TASKS]
(
[STATUS] ASC,
[SUBSTATUS] ASC,
[REFERER] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [TASKS04] ON [dbo].[TASKS]
(
[RECIPIENTTYPE] ASC,
[STATUS] ASC,
[SUBSTATUS] ASC,
[OVERDUETIME] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [TASKS05] ON [dbo].[TASKS]
(
[INCIDENT] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
zhengnan2012 2012-12-14
- 打赏
- 举报
要是没有索引本来效率就不高哦。
select distinct stepid,processname from tb a
开着拖拉机泡妞 2012-12-14
- 打赏
- 举报
with t
as(
select px=row_number()over(partition by STEPID order by (select 1))
,* from tb
)
select * from t where px=1
--不确定你要筛选的结果
开着拖拉机泡妞 2012-12-14
- 打赏
- 举报
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go
--I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时，效率高于1、2
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)
方法2:
select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID
方法3:
select * from #T a where ID=(select min(ID) from #T where Name=a.Name)
方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1
方法5:
select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)
方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0
方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)
方法8:
select * from #T a where ID!>all(select ID from #T where Name=a.Name)
方法9(注:ID为唯一时可用):
select * from #T a where ID in(select min(ID) from #T group by Name)
--SQL2005:
方法10:
select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID
方法11:
select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1
生成结果:
/*
ID Name Memo
----------- ---- ----
1 A A1
4 B B1
(2 行受影响)
*/
--II、Name相同ID最大的记录,与min相反:
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)
方法2:
select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID
方法3:
select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID
方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID group by a.ID,a.Name,a.Memo having count(1)=1
方法5:
select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)
方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)=0
方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)
方法8:
select * from #T a where ID!<all(select ID from #T where Name=a.Name)
方法9(注:ID为唯一时可用):
select * from #T a where ID in(select max(ID) from #T group by Name)
--SQL2005:
方法10:
select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID
方法11:
select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID desc) as MinID from #T a)T where MinID=1
生成结果2:
/*
ID Name Memo
----------- ---- ----
3 A A3
5 B B2
(2 行受影响)
*/
如果还不能解决问题 建议楼主给出具体需求 是根据哪个字段来去重复的
开着拖拉机泡妞 2012-12-14
- 打赏
- 举报
select * from tb where not exists(select 1 from tb a where a.col=b.col)
