WorkNoInfo(员工表)Id WorkNo GroupId
1 701 78
2 302 24
3 702 78
tableC(工单表)OrderNo WorkNo
20130925001 701
20130925002 701
20130925003 702
20130925004 302
20130925005 701
20130925006 302我想列出701所在小组的所有员工的所有工单最后列出的数据是这样的:OrderNo WorkNo GroupId
20130925001 701 78
20130925002 701 78
20130925003 702 78
20130925005 701 78请问这个语句应该怎么写?
(
select 1,701,78
union all select 2,302,24
union all select 3,702,78
),
tb(OrderNo,WorkNo) as
(
select 20130925001,701
union all select 20130925002,701
union all select 20130925003,702
union all select 20130925004,302
union all select 20130925005,701
union all select 20130925006,302
)
select a.*,b.GroupId
from tb a
inner join
(select WorkNo,GroupId
from ta
where GroupId in(select GroupId from ta where WorkNo='701')
)b on a.WorkNo=b.WorkNo
order by OrderNo/*
OrderNo WorkNo GroupId
20130925001 701 78
20130925002 701 78
20130925003 702 78
20130925005 701 78
*/
worknoInfo a join tblec b
on a.workno=b.workno
where a.groupid in(select groupid from worknoinfo
where workno='701')