表QualityArtStatics中的部分记录如下所示
HeatId LotId ShiftOrder Shift PassingTime
H10206506XX 1 白 丁 2002-11-14 19:34:35.000
H10206506XX 2 白 丁 2002-11-14 19:39:22.000
H10206506XX 3 白 丁 2002-11-14 19:44:09.000
H10206506XX 4 白 丁 2002-11-14 19:48:58.000
H10206507XX 1 白 丁 2002-11-14 19:53:45.000
H10206507XX 2 白 丁 2002-11-14 19:58:34.000
H10206507XX 3 夜 甲 2002-11-14 20:03:21.000
H10206507XX 4 夜 甲 2002-11-14 20:08:08.000
我想得到同一HeatId下的两个Shift（也可能示一个）并且按PassingTime的先后顺序排列中间加一个’/‘同时得到相应的数量中间加一个’/‘
例如：H10206507XX 要求得到 丁/甲 和 2/2
H10206506XX 要求得到 丁 和 4
怎样用一条SQL语句得出来？
---------------------------------------------------------------
为什么一定要一句??
看看以下语句，是一个语句，花了我半个小时：
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