---------------------------------------------------------------- -- Author :SQL77(只为思齐老) -- Date :2010-01-31 23:04:47 -- Version: -- Microsoft SQL Server 2000 - 8.00.194 (Intel X86) -- Aug 6 2000 00:57:48 -- Copyright (c) 1988-2000 Microsoft Corporation -- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:#TB if object_id('tempdb.dbo.#TB') is not null drop table #TB go create table #TB([id] int,[num1] varchar(2),[num2] varchar(2),[num3] varchar(2)) insert #TB select 1,'01','02','03' union all select 2,'02','11','09' union all select 3,'01','03','08' union all select 4,'06','04','05' --------------开始查询-------------------------- SELECT NUM1 FROM ( select ID,NUM1 from #TB UNION ALL select ID,NUM2 from #TB UNION ALL select ID,NUM3 from #TB )AS T GROUP BY NUM1 HAVING COUNT(1)>=2----------------结果---------------------------- /* (所影响的行数为 4 行)NUM1 ---- 01 02 03(所影响的行数为 3 行) */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-01-31 23:25:53 -- 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]([id] int,[num1] varchar(2),[num2] varchar(2),[num3] varchar(2)) insert [tb] select 1,'01','02','03' union all select 2,'02','11','09' union all select 3,'01','03','08' union all select 4,'06','04','05' --------------开始查询-------------------------- SELECT num1 FROM ( select ID,NUM1 from TB union all select ID,NUM2 from TB union all select ID,NUM3 from TB ) T GROUP BY NUM1 HAVING COUNT(1)>=2----------------结果---------------------------- /* num1 ---- 01 02 03(3 行受影响) */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-01-31 23:25:53 -- 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]([id] int,[num1] varchar(2),[num2] varchar(2),[num3] varchar(2)) insert [tb] select 1,'01','02','03' union all select 2,'02','11','09' union all select 3,'01','03','08' union all select 4,'06','04','05' --------------开始查询-------------------------- SELECT num1 FROM ( select ID,NUM1 from TB union all select ID,NUM2 from TB union all select ID,NUM3 from TB ) T GROUP BY NUM1 HAVING COUNT(1)>=2----------------结果---------------------------- /* num1 ---- 01 02 03(3 行受影响) */
HAVING COUNT(1)>=2里面那个1是什么意思
count(1)=count(*)只是一个记数的..
SELECT num1 FROM ( select ID,NUM1 from TB union all select ID,NUM2 from TB union all select ID,NUM3 from TB ) T GROUP BY NUM1 HAVING COUNT(1)>=2count(1) 中的1只是为了选取记录而制定的一个特别值,这里可以用任何常数,用*也可以
Appending:if object_id('tempdb.dbo.#TB') is not null drop table #TB go create table #TB([id] int,[num1] varchar(2),[num2] varchar(2),[num3] varchar(2)) insert #TB select 1,'01','02','03' union all select 2,'02','11','09' union all select 3,'01','03','08' union all select 4,'06','04','05' SELECT * FROM #TBSELECT * FROM ( SELECT num1 AS 'Num' FROM #TBUNION ALLSELECT num2 AS 'Num' FROM #TBUNION ALLSELECT num3 AS 'Num' FROM #TB )A GROUP BY A.Num HAVING COUNT(A.Num)> 1
-- Author :SQL77(只为思齐老)
-- Date :2010-01-31 23:04:47
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([id] int,[num1] varchar(2),[num2] varchar(2),[num3] varchar(2))
insert #TB
select 1,'01','02','03' union all
select 2,'02','11','09' union all
select 3,'01','03','08' union all
select 4,'06','04','05'
--------------开始查询--------------------------
SELECT NUM1 FROM
(
select ID,NUM1 from #TB
UNION ALL
select ID,NUM2 from #TB
UNION ALL
select ID,NUM3 from #TB
)AS T GROUP BY NUM1 HAVING COUNT(1)>=2----------------结果----------------------------
/* (所影响的行数为 4 行)NUM1
----
01
02
03(所影响的行数为 3 行)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-01-31 23:25:53
-- 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]([id] int,[num1] varchar(2),[num2] varchar(2),[num3] varchar(2))
insert [tb]
select 1,'01','02','03' union all
select 2,'02','11','09' union all
select 3,'01','03','08' union all
select 4,'06','04','05'
--------------开始查询--------------------------
SELECT
num1
FROM
(
select ID,NUM1 from TB
union all
select ID,NUM2 from TB
union all
select ID,NUM3 from TB
) T
GROUP BY
NUM1
HAVING
COUNT(1)>=2----------------结果----------------------------
/* num1
----
01
02
03(3 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-01-31 23:25:53
-- 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]([id] int,[num1] varchar(2),[num2] varchar(2),[num3] varchar(2))
insert [tb]
select 1,'01','02','03' union all
select 2,'02','11','09' union all
select 3,'01','03','08' union all
select 4,'06','04','05'
--------------开始查询--------------------------
SELECT
num1
FROM
(
select ID,NUM1 from TB
union all
select ID,NUM2 from TB
union all
select ID,NUM3 from TB
) T
GROUP BY
NUM1
HAVING
COUNT(1)>=2----------------结果----------------------------
/* num1
----
01
02
03(3 行受影响)
*/
COUNT(1)>=2里面那个1是什么意思
num1
FROM
(
select ID,NUM1 from TB
union all
select ID,NUM2 from TB
union all
select ID,NUM3 from TB
) T
GROUP BY
NUM1
HAVING
COUNT(1)>=2count(1) 中的1只是为了选取记录而制定的一个特别值,这里可以用任何常数,用*也可以
go
create table #TB([id] int,[num1] varchar(2),[num2] varchar(2),[num3] varchar(2))
insert #TB
select 1,'01','02','03' union all
select 2,'02','11','09' union all
select 3,'01','03','08' union all
select 4,'06','04','05'
SELECT * FROM #TBSELECT *
FROM
(
SELECT num1 AS 'Num' FROM #TBUNION ALLSELECT num2 AS 'Num' FROM #TBUNION ALLSELECT num3 AS 'Num' FROM #TB
)A
GROUP BY A.Num
HAVING COUNT(A.Num)> 1