我现在有张表,结构如下
khh bz
0001 人民币
0002 人民币
0003 人民币
0003 美金
0004 美金
0004 港币
0005 人民币
0005 港币
0006 人民币
0006 美金
0006 港币
0007 人民币
0008 港币
请教一下大家怎么用sql语句查询到只有人民币的客户、只有美金的客户、有美金和港币的客户、有美金港币和人民币的客户,谢谢。
khh bz
0001 人民币
0002 人民币
0003 人民币
0003 美金
0004 美金
0004 港币
0005 人民币
0005 港币
0006 人民币
0006 美金
0006 港币
0007 人民币
0008 港币
请教一下大家怎么用sql语句查询到只有人民币的客户、只有美金的客户、有美金和港币的客户、有美金港币和人民币的客户,谢谢。
select distinct khh from tb1 where bz='人民币' and khh not in (
select khh from tb1 where bz<>'人民币')
、只有美金的客户、
select distinct khh from tb1 where bz='美金' and khh not in (
select khh from tb1 where bz<>'美金')
有美金和港币的客户、
select distinct khh from tb1 where bz='美金' and khh in (
select khh from tb1 where bz='港币')
有美金港币和人民币的客户select distinct khh from tb1 where bz='美金' and khh in (
select khh from tb1 where bz='港币')
and khh in (
select khh from tb1 where bz='人民币')
select khh from tablename having count(*)=1;
select khh from table1 as a where not exists(select 1 from table1 where khh=a.khh and bz<>'美金') group by khh
select khh from table1 as a where not exists(select 1 from table1 where khh=a.khh and bz not in('美金','人民币')) group by khh having count(distinct bz)=2
select khh from table1 as a where not exists(select 1 from table1 where khh=a.khh and bz not in('美金','人民币','港币'))group by khh having count(distinct bz)=3
insert into tb select '0001','人民币'
insert into tb select '0002','人民币'
insert into tb select '0003','人民币'
insert into tb select '0003','港币'
insert into tb select '0004','美金'
insert into tb select '0004','港币'
insert into tb select '0005','人民币'
insert into tb select '0005','美金'
insert into tb select '0006','人民币'
insert into tb select '0006','美金'
insert into tb select '0006','港币'
insert into tb select '0007','美金'
insert into tb select '0008','港币'
go--只有人民币的客户
select distinct khh from tb where khh not in (select distinct khh from tb where bz <> '人民币')
/*
khh
----------
0001
0002(所影响的行数为 2 行)
*/--只有美金的客户
select distinct khh from tb where khh not in (select distinct khh from tb where bz <> '美金')
/*
khh
----------
0007(所影响的行数为 1 行)
*/--只有港币的客户
select distinct khh from tb where khh not in (select distinct khh from tb where bz <> '港币')
/*
khh
----------
0008(所影响的行数为 1 行)
*/--有美金港币的客户,应该是只有也必须有美金港币的客户吧?
select khh from tb where khh in
(
select khh from
(
select distinct khh from tb where bz = '美金'
union all
select distinct khh from tb where bz = '港币'
) t group by khh having count(1) = 2
)
group by khh having count(1) = 2
/*
khh
----------
0004(所影响的行数为 1 行)
*/--有人民币美金港币的客户,应该是只有也必须人民币美金港币的客户吧?
select khh from tb where khh in
(
select khh from
(
select distinct khh from tb where bz = '美金'
union all
select distinct khh from tb where bz = '港币'
union all
select distinct khh from tb where bz = '人民币'
) t group by khh having count(1) = 3
)
group by khh having count(1) = 3
/*
khh
----------
0006(所影响的行数为 1 行)
*/drop table tb
if object_id('dbo.tb') is not null drop table tb
create table tb(khh nvarchar(10),bz nvarchar(10))
insert into tb select '0001','人民币'
insert into tb select '0002','人民币'
insert into tb select '0003','人民币'
insert into tb select '0003','港币'
insert into tb select '0004','美金'
insert into tb select '0004','港币'
insert into tb select '0005','人民币'
insert into tb select '0005','美金'
insert into tb select '0006','人民币'
insert into tb select '0006','美金'
insert into tb select '0006','港币'
insert into tb select '0007','美金'
insert into tb select '0008','港币'create function dbo.fn_bzclass(@khh varchar(8000))
returns varchar(8000)
as
begin
declare @i varchar(8000)
set @i=''
select @i=@i+','+bz from tb where khh=''+@khh+''
return stuff(@i,1,1,'')
endselect khh,bz=dbo.fn_bzclass(khh) from tb group by khh
drop table tb0001
人民币 0002
人民币 0003
人民币,港币 0004
美金,港币 0005
人民币,美金 0006
人民币,美金,港币 0007
美金 0008
港币
if object_id('dbo.tb') is not null drop table tb
create table tb(khh nvarchar(10),bz nvarchar(10))
insert into tb select '0001','人民币'
insert into tb select '0002','人民币'
insert into tb select '0003','人民币'
insert into tb select '0003','港币'
insert into tb select '0004','美金'
insert into tb select '0004','港币'
insert into tb select '0005','人民币'
insert into tb select '0005','美金'
insert into tb select '0006','人民币'
insert into tb select '0006','美金'
insert into tb select '0006','港币'
insert into tb select '0007','美金'
insert into tb select '0008','港币'create function dbo.fn_bzclass(@khh varchar(8000))
returns varchar(8000)
as
begin
declare @i varchar(8000)
set @i=''
select @i=@i+','+bz from tb where khh=''+@khh+''
return stuff(@i,1,1,'')
endselect bz=(
case when khh=(
select top 1 khh from (select khh,bz=dbo.fn_bzclass(khh) from tb group by khh) as tt
where bz=tz.bz) then bz else '' end
),khh from (
select khh,bz=dbo.fn_bzclass(khh) from tb group by khh
) as tzdrop table tb
输出结果如下:人民币 0001
0002
人民币,港币 0003
美金,港币 0004
人民币,美金 0005
人民币,美金,港币 0006
美金 0007
港币 0008