请教个sql语句

[pgdoryoku]

我想查询下一个表中的某字段是否有重复值，sql该怎么写呢？
谢谢

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-11-07 14:30:22
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] int)
insert [tb]
select 1 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6
--------------开始查询--------------------------

select col from [tb] group by col having count(1)>1
----------------结果----------------------------
/* col
-----------
1

(1 行受影响)
*/

[--小F--]

1楼的这样查的话可以说明字段中1是重复值

[sinpoal]

select count(x.a) as 列数1from table x

select  count( distinct (x.a)) as 列数2 from table x

--------------
列数1与列数2 不相等时 table 中有重复字段。

[fhjzgsy]

count(1)>1

是什么意思啊

[daiyueqiang2045]

--colName 列名
select distinct t.colName from tb t,tb t1
where t.主键<>t1.主键 and t.colName=t1.colName

[daiyueqiang2045]

[Quote=引用 5 楼 daiyueqiang 的回复:]
--colName 列名
select distinct t.colName from tb t,tb t1
where t.主键 <>t1.主键 and t.colName=t1.colName
[/Quote]

这个查询出来的是重复的属性值

--colName 列名
select count( t.colName) from tb t,tb t1
where t.主键<>t1.主键 and t.colName=t1.colName
当返回数大于0时则此字段存在重复值

[daiyueqiang2045]

[Quote=引用 4 楼 fhjzgsy 的回复:]
SQL codecount(1)>1
是什么意思啊
[/Quote]

是啊 我运行
select col from [tb] group by col having count(0)>1
和
select col from [tb] group by col having count(col)>1
结果和你运行的一样

count(1)>1 中count中的1不知道是什么意思

[小宏]

[Quote=引用 4 楼 fhjzgsy 的回复:]
SQL codecount(1)>1
是什么意思啊
[/Quote]
获取满足条件的行数
所以用1，0效果一样

[SQL77]

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col] int,NUM INT)
insert [tb]
select 1,1 union all
select 1,2 union all
select 2,2 union all
select 3,3 union all
select 4,7 union all
select 5,10 union all
select 6,12

SELECT * FROM TB T WHERE EXISTS(SELECT 1 FROM TB WHERE COL=T.COL AND NUM<>T.NUM)

（所影响的行数为 7 行）

col         NUM         
----------- ----------- 
1           1
1           2

（所影响的行数为 2 行）

?
是哪样重复,如果是全部重复用GROUP BY 方便

[ChinaJiaBing]

我想查询下一个表中的某字段是否有重复值，sql该怎么写呢？
谢谢

select 字段,count(*)　次数 from tb
    group by 字段

[chkaka]

select 所有列名,count(*)　次数 from tb
group by 所有列名

[Rotel-刘志东]

select count(distinct(col) from tb

[aaajedll]

IF(OBJECT_ID('TEMPDB..#T') IS NOT NULL)
BEGIN
DROP TABLE #T
END
CREATE TABLE #T
(
	[CODE] NVARCHAR(100)
)
INSERT INTO #T SELECT [CODE]='..a..'
INSERT INTO #T SELECT [CODE]='..a..'
INSERT INTO #T SELECT [CODE]='..b..'
INSERT INTO #T SELECT [CODE]='..b..'
INSERT INTO #T SELECT [CODE]='..b..'
INSERT INTO #T SELECT [CODE]='..c..'
INSERT INTO #T SELECT [CODE]='..c..'
INSERT INTO #T SELECT [CODE]='..c..'
INSERT INTO #T SELECT [CODE]='..c..'



SELECT [CODE],COUNT(*) AS [COUNT] FROM #T GROUP BY [CODE] HAVING(COUNT(*)>1)

-----------------------------
CODE  COUNT
..a..	2
..b..	3
..c..	4