一个去重复的问题！！！

qiudong_5210 2012-11-05 09:55:29



CREATE TABLE #F

(

 InvieteID INT IDENTITY(1,1),

 FromUserId INT,

 ToUserId INT,

 AddTime DATETIME,

 States INT

)

INSERT INTO #F(FromUserId,ToUserId,AddTime,States)

SELECT 135732,153,'2012-11-02 18:01:48.783',0 UNION

SELECT 135732,184,'2012-11-02 18:00:30.040',0 UNION

SELECT 135732,152,'2012-11-02 17:30:38.513',0 UNION

SELECT 135728,135776,'2012-11-02 16:35:12.977',0 UNION

SELECT 16,7,'2012-11-02 11:25:21.620',0 UNION

SELECT 135732,7,'2012-11-02 11:23:59.220',0 UNION

SELECT 135728,135776,'2012-11-02 09:50:03.023',2 UNION

SELECT 135728,135776,'2012-11-02 09:49:33.303',0 UNION

SELECT 135728,135776,'2012-11-02 09:49:17.303',1 UNION

SELECT 135728,135776,'2012-11-02 09:48:42.507',0 UNION

SELECT 135728,135776,'2012-11-02 09:46:02.663',0 UNION

SELECT 135728,135776,'2012-11-02 09:44:39.617',2 UNION

SELECT 135732,121,'2012-10-31 10:26:09.563',0 UNION

SELECT 135732,127,'2012-10-29 10:02:09.617',0 UNION

SELECT 135732,112,'2012-10-29 10:02:00.750',0 UNION

SELECT 135732,109,'2012-10-29 10:01:58.287',0 UNION

SELECT 135732,102,'2012-10-29 10:01:34.147',0



DROP TABLE #F

/*

想要的结果，相同的FromUserId和ToUserId只取出时间最近的一条记录，即使States不同，也是取出时间最近的一条记录



*/

...全文

138 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

xb12369 2012-11-05

打赏
举报

晕了

fangpengyu 2012-11-05

打赏
举报

select FromUserId,ToUserId,max(AddTime) as AddTime from 

(SELECT 135732 as FromUserId,153 as ToUserId,'2012-11-02 18:01:48.783' as AddTime,0 as States UNION
SELECT 135732,184,'2012-11-02 18:00:30.040',0 UNION
SELECT 135732,152,'2012-11-02 17:30:38.513',0 UNION
SELECT 135728,135776,'2012-11-02 16:35:12.977',0 UNION
SELECT 16,7,'2012-11-02 11:25:21.620',0 UNION
SELECT 135732,7,'2012-11-02 11:23:59.220',0 UNION
SELECT 135728,135776,'2012-11-02 09:50:03.023',2 UNION
SELECT 135728,135776,'2012-11-02 09:49:33.303',0 UNION
SELECT 135728,135776,'2012-11-02 09:49:17.303',1 UNION
SELECT 135728,135776,'2012-11-02 09:48:42.507',0 UNION
SELECT 135728,135776,'2012-11-02 09:46:02.663',0 UNION
SELECT 135728,135776,'2012-11-02 09:44:39.617',2 UNION
SELECT 135732,121,'2012-10-31 10:26:09.563',0 UNION
SELECT 135732,127,'2012-10-29 10:02:09.617',0 UNION
SELECT 135732,112,'2012-10-29 10:02:00.750',0 UNION
SELECT 135732,109,'2012-10-29 10:01:58.287',0 UNION
SELECT 135732,102,'2012-10-29 10:01:34.147',0) as a
group by FromUserId,ToUserId

开着拖拉机泡妞 2012-11-05

打赏
举报

CREATE TABLE #F
(
 InvieteID INT IDENTITY(1,1),
 FromUserId INT,
 ToUserId INT,
 AddTime DATETIME,
 States INT
)
INSERT INTO #F(FromUserId,ToUserId,AddTime,States)
SELECT 135732,153,'2012-11-02 18:01:48.783',0 UNION
SELECT 135732,184,'2012-11-02 18:00:30.040',0 UNION
SELECT 135732,152,'2012-11-02 17:30:38.513',0 UNION
SELECT 135728,135776,'2012-11-02 16:35:12.977',0 UNION
SELECT 16,7,'2012-11-02 11:25:21.620',0 UNION
SELECT 135732,7,'2012-11-02 11:23:59.220',0 UNION
SELECT 135728,135776,'2012-11-02 09:50:03.023',2 UNION
SELECT 135728,135776,'2012-11-02 09:49:33.303',0 UNION
SELECT 135728,135776,'2012-11-02 09:49:17.303',1 UNION
SELECT 135728,135776,'2012-11-02 09:48:42.507',0 UNION
SELECT 135728,135776,'2012-11-02 09:46:02.663',0 UNION
SELECT 135728,135776,'2012-11-02 09:44:39.617',2 UNION
SELECT 135732,121,'2012-10-31 10:26:09.563',0 UNION
SELECT 135732,127,'2012-10-29 10:02:09.617',0 UNION
SELECT 135732,112,'2012-10-29 10:02:00.750',0 UNION
SELECT 135732,109,'2012-10-29 10:01:58.287',0 UNION
SELECT 135732,102,'2012-10-29 10:01:34.147',0
go

select
	*
from
	#F a
where
	a.AddTime=(
				select 
					MAX(b.AddTime)
				from 
					#F b
				where
					a.FromUserId=b.FromUserId 
					and a.ToUserId=b.ToUserId
					and a.States=b.States
				)
/*
InvieteID   FromUserId  ToUserId    AddTime                 States
----------- ----------- ----------- ----------------------- -----------
17          135732      184         2012-11-02 18:00:30.040 0
16          135732      153         2012-11-02 18:01:48.783 0
15          135732      152         2012-11-02 17:30:38.513 0
14          135732      127         2012-10-29 10:02:09.617 0
13          135732      121         2012-10-31 10:26:09.563 0
12          135732      112         2012-10-29 10:02:00.750 0
11          135732      109         2012-10-29 10:01:58.287 0
10          135732      102         2012-10-29 10:01:34.147 0
9           135732      7           2012-11-02 11:23:59.220 0
7           135728      135776      2012-11-02 09:50:03.023 2
5           135728      135776      2012-11-02 09:49:17.303 1
8           135728      135776      2012-11-02 16:35:12.977 0
1           16          7           2012-11-02 11:25:21.620 0

(13 行受影响)



*/

qiudong_5210 2012-11-05

打赏
举报

引用 2 楼 ssp2009 的回复:

select * from #F a where not exists(select 1 from #F where FromUserId=a.FromUserId and ToUserId=a.ToUserId and AddTime>a.AddTime)

这种必须用嵌套查询是么？

快溜 2012-11-05

打赏
举报

select * from #F a where not exists(select 1 from #F where FromUserId=a.FromUserId and ToUserId=a.ToUserId and AddTime>a.AddTime)

开着拖拉机泡妞 2012-11-05

打赏
举报


select
	*
from
	#F a
where
	a.AddTime=(
				select 
					MAX(b.AddTime)
				from 
					#F b
				where
					a.FromUserId=b.FromUserId 
					and a.InvieteID=b.InvieteID
					and a.States=b.States
				)

◎ 双版面设计，用户可以根据不同爱好选择不同的版式（酷窗版和精简版），系统会记录用户的选择，不用重复选择，也不会出现版面混淆等现象； ◎ 发贴回贴互动功能，给用户随机的互动事件，增加用户的参与兴趣； ...

name属性值重复导致保存的值有逗号 ...经过了一番内心斗争（肯定是操作的问题！！）和debug后，发现在进入更新接口的时候，值就被加上逗号了，所以感觉是页面jsp做了什么操作，然后发现了以下代码（栗子） <div

首先分析的是id生成是不是就是重复了，先关掉其中一台机器，单机跑，这个时候发现压到1000的并发都没有出现过id重复，这个说明单机情况下不存在id重复问题，说明只有集群的情况下才会出现。再分析一下id生成的几个...

原来的帖子《铁道部网络购票网站存在的一个大问题----一个大大的事务！！！》放到这里：http://blog.csdn.net/dragonimp/article/details/7192777 这几天的评论里面，大部分是比较理性思考的，而也有部分朋友不...

不可重复读就是一个事务读到另一个事务修改后并提交的数据（update）。在同一个事务中，对于同一组数据读取到的结果不一致。比如，事务B 在事务A 提交前读到的结果，和在事务A 提交后读到的结果可能不同。不可...