1\select OrgName,count(1) as [count]
from Organization
group by OrgName
having count(1) > 02\
select OrganizationID,count(1) as [count]
from Organization
group by OrganizationID
having count(1) > 0
from Organization
group by OrgName
having count(1) > 02\
select OrganizationID,count(1) as [count]
from Organization
group by OrganizationID
having count(1) > 0
1)OrganizationID不同,但OrgName相同的记录有哪些?
select distinct a.* from Organization a inner join Organization b on a.OrgName=b.OrgName and a.OrganizationID<>b.OrganizationID
2)OrganizationID相同,但OrgName不同的记录有哪些?
select distinct a.* from Organization a inner join Organization b on a.OrgName<>b.OrgName and a.OrganizationID=b.OrganizationID
insert into @tb select 1,'c'
insert into @tb select 2,'b'
insert into @tb select 1,'a'
insert into @tb select 4,'c'
insert into @tb select 5,'a'
--1
select * from @tb a where exists(select 1 from @tb where OrgName=a.OrgName and OrganizationID<>a.OrganizationID)
--2
select * from @tb a where exists(select 1 from @tb where OrganizationID=a.OrganizationID and OrgName<>a.OrgName)
--3等等
insert into @tb select 1,'c'
insert into @tb select 2,'b'
insert into @tb select 1,'a'
insert into @tb select 4,'c'
insert into @tb select 5,'a'
--1
select t1.* from @tb t1
left join(
select * from @tb a where exists(select 1 from @tb where OrgName=a.OrgName and OrganizationID<>a.OrganizationID)
union
select * from @tb a where exists(select 1 from @tb where OrganizationID=a.OrganizationID and OrgName<>a.OrgName)
) t2 on t1.OrganizationID =t2.OrganizationID where t2.OrganizationID is nullOrganizationID orgname
2 b
insert into @tb select 1,'c'
insert into @tb select 2,'b'
insert into @tb select 1,'a'
insert into @tb select 4,'c'
insert into @tb select 5,'a'select a.* from @tb a ,@tb b
where a.OrganizationID <> b.OrganizationID and a.orgname = b.orgname子句换,但都是笨方法,一定有简单的
insert into @tb select 1,'c'
insert into @tb select 2,'b'
insert into @tb select 1,'a'
insert into @tb select 4,'c'
insert into @tb select 5,'a'select OrgName,count(1) as [count]
from @tb
group by OrgName
having count(1) > 1 /*
OrgName count
------- -----------
a 2
c 2(所影响的行数为 2 行)
*/select OrganizationID,count(1) as [count]
from @tb
group by OrganizationID
having count(1) > 1
/*OrganizationID count
-------------- -----------
1 2(所影响的行数为 1 行)*/
select *
from @tb
where orgname not in(
select orgname
from @tb a
where
exists(select 1 from @tb where a.OrganizationID = OrganizationID and orgname > a.orgname)
or exists(select 1 from @tb where a.OrganizationID < OrganizationID and orgname = a.orgname))
/*
OrganizationID orgname
-------------- -------
2 b(所影响的行数为 1 行)*/
(select OrgName from Organization
group by OrgName
having count (distinct OrganizationID) > 1) o2
on o1.OrgName = o2.OrgName2> 类同1>
3> 待解
union也不行,好像只要写连接的
不用子查询,感觉好像做不到。
insert into @tb select 1,'c'
insert into @tb select 2,'b'
insert into @tb select 1,'a'
insert into @tb select 4,'c'
insert into @tb select 5,'a'
insert into @tb select 6,'d'
select a.* from @tb a,@tb b where a.organizationID = b.OrganizationID or a.orgname = b.orgname
group by a.OrganizationID,a.orgname having count(*) = 1
/*
OrganizationID orgname
-------------- -------
2 b
6 d
*/