# 取出两字段同时有相同值的行

xiaowangtian1117 2008-03-21 11:46:42

name time
a 2008-03-01 02:00
a 2008-03-01 03:00
a 2008-03-01 02:30
b 2008-03-01 02:30
b 2008-03-01 01:00
c 2008-03-01 02:00
c 2008-03-01 02:30

name
a
b

...全文
61 点赞 收藏 15

15 条回复

xiaowangtian1117 2008-03-21
name字段为b的两条记录time字段时间相差1小时，所以符合条件，但是像c的两条记录是不符合条件的

zheninchangjiang 2008-03-21

zheninchangjiang 2008-03-21

-晴天 2008-03-21

-晴天 2008-03-21

zheninchangjiang 2008-03-21
select distinct a.name from tablename a
where exists(select 1 from tablename where abs(datediff(hour,time,a.time))>=1)

zheninchangjiang 2008-03-21
select distinct a.name from tablename
where exists(select 1 from tablename where abs(datediff(hour,time,a.time))>=1)

[Quote=引用 1 楼 qianjin036a 的回复:]
select distinic name from 原表
[/Quote]

-晴天 2008-03-21
select distinct name from 原表

-晴天 2008-03-21
select distinic name from 原表

QQQQAnnie 2008-03-21
``select distinct name from tableName where datediff(hour,getdate(),[time])>1``

JiangHongTao 2008-03-21
[Quote=引用 13 楼 xiaowangtian1117 的回复:]

exists(select 1 from tb where a.name = name and datediff(hh,time,a.time)>=1)什么意思？

[/Quote]

xiaowangtian1117 2008-03-21

exists(select 1 from tb where a.name = name and datediff(hh,time,a.time)>=1)什么意思？

JiangHongTao 2008-03-21
`````` create table tb(name varchar(10),time  datetime)
insert tb select 'a',      '2008-03-01 02:00'
union all select 'a',      '2008-03-01 03:00'
union all select 'a',      '2008-03-01 02:30'
union all select 'b',      '2008-03-01 02:30'
union all select 'b',      '2008-03-01 01:00'
union all select 'c',      '2008-03-01 02:00'
union all select 'c',      '2008-03-01 02:30'
go
select distinct name from tb a where
exists(select 1 from tb where a.name = name and datediff(hh,time,a.time)>=1)

go
drop table tb
/*
name
----------
a
b
*/``````

MS-SQL Server

3.3w+

MS-SQL Server相关内容讨论专区

2008-03-21 11:46