求语句

Question

          col1    col2           addtime
fdsfds abc 2011-12-09 09:15:30.640
fdsfds abc 2011-12-09 09:15:30.640
fdsfds abc 2011-12-09 09:15:30.640
11abc abc 2011-12-09 09:15:30.640
24abc abc 2011-12-09 09:15:30.640
24abc abc 2011-12-09 09:15:30.640
24abc abc 2011-12-09 09:15:30.640
24abc abc 2011-12-09 09:15:30.640
5983abc abc 2011-12-09 09:18:45
25abc abc 2011-12-09 09:15:30.640
26abc abc 2011-12-09 09:15:30.640
26abc abc 2011-12-09 09:15:30.640
27abc abc 2011-12-09 09:15:30.640
27abc abc 2011-12-09 09:15:30.640
28abc abc 2011-12-09 09:15:30.640      想要结果：
         fdsfds abc 2011-12-09 09:15:30.640
         11abc abc 2011-12-09 09:15:30.640
         24abc abc 2011-12-09 09:15:30.640
         5983abc abc 2011-12-09 09:18:45
         25abc abc 2011-12-09 09:15:30.640
         26abc abc 2011-12-09 09:15:30.640
         27abc abc 2011-12-09 09:15:30.640
28abc abc 2011-12-09 09:15:30.640      SqlServer2008 数据无规律 数据也很多 头疼阿   先谢谢啦

Answer 1

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-12-28 11:09:08
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
-- Apr 22 2011 11:57:00 
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col1] varchar(7),[col2] varchar(3),[addtime] datetime)
insert [tb]
select 'fdsfds','abc','2011-12-09 09:15:30.640' union all
select 'fdsfds','abc','2011-12-09 09:15:30.640' union all
select 'fdsfds','abc','2011-12-09 09:15:30.640' union all
select '11abc','abc','2011-12-09 09:15:30.640' union all
select '24abc','abc','2011-12-09 09:15:30.640' union all
select '24abc','abc','2011-12-09 09:15:30.640' union all
select '24abc','abc','2011-12-09 09:15:30.640' union all
select '24abc','abc','2011-12-09 09:15:30.640' union all
select '5983abc','abc','2011-12-09 09:18:45' union all
select '25abc','abc','2011-12-09 09:15:30.640' union all
select '26abc','abc','2011-12-09 09:15:30.640' union all
select '26abc','abc','2011-12-09 09:15:30.640' union all
select '27abc','abc','2011-12-09 09:15:30.640' union all
select '27abc','abc','2011-12-09 09:15:30.640' union all
select '28abc','abc','2011-12-09 09:15:30.640'
--------------开始查询--------------------------
select
 col1,col2,addtime 
from
  (select px=row_number()over(partition by col1 order by (select 1)),* from tb)t
where
  px=1
----------------结果----------------------------
/* col1    col2 addtime
------- ---- -----------------------
11abc   abc  2011-12-09 09:15:30.640
24abc   abc  2011-12-09 09:15:30.640
25abc   abc  2011-12-09 09:15:30.640
26abc   abc  2011-12-09 09:15:30.640
27abc   abc  2011-12-09 09:15:30.640
28abc   abc  2011-12-09 09:15:30.640
5983abc abc  2011-12-09 09:18:45.000
fdsfds  abc  2011-12-09 09:15:30.640(8 行受影响)*/

Answer 2

 select distinct col1,  col2 , addtime from tb

Answer 3

;with tab as(
select top 20 ROW_NUMBER() over(partition by col1 order by col1) as rowID,* 
from Table)
select * from tab where rowID = 1和1楼的一样...
col1 可以做索引

Answer 4

select distinct col1,  col2 , addtime from tb

Answer 5

就针对你目前问题而言select distinct col1,  col2 , addtime from tb 此方法就可以了
不过select
 col1,col2,addtime 
from
  (select px=row_number()over(partition by col1 order by (select 1)),* from tb)t
where
  px=1能解决更复杂得情况

Answer 6

ORSELECT DISTINCT COL1,COL2,ADDTIME FROM TB

Answer 7

 找出同样列的一行数据。if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col1] varchar(7),[col2] varchar(3),[addtime] datetime)
insert [tb]
select 'fdsfds','abc','2011-12-09 09:15:30.640' union all
select 'fdsfds','abc','2011-12-09 09:15:30.640' union all
select 'fdsfds','abc','2011-12-09 09:15:30.640' union all
select '11abc','abc','2011-12-09 09:15:30.640' union all
select '24abc','abc','2011-12-09 09:15:30.640' union all
select '24abc','abc','2011-12-09 09:15:30.640' union all
select '24abc','abc','2011-12-09 09:15:30.640' union all
select '24abc','abc','2011-12-09 09:15:30.640' union all
select '5983abc','abc','2011-12-09 09:18:45' union all
select '25abc','abc','2011-12-09 09:15:30.640' union all
select '26abc','abc','2011-12-09 09:15:30.640' union all
select '26abc','abc','2011-12-09 09:15:30.640' union all
select '27abc','abc','2011-12-09 09:15:30.640' union all
select '27abc','abc','2011-12-09 09:15:30.640' union all
select '28abc','abc','2011-12-09 09:15:30.640'--1--
  select  distinct col1,col2,addtime from tb
col1    col2 addtime
------- ---- -----------------------
11abc   abc  2011-12-09 09:15:30.640
24abc   abc  2011-12-09 09:15:30.640
25abc   abc  2011-12-09 09:15:30.640
26abc   abc  2011-12-09 09:15:30.640
27abc   abc  2011-12-09 09:15:30.640
28abc   abc  2011-12-09 09:15:30.640
5983abc abc  2011-12-09 09:18:45.000
fdsfds  abc  2011-12-09 09:15:30.640(8 行受影响)