--这是重复的. select phone from a where phone is not null group by phone having count(1) > 1 union all select Mobile from a where phone is not null group by Mobile having count(1) > 1--这是不重复的. select phone from a where phone is not null group by phone having count(1) =1 union all select Mobile from a where phone is not null group by Mobile having count(1) = 1
select distinct Phone from( select Phone from ta union all select Mobile from ta)t
select distinct mo from ( select Phone as mo from ta union all select Mobile as mo from ta ) tab
select distinct Phone from( select Phone from ta union all select Mobile from ta)t where phone is not null
select mo from ( select Phone as mo from ta union select Mobile as mo from ta ) tab直接用UNION
select phone from a where phone is not null group by phone having count(1) =1 union all select Mobile from a where phone is not null group by Mobile having count(1) = 1
select distinct Phone from ( select Phone from ta union all select Mobile from ta)t where phone is not null
--这是重复的. select phone from a where phone is not null group by phone having count(1) > 1 union all select Mobile from a where phone is not null group by Mobile having count(1) > 1 --这是不重复的. select phone from a where phone is not null group by phone having count(1) =1 union all select Mobile from a where Mobile is not null group by Mobile having count(1) = 1 如果是查phone,Mobile 同时不重复 select phone from ( select phone from a where phone is not null union all select Mobile from a where Mobile is not null ) t group by phone having count(1) = 1
select Phone,Mobile from tb group by Phone,Mobile
另外我想统计两者都一样的的SQL怎么做? Select Count(Distinct Phone,Distinct Mobile) From A?
嗯 好的 我试试 另外is not null 和我写的!=Null是等价的吗?
is not null 这个是正确的
-->Title:Generating test data -->Author:wufeng4552 -->Date :2009-10-19 16:43:08
if not object_id('tb') is null drop table tb Go Create table tb([Phone] int,[Mobile] bigint) Insert tb select 1234567,13111111111 union all select 1234567,13222222222 union all select 1234567,13333333333 union all select 7654321,13333333333 Go select [Phone] from tb union select [Mobile] from tb /* Phone -------------------- 1234567 7654321 13111111111 13222222222 13333333333*/
select max(mt) mt_max from (select count(distinct(Phone)) mt from tb union select count(distinct(Mobile)) from tb) t;
if not object_id('tb') is null drop table tb Go Create table tb([Phone] int,[Mobile] bigint) Insert tb select 1234567,13111111111 union all select 1234567,13222222222 union all select 1234567,13333333333 union all select 7654321,13333333333 Goselect max(mt) mt_max from (select count(distinct(Phone)) mt from tb union select count(distinct(Mobile)) from tb) t;
--你的描述让我们回答起来好辛苦.Create table tb([Phone] int,[Mobile] bigint) Insert tb select 1234567,13111111111 union all select 1234567,13222222222 union all select 1234567,13333333333 union all select 7654321,13333333333--如果是3,就是查Mobile不重复? select count(distinct Mobile) cnt from tb where Mobile is not null /* cnt ----------- 3(所影响的行数为 1 行) */--查不重复的phone select distinct Phone from tb where Phone is not null /* Phone ----------- 1234567 7654321(所影响的行数为 2 行) */--查只有一个的phone,重复的不要 select Phone from tb where Phone is not null group by phone having count(1) = 1 /* Phone ----------- 7654321(所影响的行数为 1 行) */--如果是查Mobile,和上面一样。--如果是查Mobile,phone中不存在重复的,使用union allselect phone from ( select Phone from tb where Phone is not null group by phone having count(1) = 1 union all select Mobile from tb where Mobile is not null group by Mobile having count(1) = 1 ) t group by phone having count(1) = 1 /* phone -------------------- 7654321 13111111111 13222222222(所影响的行数为 3 行) */--如果是查Mobile,phone中不存在重复的数量 select count(1) cnt from ( select Phone from ( select Phone from tb where Phone is not null group by phone having count(1) = 1 union all select Mobile from tb where Mobile is not null group by Mobile having count(1) = 1 ) t group by phone having count(1) = 1 ) m /* cnt ----------- 3(所影响的行数为 1 行) */drop table tb
union
select Mobile from ta
select phone from a where phone is not null group by phone having count(1) > 1
union all
select Mobile from a where phone is not null group by Mobile having count(1) > 1--这是不重复的.
select phone from a where phone is not null group by phone having count(1) =1
union all
select Mobile from a where phone is not null group by Mobile having count(1) = 1
select Phone from ta
union all
select Mobile from ta)t
(
select Phone as mo from ta
union all
select Mobile as mo from ta
) tab
(
select Phone as mo from ta
union
select Mobile as mo from ta
) tab直接用UNION
select phone from a where phone is not null group by phone having count(1) =1
union all
select Mobile from a where phone is not null group by Mobile having count(1) = 1
比如
Phone Mobile
1234567 13111111111
1234567 13222222222
1234567 13333333333
7654321 13333333333
...
结果是 3
而不是 Phone 2 + Mobile 3 = 5
distinct Phone
from
( select Phone from ta union all select Mobile from ta)t
where
phone is not null
select phone from a where phone is not null group by phone having count(1) > 1
union all
select Mobile from a where phone is not null group by Mobile having count(1) > 1 --这是不重复的.
select phone from a where phone is not null group by phone having count(1) =1
union all
select Mobile from a where Mobile is not null group by Mobile having count(1) = 1
如果是查phone,Mobile 同时不重复
select phone from
(
select phone from a where phone is not null
union all
select Mobile from a where Mobile is not null
) t
group by phone having count(1) = 1
Select Count(Distinct Phone,Distinct Mobile) From A?
另外is not null 和我写的!=Null是等价的吗?
-->Author:wufeng4552
-->Date :2009-10-19 16:43:08
if not object_id('tb') is null
drop table tb
Go
Create table tb([Phone] int,[Mobile] bigint)
Insert tb
select 1234567,13111111111 union all
select 1234567,13222222222 union all
select 1234567,13333333333 union all
select 7654321,13333333333
Go
select [Phone] from tb
union
select [Mobile] from tb
/*
Phone
--------------------
1234567
7654321
13111111111
13222222222
13333333333*/
比如
Phone Mobile
1234567 13111111111
1234567 13222222222
1234567 13333333333
7654321 13333333333
...
结果是 3 ---这个是我想要的结果 而不是 Phone 2 + Mobile 3 = 5
from (select count(distinct(Phone)) mt from tb
union select count(distinct(Mobile)) from tb) t;
if not object_id('tb') is null
drop table tb
Go
Create table tb([Phone] int,[Mobile] bigint)
Insert tb
select 1234567,13111111111 union all
select 1234567,13222222222 union all
select 1234567,13333333333 union all
select 7654321,13333333333
Goselect max(mt) mt_max
from (select count(distinct(Phone)) mt from tb
union select count(distinct(Mobile)) from tb) t;
Insert tb
select 1234567,13111111111 union all
select 1234567,13222222222 union all
select 1234567,13333333333 union all
select 7654321,13333333333--如果是3,就是查Mobile不重复?
select count(distinct Mobile) cnt from tb where Mobile is not null
/*
cnt
-----------
3(所影响的行数为 1 行)
*/--查不重复的phone
select distinct Phone from tb where Phone is not null
/*
Phone
-----------
1234567
7654321(所影响的行数为 2 行)
*/--查只有一个的phone,重复的不要
select Phone from tb where Phone is not null group by phone having count(1) = 1
/*
Phone
-----------
7654321(所影响的行数为 1 行)
*/--如果是查Mobile,和上面一样。--如果是查Mobile,phone中不存在重复的,使用union allselect phone from
(
select Phone from tb where Phone is not null group by phone having count(1) = 1
union all
select Mobile from tb where Mobile is not null group by Mobile having count(1) = 1
) t
group by phone having count(1) = 1
/*
phone
--------------------
7654321
13111111111
13222222222(所影响的行数为 3 行)
*/--如果是查Mobile,phone中不存在重复的数量
select count(1) cnt from
(
select Phone from
(
select Phone from tb where Phone is not null group by phone having count(1) = 1
union all
select Mobile from tb where Mobile is not null group by Mobile having count(1) = 1
) t
group by phone having count(1) = 1
) m
/*
cnt
-----------
3(所影响的行数为 1 行)
*/drop table tb