去除重复行的SQL

[zedan]

表 table1
id column1
------
1. a
2. b
3. c
4. d
5. a
6. b

要得到没有重复数据的行的总数,如何写SQL?

即如上, 1-5, 2-6 行重复了, 所以结果为 2

[zedan]

谢谢各位了,尽管有些是没看清要求的.

[kook_tian]

select sum(aa) from (select count(*)-1 aa from table1 group by column1) ss

[kook_tian]

select sum(ss.aa) from (select count(*)-1 aa from DictThink group by plistid) ss

这么写标准点，可惜来晚了。

[dawugui]

SELECT COUNT(DISTINCT *) FROM TABLE1

[wzy_love_sly]

declare @tb table (id int,col varchar(1))
insert into @tb select 1,'a'
insert into @tb select 2,'b'
insert into @tb select 3,'c'
insert into @tb select 4,'d'
insert into @tb select 5,'a'
insert into @tb select 6,'b'
select sum(con) as '次数' from (
select con=(select count(1) from @tb where col=t.col and id>t.id) from @tb t)t

次数
2

[dawugui]

SELECT COUNT(*) FROM (SELECT DISTINCT * FROM TABLE1) T

[dawugui]

[Quote=引用楼主 zedan 的帖子:]
表 table1
id column1
------
1. a
2. b
3. c
4. d
5. a
6. b

要得到没有重复数据的行的总数,如何写SQL?

即如上, 1-5, 2-6 行重复了, 所以结果为 2
[/Quote]

SELECT COUNT(*) FROM TABLE1 GROUP BY ID , COLUMN1 HAVINGCOUNT(*) = 1

[zedan]

[Quote=引用 1 楼 dawugui 的回复:]
SELECT COUNT(DISTINCT *) FROM TABLE1
[/Quote]

我可能没说清楚, 不是要唯一数据的行数,而是要没有重复数据的行数, 比如有二行的数据是重复的,则这两行都不计,而不是将这两行计一行

[dawugui]

[Quote=引用楼主 zedan 的帖子:]
表 table1
id column1
------
1. a
2. b
3. c
4. d
5. a
6. b

要得到没有重复数据的行的总数,如何写SQL?

即如上, 1-5, 2-6 行重复了, 所以结果为 2
[/Quote]

SELECT COUNT(*) FROM (SELECT ID , COLUMN1 FROM TABLE1 GROUP BY ID , COLUMN1 HAVINGCOUNT(*) = 1) T

[JiangHongTao]

参考：
http://topic.csdn.net/u/20080216/15/40dea9a3-636e-4208-a8d0-612375c3e19d.html

[JiangHongTao]

发错了

[zedan]

[Quote=引用 2 楼 wzy_love_sly 的回复:]
SQL codedeclare@tbtable(idint,colvarchar(1))insertinto@tbselect1,'a'insertinto@tbselect2,'b'insertinto@tbselect3,'c'insertinto@tbselect4,'d'insertinto@tbselect5,'a'insertinto@tbselect6,'b'selectsum(con)as'次数'from(selectcon=(selectcount(1)from@tbwherecol=t.colandid>t.id)from@tbt)t

次数
2
[/Quote]


次数 2 是有相同行的数量, 不是要求的: 不同行的数量

[xiaoliaoyun]

declare @tb table (id int,col varchar(1))
insert into @tb select 1,'a'
insert into @tb select 2,'b'
insert into @tb select 3,'c'
insert into @tb select 4,'d'
insert into @tb select 5,'a'
insert into @tb select 6,'b'


select count(1) from @tb a where not exists (select 1 from @tb b where a.id<>b.id and a.col = b.col)

[haonanernet]

declare @t table(id int ,column1 varchar(10) )
insert into @t
select
1, 'a' union all select
2, 'b' union all select
3, 'c' union all select
4, 'd' union all select
5, 'a' union all select
6, 'b'





select count(*) from @t t where column1 not in
(select column1 from @t group by column1 having count(*) >1)

[fuda_1985]

同意二楼的。

[肥胖的柠檬]

不用这烦,

declare @tb table (id int,col varchar(1))
insert into @tb select 1,'a'
insert into @tb select 2,'b'
insert into @tb select 3,'c'
insert into @tb select 4,'d'
insert into @tb select 5,'a'
insert into @tb select 6,'b'


select min(id),col from @tb group by col


这样就可以了,

[oolinyu]

学习了