取唯一值问题

[totoz]

表结构：

create table a(ID varchar(50), time datetime, name varchar(10) , typeid varchar(10))
insert into a values('5FF8A054-E5B6-443F-A2EB-D7CA905F7E9F' , '2011-01-23 08:38:00.000' , '异常时间' , '10536')
insert into a values('D07FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-23 08:38:00.000' , '异常时间' , 'E2345')
insert into a values('pp7FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-23 08:38:00.000' , '异常时间' , 'E2345')
insert into a values('733EA38A-B449-4F53-A6AC-72630DA60DFC' , '2011-01-23 13:38:00.000' , '异常时间' , 'M33333')
create table b(typeid varchar(10), userID varchar(10))
insert into b values('10536' , '张三')
insert into b values('E2345' , '张三')
insert into b values('M33333', '张三')
go

我想返回张三，时间不一样的前两条数据，也就是

ID time name typeid

5FF8A054-E5B6-443F-A2EB-D7CA905F7E9F 2011-01-23 08:38:00.000 时间1 10536


733EA38A-B449-4F53-A6AC-72630DA60DFC 2011-01-23 13:38:00.000 时间3 M33333



我的写法是：
select top 2 a.* from a,b
where not exists (select 1 from a where CONVERT(CHAR(20),a.Time,120)=CONVERT(CHAR(20),Time,120) AND Time>a.Time )
and b.userid = '张三' and a.typeid = b.typeid

但是一直取不到字段Time不一样的值， 都是2011-01-23 08:38:00.000 的两条数据

[icelovey]

create table a(ID varchar(50), [time] datetime, name varchar(10) , typeid varchar(10)) 
insert into a values('5FF8A054-E5B6-443F-A2EB-D7CA905F7E9F' , '2011-01-23 08:38:00.000' , '异常时间' , '10536') 
insert into a values('D07FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-23 08:38:00.000' , '异常时间' , 'E2345') 
insert into a values('pp7FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-23 08:38:00.000' , '异常时间' , 'E2345') 
insert into a values('733EA38A-B449-4F53-A6AC-72630DA60DFC' , '2011-01-23 13:38:00.000' , '异常时间' , 'M33333') 

SELECT ID, [time], name, typeid
FROM (
		SELECT *, ROW_NUMBER() OVER (PARTITION BY [time] ORDER BY [time]) AS ROWID
		FROM A
	 ) AS A
WHERE A.ROWID=1


drop table a

--------------------------
ID                                                 time                    name       typeid
-------------------------------------------------- ----------------------- ---------- ----------
5FF8A054-E5B6-443F-A2EB-D7CA905F7E9F               2011-01-23 08:38:00.000 异常时间       10536
733EA38A-B449-4F53-A6AC-72630DA60DFC               2011-01-23 13:38:00.000 异常时间       M33333

(2 row(s) affected)

[SQL77]

用NOT EXISTS

[--小F--]

create table a(ID varchar(50), time datetime, name varchar(10) , typeid varchar(10)) 
insert into a values('5FF8A054-E5B6-443F-A2EB-D7CA905F7E9F' , '2011-01-23 08:38:00.000' , '异常时间' , '10536') 
insert into a values('D07FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-23 08:38:00.000' , '异常时间' , 'E2345') 
insert into a values('pp7FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-23 08:38:00.000' , '异常时间' , 'E2345') 
insert into a values('733EA38A-B449-4F53-A6AC-72630DA60DFC' , '2011-01-23 13:38:00.000' , '异常时间' , 'M33333') 
create table b(typeid varchar(10), userID varchar(10)) 
insert into b values('10536' , '张三') 
insert into b values('E2345' , '张三') 
insert into b values('M33333', '张三') 
go 
--select * from a
--select * from b
;with f as
(
select a.* from a join b on a.typeid=b.typeid
)
select id,time,name,typeid from (select id0=row_number()over(partition by time order by time),* from f)t where id0<2
drop table a,b
/*id                                                 time                    name       typeid
-------------------------------------------------- ----------------------- ---------- ----------
5FF8A054-E5B6-443F-A2EB-D7CA905F7E9F               2011-01-23 08:38:00.000 异常时间       10536
733EA38A-B449-4F53-A6AC-72630DA60DFC               2011-01-23 13:38:00.000 异常时间       M33333

(2 行受影响)*/

[dawugui]

create table a(ID varchar(50), time datetime, name varchar(10) , typeid varchar(10)) 
insert into a values('5FF8A054-E5B6-443F-A2EB-D7CA905F7E9F' , '2011-01-23 08:38:00.000' , '异常时间' , '10536') 
insert into a values('D07FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-23 08:38:00.000' , '异常时间' , 'E2345') 
insert into a values('pp7FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-23 08:38:00.000' , '异常时间' , 'E2345') 
insert into a values('733EA38A-B449-4F53-A6AC-72630DA60DFC' , '2011-01-23 13:38:00.000' , '异常时间' , 'M33333') 
create table b(typeid varchar(10), userID varchar(10)) 
insert into b values('10536' , '张三') 
insert into b values('E2345' , '张三') 
insert into b values('M33333', '张三') 
go 

select m.id , m.time, m.name , m.typeid  from
(
select t.* , px = (select count(1) from a where typeid in (select typeid from b where b.userid = '张三') and time = t.time and id < t.id) + 1 
from a t where typeid in (select typeid from b where b.userid = '张三')
) m
where px = 1

drop table a , b

/*
id                                                 time                                                   name       typeid     
-------------------------------------------------- ------------------------------------------------------ ---------- ---------- 
5FF8A054-E5B6-443F-A2EB-D7CA905F7E9F               2011-01-23 08:38:00.000                                异常时间       10536
733EA38A-B449-4F53-A6AC-72630DA60DFC               2011-01-23 13:38:00.000                                异常时间       M33333

（所影响的行数为 2 行）
*/

[totoz]

3楼和4楼的有问题，当数据量多的时候看：

-----------------------------------
create table a(ID varchar(50), time datetime, name varchar(10) , typeid varchar(10))
insert into a values('5FF8A054-E5B6-443F-A2EB-D7CA905F7E9F' , '2011-01-23 08:38:00.000' , '异常时间' , '10536')
insert into a values('D07FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-23 08:38:00.000' , '异常时间' , 'E2345')
insert into a values('pp7FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-23 08:38:00.000' , '异常时间' , 'E2345')
insert into a values('733EA38A-B449-4F53-A6AC-72630DA60DFC' , '2011-01-23 13:38:00.000' , '异常时间' , 'M33333')
insert into a values('p27FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-22 08:38:00.000' , '异常时间' , 'E2345')
insert into a values('p37FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-21 08:38:00.000' , '异常时间' , 'E2345')
create table b(typeid varchar(10), userID varchar(10))
insert into b values('10536' , '张三')
insert into b values('E2345' , '张三')
insert into b values('M33333', '张三')
go

with f as
(
select a.* from a join b on a.typeid=b.typeid
)
select id,time,name,typeid from (select id0=row_number()over(partition by time order by time),* from f)t where id0<2
drop table a,b

会返回4条数据

[sych888]

select top 2 a.*,b.* from a left join b on a.typeid=b.typeid
where userID='张三' and a.id in
(
select min(a.id) from a left join b on a.typeid=b.typeid where userID='张三'
group by time)

[dawugui]

[Quote=引用 5 楼 totoz 的回复:]
3楼和4楼的有问题，当数据量多的时候看：

-----------------------------------
create table a(ID varchar(50), time datetime, name varchar(10) , typeid varchar(10))
insert into a values('5FF8A054-E5B6-443F-A2EB-D7CA905F7E9F' , '2011-01-23 08:38:00.000' , '异常时间' , '10536')
insert into a values('D07FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-23 08:38:00.000' , '异常时间' , 'E2345')
insert into a values('pp7FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-23 08:38:00.000' , '异常时间' , 'E2345')
insert into a values('733EA38A-B449-4F53-A6AC-72630DA60DFC' , '2011-01-23 13:38:00.000' , '异常时间' , 'M33333')
insert into a values('p27FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-22 08:38:00.000' , '异常时间' , 'E2345')
insert into a values('p37FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-21 08:38:00.000' , '异常时间' , 'E2345')
create table b(typeid varchar(10), userID varchar(10))
insert into b values('10536' , '张三')
insert into b values('E2345' , '张三')
insert into b values('M33333', '张三')
go

with f as
(
select a.* from a join b on a.typeid=b.typeid
)
select id,time,name,typeid from (select id0=row_number()over(partition by time order by time),* from f)t where id0 <2
drop table a,b

会返回4条数据



[/Quote]

create table a(ID varchar(50), time datetime, name varchar(10) , typeid varchar(10)) 
insert into a values('5FF8A054-E5B6-443F-A2EB-D7CA905F7E9F' , '2011-01-23 08:38:00.000' , '异常时间' , '10536') 
insert into a values('D07FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-23 08:38:00.000' , '异常时间' , 'E2345') 
insert into a values('pp7FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-23 08:38:00.000' , '异常时间' , 'E2345') 
insert into a values('733EA38A-B449-4F53-A6AC-72630DA60DFC' , '2011-01-23 13:38:00.000' , '异常时间' , 'M33333') 
insert into a values('p27FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-22 08:38:00.000' , '异常时间' , 'E2345') 
insert into a values('p37FDE95-2E95-4408-ABD4-83931FB22CA6' , '2011-01-21 08:38:00.000' , '异常时间' , 'E2345') create table b(typeid varchar(10), userID varchar(10)) 
insert into b values('10536' , '张三') 
insert into b values('E2345' , '张三') 
insert into b values('M33333', '张三') 
go 

select top 2 m.id , m.time, m.name , m.typeid  from
(
select t.* , px = (select count(1) from a where typeid in (select typeid from b where b.userid = '张三') and time = t.time and id < t.id) + 1 
from a t where typeid in (select typeid from b where b.userid = '张三')
) m
where px = 1

drop table a , b

/*
id                                                 time                                                   name       typeid     
-------------------------------------------------- ------------------------------------------------------ ---------- ---------- 
5FF8A054-E5B6-443F-A2EB-D7CA905F7E9F               2011-01-23 08:38:00.000                                异常时间       10536
733EA38A-B449-4F53-A6AC-72630DA60DFC               2011-01-23 13:38:00.000                                异常时间       M33333

（所影响的行数为 2 行）


*/