select * from A表 a where exists(select 1 from A表 b where b.name=a.name and b.id!=a.id and datediff(day,a.date,b.date)=0)
select name from A group by name having count(*)>1
select name ,count(1) from A group by Year(date)+'-'+month(date)+'-'+day(date)
select * from A where name in ( select name from A group by name having(count(*))>1 ) and date > '2008-03-21 00:00:00' and date < '2008-03-22 00:00:00'
好像都不行 我要查出的记录必须是完整的 类似id name date 1 tom 2008-03-21 16:13:18.193 2 tom 2008-03-21 02:11:34.110 3 tom 2008-03-21 13:44:21.002
sjett 你的似乎没问题 但是我不确定要哪一天and date > '2008-03-21 00:00:00' and date < '2008-03-22 00:00:00' 这句怎么改
select * from A B where exists(select 1 from B where A.name=B.name and A.id>B.id) and convert(varchar(10),date,120)=convert(varchar(10),getdate(),120)
select * from A B where exists(select 1 from B where A.name=B.name and A.id>B.id) and convert(varchar(10),date,120)=convert(varchar(10),日期参数,120)
select * from A where date in ( select cast(convert(varchar,getdate(),112) as datetime) from A group by convert(varchar,getdate(),112),name having count(*)>1 )
上面的不对,应该是select * from A where convert(varchar,date,112) in ( select convert(varchar,date,112) from A group by convert(varchar,date,112),name having count(*)>1 )
改下10楼的 and date > convert(varchar(10),getdate(),120) and date < convert(varchar(10),DATEADD(day, 1,getdate()),120)
If Object_ID('tempdb..#t') IS NOT NUll drop table #tCreate Table #t (s_ID int IDENTITY (1, 1) ,s_name Nvarchar(10),s_Date DateTime)Insert into #t Select 'aaa','2008-5-10' Union All Select 'aaa','2008-5-11' Union All Select 'aaa','2008-5-11' Union All Select 'aaa','2008-5-11' Union All Select 'aaa','2008-5-12' Union All Select 'bbb','2008-5-10' Union All Select 'bbb','2008-5-11' Union All Select 'bbb','2008-5-12' Union All select 'bbb','2008-5-12'--查询所有记录 select * from #t --查询重复记录 select * from #t As B Where Exists(select * from #t where #t.s_Name=B.s_Name And #t.s_ID<>B.s_ID And DateDiff(day,#t.s_date,B.s_date)=0)
select * from A where name in ( select name from A group by name having(count(*))>1 ) and date > convert(varchar(10),getdate(),120) and date < convert(varchar(10),DATEADD(day, 1,getdate()),120)
select * from A where convert(varchar,date,112)+'_' + name in ( select convert(varchar,date,112)+'_' + name from A group by convert(varchar,date,112),name having count(*)>1 )
and date between convert(datetime,left(getdate(),10)+' 00:00:00.000') and dateadd(days,1,convert(datetime,left(getdate(),10)+' 00:00:00.000'))convert(datetime,left(getdate(),10)+' 00:00:00.000')获取当前时间取日期部分,加上' 00:00:00.000',就是当天的0点0分0秒函数dateadd()用来给天数加1,也就是第二天的0点0分0秒 两者之间就是当天的所有时间记录如果不确定哪一天,那就只能传个变量进来,然后同样用这种方法转换一下就行了
同意楼上,给分吧 不过听说where in 比where Exist耗性能
select top 1 name from A where date='你要的日期' order by date
Select * From dbo.RegisterStudent Where [Name] in (SELECT [Name] FROM RegisterStudent WHERE RegisterDate Between Getdate() AND (Dateadd(day,1,GetDate())) Group By [Name] having (Count([Name]))>1)
SELECT name ,count(name) count FROM abc WHERE CONVERT(char(10),date,120)= CONVERT(char(10),getdate(),120) GROUP BY name
不好意思,你要的是重复的记录,下面你试一下! SELECT name ,count(name) count FROM abc WHERE CONVERT(char(10),date,120)= CONVERT(char(10),getdate(),120) GROUP BY name HAVING count(name)>1
select * from A表 a where exists(select 1 from A表 b where b.name=a.name and b.id!=a.id and convert(varchar(10),a.date,120)=convert(varchar(10),b.date,120))
select * from a where count(name)>1 and substr(to_char(date,'yyyy-mm-dd'),0,10) as '系统的日期' = substr(to_char(date,'yyyy-mm-dd'),0,10) as date只要在24小时内就算相同的一天,所以只需'yyyy-mm-dd'相同就行。
select name,count(*) from A where date between '2008-03-21' and '2008-03-22' group by name having count(*)>1 [align=center]====思想重于技巧==== [/align]
select * from A where name in ( select name from A group by name having(count(*))>1 ) and datediff(day,date,getdate())=0
--查询一天内名字重复的记录 create table uu ( id int identity (1,1) primary key, name varchar(5), date datetime ) insert into uu([name],date) values('aa','2008-5-12') insert into uu([name],date) values('aa','2008-5-12') insert into uu([name],date) values('bb','2008-5-12') insert into uu([name],date) values('cc','2008-5-13') insert into uu([name],date) values('bb','2008-5-12') insert into uu([name],date) values('aa','2008-5-13') insert into uu([name],date) values('cc','2008-5-13') insert into uu([name],date) values('bb','2008-5-12') insert into uu([name],date) values('aa','2008-5-12') insert into uu([name],date) values('cc','2008-5-12')SELECT [name] ,count([name]) FROM uu WHERE date Between '2008-5-12' and '2008-5-13' GROUP BY [name] HAVING count([name])>1 兄弟给分吧!
declare @tb table([name] char(10),[date] datetime) Insert into @tb Select 'aaa','2008-5-10 16:13:18.907' Union All Select 'aaa','2008-5-12 16:18:18.208' Union All Select 'aaa','2008-5-12 16:23:18.208' Union All Select 'aaa','2008-5-12 16:33:18.208' Union All Select 'aaa','2008-5-12 16:43:18.208' Union All Select 'bbb','2008-5-10 16:23:18.208' Union All Select 'bbb','2008-5-11 16:13:18.208' Union All Select 'bbb','2008-5-12 16:13:18.208' Union All select 'bbb','2008-5-12 16:15:18.208' Union All select 'ccc','2008-5-12 16:15:18.208'select * from @tb tp where ( select count(1) from @tb where convert(varchar(10),date,120)=convert(varchar(10),tp.date,120) and name=tp.name )> =2 aaa 2008-05-12 16:18:18.207 aaa 2008-05-12 16:23:18.207 aaa 2008-05-12 16:33:18.207 aaa 2008-05-12 16:43:18.207 bbb 2008-05-12 16:13:18.207 bbb 2008-05-12 16:15:18.207 不知是否是楼主想要的结果
select * from A where datatime =[取前面字节] &[重复name]
select * from A where name in ( select name from A group by name having(count(*))>1 ) and datediff(day,date,getdate())=0 同意,这个
select name,count(name) as cnt from ( select name,convert(varchar(10),date,120) as date from A ) as a where date = '20080312' group by date
select * from A where name in ( select name from A group by name having(count(*))>1 ) and date =xxx把你要的日期传进去不就行了..上面的朋友已经帮你解决了.
select * from A where name in (select name from A group by name having count(name)>1 ) and day([date]) = 具体时间 group by [date]
select a.id ,a.name,a.date from A a,A b where a.name=b.name and to_char(a.date,'yyyymmdd')=to_char(b.date,'yyyymmdd') and a.rowid <> b.rowid這個應該沒有問題,rowid是數據庫里默有字段,不用定義
a表字段(aID,aName,aDate) select a.* from a , (select aName,convert(varchar(10),aDate,101) as dd from a group by aName,convert(varchar(10),aDate,101) having count(*)>1) b --同一天出现多次的Name和Date(不包括时间) where a.Nname=b.aName and convert(varchar(10),a.aDate,101)=b.dd
--sql server select name , convert(varchar(10),date,120) date from A group by name , convert(varchar(10),date,120) having count(*) > 1--oracle select name , trunc(date) date from A group by name , trunc(date) having count(*) > 1 select name , to_char(date,'yyyy-mm-dd') date from A group by name , to_char(date,'yyyy-mm-dd') having count(*) > 1
--只查日期 --sql server select name , convert(varchar(10),date,120) date from A group by name , convert(varchar(10),date,120) having count(*) > 1--oracle select name , trunc(date) date from A group by name , trunc(date) having count(*) > 1 select name , to_char(date,'yyyy-mm-dd') date from A group by name , to_char(date,'yyyy-mm-dd') having count(*) > 1--查日期时间 --sql server select A.* from A m where exists ( select 1 from ( select name , convert(varchar(10),date,120) date from A group by name , convert(varchar(10),date,120) having count(*) > 1 ) n where name = m.name and date = convert(varchar(10),m.date,120) )--oracle select A.* from A m where exists ( select 1 from ( select name , trunc(date) date from A group by name , trunc(date) having count(*) > 1 ) n where name = m.name and date = convert(varchar(10),m.date,120) )select A.* from A m where exists ( select 1 from ( select name , to_char(date,'yyyy-mm-dd') date from A group by name , to_char(date,'yyyy-mm-dd') having count(*) > 1 ) n where name = m.name and date = convert(varchar(10),m.date,120) )
select * from A表 a where exists(select 1 from A表 b where b.name=a.name and b.id!=a.id and datediff(day,a.date,b.date)=0)
where name in (
select name from A
group by name
having(count(*))>1
)
and date > '2008-03-21 00:00:00' and date < '2008-03-22 00:00:00'
我要查出的记录必须是完整的
类似id name date
1 tom 2008-03-21 16:13:18.193
2 tom 2008-03-21 02:11:34.110
3 tom 2008-03-21 13:44:21.002
但是我不确定要哪一天and date > '2008-03-21 00:00:00' and date < '2008-03-22 00:00:00'
这句怎么改
select * from A B where exists(select 1 from B where A.name=B.name and A.id>B.id)
and convert(varchar(10),date,120)=convert(varchar(10),getdate(),120)
and convert(varchar(10),date,120)=convert(varchar(10),日期参数,120)
如C#中beginDate = DateTime.Today和 endDate = DateTime.Today.AddDays(1)
(
select cast(convert(varchar,getdate(),112) as datetime) from A group by convert(varchar,getdate(),112),name having count(*)>1
)
(
select convert(varchar,date,112) from A group by convert(varchar,date,112),name having count(*)>1
)
and date > convert(varchar(10),getdate(),120) and date < convert(varchar(10),DATEADD(day, 1,getdate()),120)
If Object_ID('tempdb..#t') IS NOT NUll drop table #tCreate Table #t
(s_ID int IDENTITY (1, 1) ,s_name Nvarchar(10),s_Date DateTime)Insert into #t
Select 'aaa','2008-5-10' Union All
Select 'aaa','2008-5-11' Union All
Select 'aaa','2008-5-11' Union All
Select 'aaa','2008-5-11' Union All
Select 'aaa','2008-5-12' Union All
Select 'bbb','2008-5-10' Union All
Select 'bbb','2008-5-11' Union All
Select 'bbb','2008-5-12' Union All
select 'bbb','2008-5-12'--查询所有记录
select * from #t
--查询重复记录
select * from #t As B Where Exists(select * from #t where #t.s_Name=B.s_Name And #t.s_ID<>B.s_ID And DateDiff(day,#t.s_date,B.s_date)=0)
where name in (
select name from A
group by name
having(count(*))>1
)
and date > convert(varchar(10),getdate(),120) and date < convert(varchar(10),DATEADD(day, 1,getdate()),120)
(
select convert(varchar,date,112)+'_' + name from A group by convert(varchar,date,112),name having count(*)>1
)
and date between convert(datetime,left(getdate(),10)+' 00:00:00.000') and dateadd(days,1,convert(datetime,left(getdate(),10)+' 00:00:00.000'))convert(datetime,left(getdate(),10)+' 00:00:00.000')获取当前时间取日期部分,加上' 00:00:00.000',就是当天的0点0分0秒函数dateadd()用来给天数加1,也就是第二天的0点0分0秒
两者之间就是当天的所有时间记录如果不确定哪一天,那就只能传个变量进来,然后同样用这种方法转换一下就行了
不过听说where in 比where Exist耗性能
(SELECT [Name]
FROM RegisterStudent
WHERE RegisterDate Between Getdate() AND (Dateadd(day,1,GetDate()))
Group By [Name]
having (Count([Name]))>1)
SELECT name ,count(name) count FROM abc WHERE CONVERT(char(10),date,120)= CONVERT(char(10),getdate(),120)
GROUP BY name
SELECT name ,count(name) count FROM abc WHERE CONVERT(char(10),date,120)= CONVERT(char(10),getdate(),120)
GROUP BY name HAVING count(name)>1
convert(varchar(10),a.date,120)=convert(varchar(10),b.date,120))
substr(to_char(date,'yyyy-mm-dd'),0,10) as '系统的日期'
=
substr(to_char(date,'yyyy-mm-dd'),0,10) as date只要在24小时内就算相同的一天,所以只需'yyyy-mm-dd'相同就行。
from A
where date between '2008-03-21' and '2008-03-22'
group by name
having count(*)>1
[align=center]==== 思想重于技巧 ====
[/align]
where name in (
select name from A
group by name
having(count(*))>1
)
and datediff(day,date,getdate())=0
create table uu
(
id int identity (1,1) primary key,
name varchar(5),
date datetime
)
insert into uu([name],date) values('aa','2008-5-12')
insert into uu([name],date) values('aa','2008-5-12')
insert into uu([name],date) values('bb','2008-5-12')
insert into uu([name],date) values('cc','2008-5-13')
insert into uu([name],date) values('bb','2008-5-12')
insert into uu([name],date) values('aa','2008-5-13')
insert into uu([name],date) values('cc','2008-5-13')
insert into uu([name],date) values('bb','2008-5-12')
insert into uu([name],date) values('aa','2008-5-12')
insert into uu([name],date) values('cc','2008-5-12')SELECT [name] ,count([name]) FROM uu
WHERE date Between '2008-5-12' and '2008-5-13'
GROUP BY [name] HAVING count([name])>1
兄弟给分吧!
declare @tb table([name] char(10),[date] datetime)
Insert into @tb
Select 'aaa','2008-5-10 16:13:18.907' Union All
Select 'aaa','2008-5-12 16:18:18.208' Union All
Select 'aaa','2008-5-12 16:23:18.208' Union All
Select 'aaa','2008-5-12 16:33:18.208' Union All
Select 'aaa','2008-5-12 16:43:18.208' Union All
Select 'bbb','2008-5-10 16:23:18.208' Union All
Select 'bbb','2008-5-11 16:13:18.208' Union All
Select 'bbb','2008-5-12 16:13:18.208' Union All
select 'bbb','2008-5-12 16:15:18.208' Union All
select 'ccc','2008-5-12 16:15:18.208'select * from @tb tp
where (
select count(1) from @tb where convert(varchar(10),date,120)=convert(varchar(10),tp.date,120) and name=tp.name
)> =2
aaa 2008-05-12 16:18:18.207
aaa 2008-05-12 16:23:18.207
aaa 2008-05-12 16:33:18.207
aaa 2008-05-12 16:43:18.207
bbb 2008-05-12 16:13:18.207
bbb 2008-05-12 16:15:18.207
不知是否是楼主想要的结果
where name in (
select name from A
group by name
having(count(*))>1
)
and datediff(day,date,getdate())=0
同意,这个
from
(
select name,convert(varchar(10),date,120) as date from A
) as a
where date = '20080312'
group by date
where name in (
select name from A
group by name
having(count(*))>1
)
and date =xxx把你要的日期传进去不就行了..上面的朋友已经帮你解决了.
select * from A
where name in (select name from A
group by name
having count(name)>1
)
and day([date]) = 具体时间
group by [date]
a.id ,a.name,a.date
from
A a,A b
where
a.name=b.name and
to_char(a.date,'yyyymmdd')=to_char(b.date,'yyyymmdd') and
a.rowid <> b.rowid這個應該沒有問題,rowid是數據庫里默有字段,不用定義
select a.* from a ,
(select aName,convert(varchar(10),aDate,101) as dd from a group by aName,convert(varchar(10),aDate,101) having count(*)>1) b --同一天出现多次的Name和Date(不包括时间)
where a.Nname=b.aName and convert(varchar(10),a.aDate,101)=b.dd
--sql server
select name , convert(varchar(10),date,120) date from A group by name , convert(varchar(10),date,120) having count(*) > 1--oracle
select name , trunc(date) date from A group by name , trunc(date) having count(*) > 1
select name , to_char(date,'yyyy-mm-dd') date from A group by name , to_char(date,'yyyy-mm-dd') having count(*) > 1
--sql server
select name , convert(varchar(10),date,120) date from A group by name , convert(varchar(10),date,120) having count(*) > 1--oracle
select name , trunc(date) date from A group by name , trunc(date) having count(*) > 1
select name , to_char(date,'yyyy-mm-dd') date from A group by name , to_char(date,'yyyy-mm-dd') having count(*) > 1--查日期时间
--sql server
select A.* from A m where exists
(
select 1 from
(
select name , convert(varchar(10),date,120) date from A group by name , convert(varchar(10),date,120) having count(*) > 1
) n where name = m.name and date = convert(varchar(10),m.date,120)
)--oracle
select A.* from A m where exists
(
select 1 from
(
select name , trunc(date) date from A group by name , trunc(date) having count(*) > 1
) n where name = m.name and date = convert(varchar(10),m.date,120)
)select A.* from A m where exists
(
select 1 from
(
select name , to_char(date,'yyyy-mm-dd') date from A group by name , to_char(date,'yyyy-mm-dd') having count(*) > 1
) n where name = m.name and date = convert(varchar(10),m.date,120)
)
<head>
asdklfj
</head>
<body>
alkjdfjlksdjklfkjlsda
</body>
</html>