简单SQL语句，Select？

[insert2003]

有表

tb_Temp(c_No,dt_Time,......)

现在要查找

c_No='12345678'

dt_Time相同的记录？

怎么写代码？

要求：最省时间的语句！

[zlp321002]

--try
select * from tb_Temp
where c_No='12345678' and exists (select 1 from tb_Temp where dt_Time=a.dt_Time)

[棒主]

group by 不好吗?

[Andy__Huang]

select * from tb_temp
where c_No='12345678' and dt_Time in
(select dt_Time from tb_temp group by dt_Time having count(*)>1)

[singlepine]

select * from tb_Temp where c_No='12345678' and dt_Time in(select distinct dt_Time from tb_Temp)

[insert2003]

怎样写速度会最快呢？

[zlp321002]

--这么写效率应该高点
select * from tb_Temp a
where c_No='12345678' and exists
(select 1 from tb_Temp where c_No=a.c_No and dt_Time=a.dt_Time
group by dt_Time having count(*)>1)

[brooks105]

select * from tb_Temp
where dt_time in(select dt_time from tb_Temp
group by dt_time
having count(1)>1) and c_No='12345678'

[vivianfdlpw]

select * from 表 t
where c_No='12345678'
and (select count(1)
from 表
where C_NO=t.C_NO
and dt_Time=t.dt_Time)>1

[insert2003]

汗~~~应该是我的描述有问题

应该是这样的

如
有以下记录

c_No dt_Time
12345678 2004-05-01 12:00:00
99999999 2004-05-01 12:00:00
65487741 2005-03-02 12:00:00
12345678 2005-09-01 12:00:00
55555555 2005-09-01 12:00:00
21212121 2005-09-10 12:00:00

要得到如下结果：
c_No dt_Time
12345678 2004-05-01 12:00:00
99999999 2004-05-01 12:00:00
12345678 2005-09-01 12:00:00
55555555 2005-09-01 12:00:00

[zlp321002]

--是时间相同的记录取出来

--测试环境
declare @tb_Temp table (c_No varchar(200),dt_Time datetime )
insert into @tb_Temp select '12345678','2004-05-01 12:00:00'
insert into @tb_Temp select '99999999','2004-05-01 12:00:00'
insert into @tb_Temp select '65487741','2005-03-02 12:00:00'
insert into @tb_Temp select '12345678','2005-09-01 12:00:00'
insert into @tb_Temp select '55555555','2005-09-01 12:00:00'
insert into @tb_Temp select '21212121','2005-09-10 12:00:00'


--查询
select * from @tb_Temp a
where exists
(select 1 from @tb_Temp where dt_Time=a.dt_Time
group by dt_Time having count(*)>1)

--结果
12345678 2004-05-01 12:00:00.000
99999999 2004-05-01 12:00:00.000
12345678 2005-09-01 12:00:00.000
55555555 2005-09-01 12:00:00.000

[zlp321002]

用 exists 的效率应该是比较高的!~ 推荐用 Exists 的方法!

[insert2003]

忘了还有条件:
c_No='12345678' 时

如
有以下记录

c_No dt_Time
12345678 2004-05-01 12:00:00
99999999 2004-05-01 12:00:00
65487741 2005-03-02 12:00:00
12345678 2005-09-01 12:00:00
55555555 2005-09-01 12:00:00
21212121 2005-09-10 12:00:00
88888888 2005-09-26 12:00:00
31313131 2005-09-26 12:00:00


要得到如下结果：
c_No dt_Time
12345678 2004-05-01 12:00:00
99999999 2004-05-01 12:00:00
12345678 2005-09-01 12:00:00
55555555 2005-09-01 12:00:00

这两条记录不要select出来
88888888 2005-09-26 12:00:00
31313131 2005-09-26 12:00:00

说了半天都没说清楚，真是晕^_^，不好意思

[vivianfdlpw]

declare @tb_Temp table (c_No varchar(10),dt_Time datetime )
insert into @tb_Temp select '12345678','2004-05-01 12:00:00'
insert into @tb_Temp select '99999999','2004-05-01 12:00:00'
insert into @tb_Temp select '65487741','2005-03-02 12:00:00'
insert into @tb_Temp select '12345678','2005-09-01 12:00:00'
insert into @tb_Temp select '55555555','2005-09-01 12:00:00'
insert into @tb_Temp select '21212121','2005-09-10 12:00:00'

--查询
select * from @tb_Temp t
where (select count(1)
from @tb_Temp
where dt_Time=t.dt_Time)>1
order by dt_Time

--结果
/*
c_No dt_Time
---------- ------------------------
12345678 2004-05-01 12:00:00.000
99999999 2004-05-01 12:00:00.000
12345678 2005-09-01 12:00:00.000
55555555 2005-09-01 12:00:00.000

(4 row(s) affected)
*/

[zlp321002]

--加个条件拉!!
declare @tb_Temp table (c_No varchar(200),dt_Time datetime )
insert into @tb_Temp select '12345678','2004-05-01 12:00:00'
insert into @tb_Temp select '99999999','2004-05-01 12:00:00'
insert into @tb_Temp select '65487741','2005-03-02 12:00:00'
insert into @tb_Temp select '12345678','2005-09-01 12:00:00'
insert into @tb_Temp select '55555555','2005-09-01 12:00:00'
insert into @tb_Temp select '21212121','2005-09-10 12:00:00'


--查询
select * from @tb_Temp a
where c_No='12345678' and exists
(select 1 from @tb_Temp where dt_Time=a.dt_Time
group by dt_Time having count(*)>1)

--结果
12345678 2004-05-01 12:00:00.000
12345678 2005-09-01 12:00:00.000

[insert2003]

declare @tb_Temp table (c_No varchar(10),dt_Time datetime )
insert into @tb_Temp select '12345678','2004-05-01 12:00:00'
insert into @tb_Temp select '99999999','2004-05-01 12:00:00'
insert into @tb_Temp select '65487741','2005-03-02 12:00:00'
insert into @tb_Temp select '12345678','2005-09-01 12:00:00'
insert into @tb_Temp select '55555555','2005-09-01 12:00:00'
insert into @tb_Temp select '21212121','2005-09-10 12:00:00'
insert into @tb_Temp select '88888888','2005-09-26 12:00:00'
insert into @tb_Temp select '31313131','2005-09-26 12:00:00'

select * from @tb_Temp t
where (select count(1)
from @tb_Temp
where dt_Time=t.dt_Time)>1
order by dt_Time

--------------------结果集
12345678 2004-05-01 12:00:00.000
99999999 2004-05-01 12:00:00.000
12345678 2005-09-01 12:00:00.000
55555555 2005-09-01 12:00:00.000
88888888 2005-09-26 12:00:00.000
31313131 2005-09-26 12:00:00.000


不行啊，不要将
88888888 2005-09-26 12:00:00.000
31313131 2005-09-26 12:00:00.000
这两条记录
select 出来！

[vivianfdlpw]

晕。。。。。。。

[insert2003]

要得到这个结果的：
c_No dt_Time
12345678 2004-05-01 12:00:00
99999999 2004-05-01 12:00:00
12345678 2005-09-01 12:00:00
55555555 2005-09-01 12:00:00

不是：
12345678 2004-05-01 12:00:00.000
12345678 2005-09-01 12:00:00.000

[insert2003]

不好意思，老大^_^

[zlp321002]

汗.

两个结果.我都写了!你参考一下!想要哪个你自己要考虑清楚啊!楼主!!
把问题理一理啊!!!

[insert2003]

确实我的头有点晕！
重新整理一下

放到这里：
请看看
http://community.csdn.net/Expert/topic/4295/4295677.xml?temp=.6577112