主键 任务编号 企业编号 范围编号
1 rw001 Qy001 1
2 rw001 Qy002 1
3 rw001 Qy002 2
4 rw001 Qy004 1
5 rw002 Qy005 1
6 rw002 Qy004 1
7 rw002 Qy004 1
8 rw002 Qy008 1
9 Rw003 Qy011 1
10 Rw003 Qy012 1
11 Rw003 Qy013 1
12 Rw003 Qy014 1求:通过sql语句查询出:“任务编号”相同“企业编号”相同“范围编号”不相同的“任务编号”和“企业编号”。
求出的结果应该是:rw001 qy002
1 rw001 Qy001 1
2 rw001 Qy002 1
3 rw001 Qy002 2
4 rw001 Qy004 1
5 rw002 Qy005 1
6 rw002 Qy004 1
7 rw002 Qy004 1
8 rw002 Qy008 1
9 Rw003 Qy011 1
10 Rw003 Qy012 1
11 Rw003 Qy013 1
12 Rw003 Qy014 1求:通过sql语句查询出:“任务编号”相同“企业编号”相同“范围编号”不相同的“任务编号”和“企业编号”。
求出的结果应该是:rw001 qy002
select 任务编号,企业编号 from 你的表 group by 任务编号,企业编号 having count(*)>1 and min(范围编号)<>max(范围编号)
group by 任务编号,企业编号 having count(distinct 范围编号
)>1
select 1 ID ,'rw001' rw,'Qy001' qy, 1 fw from dual union all
select 2 ,'rw001','Qy002', 1 from dual union all
select 3 ,'rw001','Qy002', 2 from dual union all
select 4 ,'rw001','Qy004', 1 from dual union all
select 5 ,'rw002','Qy005', 1 from dual union all
select 6 ,'rw002','Qy004', 1 from dual union all
select 7 ,'rw002','Qy004', 1 from dual union all
select 8 ,'rw002','Qy008', 1 from dual union all
select 9 ,'Rw003','Qy011', 1 from dual union all
select 10,'Rw003','Qy012', 1 from dual union all
select 11,'Rw003','Qy013', 1 from dual union ALL
select 111,'Rw003','Qy013', 1 from dual union all
select 12,'Rw003','Qy014', 1 from dual )
SELECT rw,qy
FROM t t
GROUP BY rw,qy
HAVING COUNT(1)>1 AND NOT EXISTS (SELECT 1
FROM t a
GROUP BY rw,qy,fw
HAVING COUNT(1)>1 AND a.rw=t.rw)
比如你这个表叫:AAA
SQL语句:
select distinct(e1.任务编号),e1.企业编号 from AAA e1,AAA e2
where e1.任务编号=e2.任务编号
and e1.企业编号=e2.企业编号
and e1.范围编号<>e2.范围编号查询结果:rw001 qy002
[/Quote]
where e1.任务编号=e2.任务编号
and e1.企业编号=e2.企业编号
and e1.范围编号<>e2.范围编号select 任务编号,企业编号 from 你的表 group by 任务编号,企业编号 having count(*)>1 and min(范围编号)<>max(范围编号)