select reqID, reqTitle, testcaseId, testcastTitle, restcaseResult from requirement join ( select testcaseId, testcastTitle, testcaseResult from testcase ) on requirement.reqID=testcase.reqID
select a.*,case when b.TestcaseResult>1 then 'fail' else 'pass' end as TestcaseResult from requirement a left join (select reqID,SUM(case when TestcaseResult='fail' then 1 else 0 end) as TestcaseResult from testcase group by reqID )b on a.reqID=b.reqID
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp; go create table #temp( reqID INT, TestcaseResult varchar(10)); insert #temp select '1','pass' union all select '1','pass' union all select '1','pass' union ALL select '2','pass' union ALL select '2','fail'--假如你的TestcaseResult只有两个值(pass,fail)的话 SELECT *, reqResult = MIN(TestcaseResult) OVER(PARTITION BY reqID) FROM #temp--方法2 SELECT *, reqResult = (CASE WHEN EXISTS(SELECT 1 FROM #TEMP B WHERE B.reqID=A.reqID AND TestcaseResult='fail') THEN 'fail' ELSE 'pass' END) FROM #temp A/* reqID TestcaseResult reqResult 1 pass pass 1 pass pass 1 pass pass 2 pass fail 2 fail fail */
select requirement.reqID, requirement.reqTitle, testcase.testcaseId, testcase.testcastTitle, case when a.result=0 then 'pass' else 'fail' end as testcaseResult from ( select reqID, sum(case when testcaseResult='fail' then 1 else 0 end) as result from testcase group by reqID ) a inner join requirement on requirement.reqID=a.reqID inner join testcase on a.reqID=testcase.reqID
from requirement
join
(
select testcaseId, testcastTitle, testcaseResult
from testcase
)
on requirement.reqID=testcase.reqID
from requirement a
left join (select reqID,SUM(case when TestcaseResult='fail' then 1 else 0 end) as TestcaseResult
from testcase group by reqID
)b
on a.reqID=b.reqID
go
create table #temp( reqID INT, TestcaseResult varchar(10));
insert #temp
select '1','pass' union all
select '1','pass' union all
select '1','pass' union ALL
select '2','pass' union ALL
select '2','fail'--假如你的TestcaseResult只有两个值(pass,fail)的话
SELECT *,
reqResult = MIN(TestcaseResult) OVER(PARTITION BY reqID)
FROM #temp--方法2
SELECT *,
reqResult = (CASE WHEN EXISTS(SELECT 1 FROM #TEMP B WHERE B.reqID=A.reqID AND TestcaseResult='fail') THEN 'fail' ELSE 'pass' END)
FROM #temp A/*
reqID TestcaseResult reqResult
1 pass pass
1 pass pass
1 pass pass
2 pass fail
2 fail fail
*/
from
(
select reqID, sum(case when testcaseResult='fail' then 1 else 0 end) as result
from testcase
group by reqID
) a
inner join requirement
on requirement.reqID=a.reqID
inner join testcase
on a.reqID=testcase.reqID