这个语句应该如何接下去写?

WorkNoInfo(员工表)

Id    WorkNo   GroupId
1      701          78
2      302          24
3      702          78


tableC(工单表)

OrderNo     WorkNo  
20130925001  701
20130925002  701
20130925003  702
20130925004  302

我想列出701所在小组的所有员工的所有工单的数量，这个数量应该是3

我这边的sql语句是：

selct count(*) from  tableC where(后面的语句要继续写下去)

因为这是程序里面的限制，所以后面的语句只能从where开始写
请问这个语句应该如何接下去写？

[解决办法]

create table #WorkNoInfo(id int,workno varchar(50),groupid varchar(50))
 insert into #WorkNoInfo
select 1,701,78 union all
select 2,302,24 union all
select 3,702,78
go 
 
create table #tableC(OrderNo varchar(50),WorkNo varchar(50))
insert into #tableC
select 20130925001,701 union all
select 20130925002,701 union all
select 20130925003,702 union all
select 20130925004,302
go

select count(*) from #tableC where WorkNo 
in(select WorkNo from #WorkNoInfo where groupid=
( select groupid from #WorkNoInfo where workno='701')
)

[解决办法]

create table WorkNoInfo
(Id int, WorkNo int, GroupId int)

insert into WorkNoInfo
 select 1, 701, 78 union all
 select 2, 302, 24 union all
 select 3, 702, 78
 
create table tableC
(OrderNo varchar(16), WorkNo int)

insert into tableC
 select '20130925001', 701 union all
 select '20130925002', 701 union all
 select '20130925003', 702 union all
 select '20130925004', 302


select count(1) '工单数量'
 from tableC where WorkNo in
 (select WorkNo from WorkNoInfo where GroupId=
  (select top 1 GroupId from WorkNoInfo where WorkNo=701))

/*
工单数量
-----------
3

(1 row(s) affected)
*/

[解决办法]
方法2,

create table WorkNoInfo
(Id int, WorkNo int, GroupId int)

insert into WorkNoInfo
 select 1, 701, 78 union all
 select 2, 302, 24 union all
 select 3, 702, 78
 
create table tableC
(OrderNo varchar(16), WorkNo int)

insert into tableC
 select '20130925001', 701 union all
 select '20130925002', 701 union all
 select '20130925003', 702 union all
 select '20130925004', 302


select count(1) '工单数量'
 from tableC a
 inner join WorkNoInfo b on a.WorkNo=b.WorkNo
 where exists(select 1 from WorkNoInfo c 
              where c.WorkNo=701 and c.GroupId=b.GroupId)
 group by b.GroupId

/*
工单数量
-----------
3

(1 row(s) affected)
*/

[解决办法]

select count(*) from tableC where WorkNo 
in(select WorkNo from WorkNoInfo where groupid=
( select groupid from WorkNoInfo where workno='701')
)

[解决办法]
夜不能寐 整一句不绕弯子的sql
with WorkNoInfo(id,WorkNo,GroupId)
as(
select '1', '701', '78'
union
select '2', '302', '24'
union
select '3', '702', '78'
),

tableC(OrderNo,WorkNo)
as(

select '20130925001', '701'
union
select '20130925002', '701'
union
select '20130925003', '702'
union
select '20130925004', '302')

select count(*) from tableC left join WorkNoInfo on tableC.workno=WorkNoInfo.workno
group by groupid having sum(charindex(WorkNoInfo.workno,'701'))>0