34,576
社区成员
发帖
与我相关
我的任务
分享
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
select count(distinct(col) from tb
select 字段,count(*) 次数 from tb
group by 字段
--> 测试数据:[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 行)
?
count(1)>1
select count(x.a) as 列数1from table x
select count( distinct (x.a)) as 列数2 from table x
----------------------------------------------------------------
-- 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 行受影响)
*/