小弟初学，现写了一条语句用来查询：
Select a.BulkCode,a.BulkBatchNo,'' as LotNo,a.ProcessingDate,a.InspectionNo,
b.PH_Result,
b.Viso_Result
From TBKInspectionHeader a Left outer Join
(
select a.Inspectionno,a.PH_Result,b.Viso_Result
from
(Select InspectionNo,Result as PH_Result
From TBKInspectionLines
Where Parameter like 'Viscosity%') a
inner join
(Select InspectionNo,Result as Viso_Result
From TBKInspectionLines
Where Parameter like 'Viscosity%' ) b
on a.InspectionNo=b.InspectionNo
where (a.PH_Result is not null or b.Viso_Result is not Null)
and a.InspectionNo = 'BK20120203001'
)b on a.InspectionNo=b.InspectionNo
Where a.Status=1
And a.BulkCode = '766735/70'
and a.BulkBatchNo = 'B2012020017'
查询结果如下：
766735/70 B2012020017 2012-02-03 00:00:00.000 BK20120203001 38.000000 38.000000
766735/70 B2012020017 2012-02-03 00:00:00.000 BK20120203001 38.000000 35.000000
766735/70 B2012020017 2012-02-03 00:00:00.000 BK20120203001 35.000000 38.000000
766735/70 B2012020017 2012-02-03 00:00:00.000 BK20120203001 35.000000 35.000000
很明显重复了，然后又试着改了语句，如下：
Select a.BulkCode,a.BulkBatchNo,'' as LOrealLotNo,a.ProcessingDate as PackingDay,a.InspectionNo,b.Result as PH_Result,c.Result as Viso_Result
From TBKInspectionHeader a
Left outer Join
( Select InspectionNo,Result
From TBKInspectionLines
Where Parameter like 'PH%'
) b on a.InspectionNo=b.InspectionNo
Left Outer Join
(
Select InspectionNo,Result
From TBKInspectionLines
Where Parameter like 'Viscosity%'
)c on a.InspectionNo=c.InspectionNo
Where a.Status='1'
And (b.Result is not null or c.Result is not Null)
查询结果还是重复了：
查询结果如下：
766735/70 B2012020017 2012-02-03 00:00:00.000 BK20120203001 38.000000 38.000000
766735/70 B2012020017 2012-02-03 00:00:00.000 BK20120203001 38.000000 35.000000
766735/70 B2012020017 2012-02-03 00:00:00.000 BK20120203001 35.000000 38.000000
766735/70 B2012020017 2012-02-03 00:00:00.000 BK20120203001 35.000000 35.000000
请问有什么好的方法来避免重复吗？
------解决方案--------------------
- SQL code
Select DISTINCT a.BulkCode,a.BulkBatchNo,'' as LOrealLotNo,a.ProcessingDate as PackingDay,a.InspectionNo,b.Result as PH_Result,c.Result as Viso_Result,b.Parameter as PH_Parameter,c.Parameter as Viso_ParameterFrom TBKInspectionHeader a Left outer Join ( Select InspectionNo,Result,Parameter From TBKInspectionLines Where Parameter like 'PH%' ) b on a.InspectionNo=b.InspectionNo Left Outer Join ( Select InspectionNo,Result,Parameter From TBKInspectionLines Where Parameter like 'Viscosity%' )c on a.InspectionNo=c.InspectionNoWhere a.Status='1'And (b.Result is not null or c.Result is not Null)And a.BulkCode = '766735/70'and a.BulkBatchNo = 'B2012020017'[color=#FF0000]and RIGHT(b.Parameter,LEN(b.Parameter) - CHARINDEX('-',b.Parameter)) =RIGHT(c.Parameter,LEN(c.Parameter) - CHARINDEX('-',c.Parameter))[/color]