22,207
社区成员
发帖
与我相关
我的任务
分享
select
product_sn,
min(data) as data_25,
max(data) as data_85,
case when min(data)>10 and min(data)<70 and max(data)>0 and max(data)<30 then 'Y' else 'N' end as pass_or_not
from
(
SELECT id , product_sn , test_temperature , data
FROM tb a
WHERE NOT EXISTS(
SELECT TOP 1 1
FROM tb
WHERE product_sn=a.product_sn
AND test_temperature= a.test_temperature
AND id>a.id
)
)tmp
GROUP BY product_sn
select * ,
(case when data_25 > 10 and data_25 < 70 and data_85 > 0 and data_85 < 30 then '合格' else '不合格' end) pass_or_not
from
(
select product_sn ,
max(case test_temperature when 25 then data else 0 end) data_25,
max(case test_temperature when 85 then data else 0 end) data_85
from
(
select t.* from DbProduct t where id = (select max(id) from DbProduct whenre product_sn = t.product_sn and test_temperature = t.test_temperature)
) m
group by product_sn
) n
SELECT product_sn,data_25=MIN(data),
data_85=MAX(data),
pass_or_not=CASE WHEN MIN(data)>10 AND MIN(data)<70
AND
MAX(data)>0 AND MAX(data)<30
THEN 'Y'
ELSE 'N'
END
FROM
(
SELECT id , product_sn , test_temperature , data
FROM tb a
WHERE NOT EXISTS(
SELECT TOP 1 1
FROM tb
WHERE product_sn=a.product_sn
AND test_temperature= a.test_temperature
AND id>a.id
)
)tmp
GROUP BY product_sn