表Aname group code type
A111 1 A22 1
A111 1 22 2
A111 1 33 2
A111 1 A22 1
A111 2 B22 1
A111 2 12 2
A111 3 12 2
A111 3 12 2
B111 1 B22 1
B111 1 22 2
B111 1 33 2
B111 2 C22 1
B111 2 B22 1
B111 2 12 2
C111 1 12 2
C111 1 43 2
结果是
name group code type
A111 1 A22 1
A111 1 22 2
A111 1 33 2
A111 1 A22 1
A111 3 12 2
A111 3 12 2
B111 1 B22 1
B111 1 22 2
B111 1 33 2
C111 1 12 2
C111 1 43 2
条件是按照 name 和gourp 分组 时 取一组内 如果 type =1 时 不存在 left(name,1) <> left(code,1)
A111 1 A22 1
A111 1 22 2
A111 1 33 2
A111 1 A22 1
A111 2 B22 1
A111 2 12 2
A111 3 12 2
A111 3 12 2
B111 1 B22 1
B111 1 22 2
B111 1 33 2
B111 2 C22 1
B111 2 B22 1
B111 2 12 2
C111 1 12 2
C111 1 43 2
结果是
name group code type
A111 1 A22 1
A111 1 22 2
A111 1 33 2
A111 1 A22 1
A111 3 12 2
A111 3 12 2
B111 1 B22 1
B111 1 22 2
B111 1 33 2
C111 1 12 2
C111 1 43 2
条件是按照 name 和gourp 分组 时 取一组内 如果 type =1 时 不存在 left(name,1) <> left(code,1)
Select a.* from tb as a
inner join (Select distinct name,[group] from tb where type=1 and left(name,1)=left(code,1)) as b
on b.name=a.name and b.[group]=a.[group]
from 表A A
where exists(select 1 from 表A where name=A.name and group=A.group and left(name,1) = left(code,1))
select * from 表A where type =2
UNION all
select * from 表A where type =1 and left(name,1) =left(code,1)) a group by a.name,a.gourp
select name, group, code, type
from 表A A
where not exists(select 1 from 表A where name=A.name and group=A.group and left(name,1) <> left(code,1) and type = 1)
where not exists(Select * from
(Select name,[group] from tb where type=1 and
left(name,1)<>left(code,1) group by name,[group]) as b
Where b.name=a.name and b.[group]=a.[group])
Select a.* from tb as a
where not exists(Select * from
(Select name,[group] from tb where type=1 and
left(name,1)<>left(code,1) group by name,[group]) as b
Where b.name=a.name and b.[group]=a.[group])
--try to
Select a.* from tb as a
inner join (Select distinct name,[group],code from tb where type=1 and left(name,1)=left(code,1)) as b
on b.name=a.name and b.[group]=a.[group] and a.code =b.code
group by a.name,a.gourp
改了‘先发’的东西,我觉得这样比较合适一点!
from 表A A
where not exists(select 1 from 表A where name=A.name and group=A.group and left(name,1) <> left(code,1) and type = 1)
这个写法已经考虑到你说的那中情况了, 只存在type =2 就被通过了
select * from 表 a where not exists(Select 1 from 表 b where a.name = b.name and a.[group] = b.[group] and type = 1 and left(a.name,1) <> left(b.code,1))
A111 1 A22 1
B111 1 B22 1
A111 1 A22 1
这样的结果是不是有问题