关于SQL server语句查询统计问题

如果相同的Jointno算一个检测口，这一个检测口里有一个是不合格的 那么这个检测口就是不合格，如何统计合格的检测口里的合格数量？ 查询生成的结果 Weldercode|合格数
SFCC-124 | 3

哥们，对不对？对的话麻烦结贴。

max(len(ResultB) 应该可以吧

我这里设置table名为A

select count(C.Lineno) as '合格数' from
(select Lineno,count(Lineno) as 'count' from A where ResultB='合格' group by Lineno)C
where C.count=3

select weldercode,count(Lineno) as "合格数" from Table where Lineno not in (select DISTINCT Lineno from table where ResultB="不合格")

--先得到中间表
SELECT jointno
,Weldercode
,CASE
WHEN max(len(ResultB)) = 2
THEN 'qualified'
WHEN max(len(ResultB)) = 3
THEN 'unqualified'
END AS result
INTO #midtable
FROM tablename
GROUP BY jointno
,Weldercode

--统计各个Weldercode的 合格/不合格检测口数量
SELECT Weldercode,result
,count(*)
FROM #midtable
GROUP BY Weldercode,result