令人头痛的sql问题

[yeefa]

有一张表 Customer

ID CustomerName CompanyName Address
------------------------------------------------
1 A CC A1
2 A CC A12
3 B BB B1
4 B BB B1
5 B DD B2
6 C DD C1
7 D FF D1
8 F DD F1
......
------------------------------------------------
要求找出同一个CustomerName（CustomerName可能会有重复的名称）,同一个公司名 不同地址的数据列表
ID CustomerName CompanyName Address
---------------------------------------------
.....

在线等

[sdhdy]

select  * from Customer a where not exists(select 1 from Customer where CustomerName=a.CustomerName
and id<>a.id and (CompanyName<>a.CompanyName or (CompanyName=a.CompanyName and  Address=a.Address))

[ai_li7758521]

给出结果。

[sdhdy]

declare @Customer table(ID int,   CustomerName varchar(5),  CompanyName varchar(5),  Address varchar(5) )

insert @Customer select 1   ,     'A' ,                 'CC' ,      'A1' 
insert @Customer select 2    ,    'A' ,                 'CC'  ,      'A12' 
insert @Customer select 3    ,    'B' ,                 'BB'  ,      'B1' 
insert @Customer select 4    ,    'B' ,                 'BB' ,       'B1' 
insert @Customer select 5    ,    'B'  ,                'DD'  ,      'B2' 
insert @Customer select 6    ,    'C'  ,                'DD'  ,      'C1' 
insert @Customer select 7     ,   'D'  ,                'FF' ,       'D1' 
insert @Customer select 8     ,   'F'  ,                'DD' ,       'F1' 

-- 要求找出同一个CustomerName（CustomerName可能会有重复的名称）,同一个公司名  不同地址的数据列表 
-- ID    CustomerName  CompanyName  Address 
-- --------------------------------------------- 

select  * from @Customer a where not exists(select 1 from @Customer where CustomerName=a.CustomerName
and id<>a.id and (CompanyName<>a.CompanyName or (CompanyName=a.CompanyName and  Address=a.Address)))

/*
ID          CustomerName CompanyName Address 
----------- ------------ ----------- ------- 
1           A            CC          A1
2           A            CC          A12
6           C            DD          C1
7           D            FF          D1
8           F            DD          F1

（所影响的行数为 5 行）
*/

[让你望见影子的墙]

select s.* from tb where Customer s,Customer t where s.CustomerName=t.CustomerName and s.CompanyName=t.CompanyName and s.address<>t.address

[ks_reny]

[Quote=引用 1 楼 josy 的回复:]
SQL codeselect
*
from
Customer t
where
exists(select 1 from Customer where CustomerName=t.CustomerName and Address!=t.Address)
[/Quote]
顶树人的。

[yingzhilian2008]

[Quote=引用 3 楼 sdhdy 的回复:]
先贴出你要的结果，不要让大家去猜。
[/Quote]
同意

[claro]

create table Customer(ID int,CustomerName varchar(20),CompanyName varchar(20),Address varchar(20))
GO
insert Customer select 1,'A','CC','A1'
insert Customer select 2,'A','CC','A12'
insert Customer select 3,'B','BB','B1'
insert Customer select 4,'B','BB','B1'
insert Customer select 5,'B','DD','B2'
insert Customer select 6,'C','DD','C1'
insert Customer select 7,'D','FF','D1'
insert Customer select 8,'F','DD','F1'

--嵌套循环
select * from Customer a
where exists (select 1 from Customer where a.CompanyName=CompanyName and a.CustomerName=CustomerName
and a.Address!=Address)
/*
ID	CustomerName	CompanyName	Address
1	A	CC	A1
2	A	CC	
*/
select * from Customer a
where exists (select 1 from Customer where a.CompanyName=CompanyName and a.CustomerName=CustomerName
and a.Address<>Address)
/*
ID	CustomerName	CompanyName	Address
1	A	CC	A1
2	A	CC	A12
*/
--合并联接
select a.*
from Customer a
join (
select CustomerName,CompanyName 
from Customer 
group by CustomerName,CompanyName
having COUNT(distinct Address) > 1
) b on a.CompanyName=b.CompanyName and a.CustomerName=b.CustomerName
/*
ID	CustomerName	CompanyName	Address
1	A	CC	A1
2	A	CC	A12
*/

[anloveddc]

up

[smilelhh]

你的意思应该是取出：
ID CustomerName CompanyName Address
------------------------------------------------
1 A CC A1
2 A CC A12
这么二条记录吧？同名，同公司，但地址不同。借一个楼上的数据！

declare @Customer table(ID int, CustomerName varchar(5), CompanyName varchar(5), Address varchar(5) )

insert @Customer select 1 , 'A' , 'CC' , 'A1'
insert @Customer select 2 , 'A' , 'CC' , 'A12'
insert @Customer select 3 , 'B' , 'BB' , 'B1'
insert @Customer select 4 , 'B' , 'BB' , 'B1'
insert @Customer select 5 , 'B' , 'DD' , 'B2'
insert @Customer select 6 , 'C' , 'DD' , 'C1'
insert @Customer select 7 , 'D' , 'FF' , 'D1'
insert @Customer select 8 , 'F' , 'DD' , 'F1'


select a.* from @Customer a, @Customer b where a.CustomerName=b.CustomerName and
a.CompanyName = b.CompanyName and a.Address<> b.Address

[ChinaJiaBing]

declare @customer table (id int ,customername nvarchar(10),companyname nvarchar(10),
   address nvarchar(10))
insert into @customer select 1,'a','cc','a1'
            union all select 2,'a','cc','a12'
            union all select 3,'b','bb','b1'
            union all select 4,'b','bb','b1'
            union all select 5,'b','dd','b2'
            union all select 6,'c','dd','c1'
            union all select 7,'d','ff','d1'
            union all select 8,'f','dd','f1'
select customername,companyname,address from @customer
       group by customername,companyname,address

[langziqian]

帮顶

[Tomzzu]

找出同一CustomerName, 同一CompanyName, 不同地址(即多个地址)的数据

select CustomerName, CompanyName
	from Customer
	group by CustomerName, CompanyName
	having count(1) > 1

[yonghenghxq]

select * from Customer where Address not in (select Address from Customer C where CustomerName=C.CustomerName and CompanyName=C.CompanyName)

[fuhaojie626]

这个语句主要条件是地址不相同，其它的不用管。

[-狙击手-]

create table Customer(ID int,CustomerName varchar(20),CompanyName varchar(20),Address varchar(20))
GO
insert Customer select 1,'A','CC','A1'
insert Customer select 2,'A','CC','A12'
insert Customer select 3,'B','BB','B1'
insert Customer select 4,'B','BB','B1'
insert Customer select 5,'B','DD','B2'
insert Customer select 6,'C','DD','C1'
insert Customer select 7,'D','FF','D1'
insert Customer select 8,'F','DD','F1'
go


SELECT
  *
FROM
  CUSTOMER T
WHERE
  EXISTS(SELECT 1 
         FROM CUSTOMER 
         WHERE COMPANYNAME = T.COMPANYNAME AND CUSTOMERNAME=T.CUSTOMERNAME AND ADDRESS!=T.ADDRESS)


drop table Customer

/*
ID          CustomerName         CompanyName          Address              
----------- -------------------- -------------------- -------------------- 
1           A                    CC                   A1
2           A                    CC                   A12

（所影响的行数为 2 行）

*/

[wendy_fw]

用一個簡單的思路就可實現, 無需那麼複雜:
CustomerName CompanyName 一起作為一個關鍵字,假設叫KeyCC,(可想像成一個欄位)
用Group by KeyCC的方法數出distinct Address,

簡單吧... :)

[cch1010]

帮顶

[jinlingoo1]

declare @Customer table (ID int,CustomerName varchar(20),CompanyName varchar(20),Address varchar(20))
insert @Customer select 1,'A','CC','A1'
insert @Customer select 2,'A','CC','A12'
insert @Customer select 3,'B','BB','B1'
insert @Customer select 4,'B','BB','B1'
insert @Customer select 5,'B','DD','B2'
insert @Customer select 6,'C','DD','C1'
insert @Customer select 7,'D','FF','D1'
insert @Customer select 8,'F','DD','F1'

select * from @Customer A where exists(select * from @Customer where CompanyName=A.CompanyName and Address<>A.Address )


ID CustomerName CompanyName Address
----------- -------------------- -------------------- --------------------
1 A CC A1
2 A CC A12
5 B DD B2
6 C DD C1
8 F DD F1

[feixianxxx]

[Quote=引用 19 楼 happyflystone 的回复:]
SQL codecreate table Customer(ID int,CustomerName varchar(20),CompanyName varchar(20),Address varchar(20))
GO
insert Customer select 1,'A','CC','A1'
insert Customer select 2,'A','CC','A12'
insert Customer select 3,'B','BB','B1'
insert Customer select 4,'B','BB','B1'
insert Customer select 5,'B','DD','B2'
insert Customer select 6,'C','DD','C1'
insert Customer select 7,'D','FF','D1'
inser…
[/Quote]

Up

[等不到来世]

select id=row_number() over(order by CustomerName,CompanyName)
	,CustomerName,CompanyName
	,Address=stuff((select ''+Address from Customer where CustomerName=t.CustomerName and CompanyName=t.CompanyName for xml path('')),1,1,'')
from Customer t
group by CustomerName,CompanyName