select comid, jobid,(isnull(cast((select count(*) from TABLE1 where TABLE1.jobid=TABLE2.jobid) as varchar (10)),'0') + '封/' +isnull(cast((select sum(cast(new as int)) from TABLE1 where TABLE1.jobid=TABLE2.jobid)as varchar(10)),'0') + '封新')
from TABLE2 where comid=303
group by comid, jobid
from TABLE2 where comid=303
group by comid, jobid
b.comid,
a.jobid,
[count]=N'('+rtrim(count(a.jobid))
+N'/'+rtrim(sum(case when a.new=1 then 1 else 0 end))
+N'封新)'
from table1 a, table2 b
where a.jobid=b.jobid
group by b.comid,a.jobid
(
jobid int,new bit
)
create table table2
(
jobid int,comid int
)
insert table1
select 1,1 union all
select 2,1 union all
select 1,1 union all
select 5,0 union all
select 1,0
insert table2
select 1,303 union
select 2,303 union
select 3,303 union
select 4,303 union
select 5,304--查询
select A.comid
,A.jobid
,('('
+convert(varchar,sum(case when B.jobid is not null then 1 else 0 end))
+'封/'
+convert(varchar,(sum (case when B.[new]=1 then 1 else 0 end)))
+'封新)') as [count]
from table2 A
left join table1 B on A.jobid=B.jobid
where A.comid=303
group by A.jobid
,A.comid--删除测试环境
drop table table1,table2--结果
/*
comid jobid count
----------- ----------- ----------------
303 1 (3封/2封新)
303 2 (1封/1封新)
303 3 (0封/0封新)
303 4 (0封/0封新)(4 row(s) affected)
*/
create table table2(jobid int,comid int)
insert table1
select 1 ,1 union
select 2 ,1 union
select 1 ,1 union
select 5 ,0 union
select 1 ,0
insert into table2
select 1 ,303 union
select 2 ,303 union
select 3 ,303 union
select 4 ,303 union
select 5 ,304
go
select a.comid,a.jobid,
[count]='('+cast(sum(isnull(b.jobid,0))as varchar(10))+'封/'+
cast(sum(case new when 1 then 1 else 0 end)as varchar(10))+'封新)'
from table2 a left join table1 b on a.jobid=b.jobid where a.comid=303
group by a.comid,a.jobid
/*(所影响的行数为 4 行)
(所影响的行数为 5 行)comid jobid count
----------- ----------- -----------------------------
303 1 (2封/1封新)
303 2 (2封/1封新)
303 3 (0封/0封新)
303 4 (0封/0封新)(所影响的行数为 4 行)
*/
drop table table1,table2
from table2 a left join table1 b on a.jobid=b.jobid where a.comid=303 group by a.comid,a.jobid