为什么一定要一句??
看看以下语句,是一个语句,花了我半个小时:select isnull(a.HeatId,b.HeatId) as HeatId,
case when a.HeatId is null then b.shift
when b.HeatId is null then a.shift
when a.HeatId is not null and b.HeatId is not null then a.shift+'/'+b.shift
end as Shift,
case when a.HeatId is null then cast(b.cn as varchar(10))
when b.HeatId is null then cast(a.cn as varchar(10))
when a.HeatId is not null and b.HeatId is not null then cast(a.cn as varchar(10))+'/'+cast(b.cn as varchar(10))
end as cn
from
(
select HeatId,Shift,count(*) as cn from QualityArtStatics a where Shift=(
select shift from QualityArtStatics b where HeatId=a.HeatId and PassingTime=(
select min(PassingTime) from QualityArtStatics where HeatId=b.HeatId
)
)
group by HeatId,Shift
) as a
full join
(
select HeatId,Shift,count(*) as cn from QualityArtStatics a where Shift<>(
select shift from QualityArtStatics b where HeatId=a.HeatId and PassingTime=(
select min(PassingTime) from QualityArtStatics where HeatId=b.HeatId
)
)
group by HeatId,Shift
) as b
on a.HeatId=b.HeatId
看看以下语句,是一个语句,花了我半个小时:select isnull(a.HeatId,b.HeatId) as HeatId,
case when a.HeatId is null then b.shift
when b.HeatId is null then a.shift
when a.HeatId is not null and b.HeatId is not null then a.shift+'/'+b.shift
end as Shift,
case when a.HeatId is null then cast(b.cn as varchar(10))
when b.HeatId is null then cast(a.cn as varchar(10))
when a.HeatId is not null and b.HeatId is not null then cast(a.cn as varchar(10))+'/'+cast(b.cn as varchar(10))
end as cn
from
(
select HeatId,Shift,count(*) as cn from QualityArtStatics a where Shift=(
select shift from QualityArtStatics b where HeatId=a.HeatId and PassingTime=(
select min(PassingTime) from QualityArtStatics where HeatId=b.HeatId
)
)
group by HeatId,Shift
) as a
full join
(
select HeatId,Shift,count(*) as cn from QualityArtStatics a where Shift<>(
select shift from QualityArtStatics b where HeatId=a.HeatId and PassingTime=(
select min(PassingTime) from QualityArtStatics where HeatId=b.HeatId
)
)
group by HeatId,Shift
) as b
on a.HeatId=b.HeatId
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货