求教一条SQL语句。

Question

try:
select 
(select top 1 vMaterialName from PoundMaterial where PoundMaterial.vMaterialId=PoundRecord.vMaterialId) as vMaterialName,
(select top 1 vUnitName  from PoundIntOutUnit where PoundIntOutUnit.vUnitId=PoundRecord.vOutUnitId) as vUnitName,
(select top 1 vUnitName from ShouHuoFang where ShouHuoFang.vUnitId=PoundRecord.vInUnitId) as vUnitName,
(select top 1 vTypeName from PoundType where  PoundType.vMatTypeId=PoundRecord.vMatTypeId) as vTypeName,
(select top 1 vUserName from Operator where  Operator.vUserId=PoundRecord.vEmployeeId) as vUserName,
(select top 1 vUserName from ChengPiZhong where ChengPiZhong.vUserId=PoundRecord.vSkinOperator) as vUserName,
PoundRecord.vRecoreId 磅单号,
PoundRecord.vCarNo 车号,
PoundRecord.SkinDate 毛重日期,
PoundRecord.SkinTime 毛重时间,
PoundRecord.dDate 皮重日期,
PoundRecord.dTime 皮重时间,
PoundRecord.nGrossWei 毛重,
PoundRecord.nSkin 皮重,PoundRecord.nNetWei 净重
from PoundRecord
where 
PoundRecord.SkinDate between '2005-10-1'  
       and '2005-10-1'
order by PoundRecord.vRecoreId

Answer 1

用外连接：SELECT PoundMaterial.vMaterialName 货名,
       PoundIntOutUnit.vUnitName 发货方,
       ShouHuoFang.vUnitName 收货方,
       PoundType.vTypeName 装货方,
       Operator.vUserName 称毛重,
       ChengPiZhong.vUserName 称皮重,
       PoundRecord.vRecoreId 磅单号,
       PoundRecord.vCarNo 车号,
       PoundRecord.SkinDate 毛重日期,
       PoundRecord.SkinTime 毛重时间,
       PoundRecord.dDate 皮重日期,
       PoundRecord.dTime 皮重时间,
       PoundRecord.nGrossWei 毛重,
       PoundRecord.nSkin 皮重,PoundRecord.nNetWei 净重
FROM PoundRecord 
LEFT OUTER JOIN Operator 
ON Operator.vUserId=PoundRecord.vEmployeeId
LEFT OUTER JOIN PoundMaterial 
ON PoundMaterial.vMaterialId=PoundRecord.vMaterialId
LEFT OUTER JOIN PoundType 
ON PoundType.vMatTypeId=PoundRecord.vMatTypeId
LEFT OUTER JOIN PoundIntOutUnit 
ON PoundIntOutUnit.vUnitId=PoundRecord.vOutUnitId
LEFT OUTER JOIN PoundIntOutUnit ShouHuoFang 
ON ShouHuoFang.vUnitId=PoundRecord.vInUnitId
LEFT OUTER JOIN Operator ChengPiZhong 
ON ChengPiZhong.vUserId=PoundRecord.vSkinOperator
WHERE PoundRecord.SkinDate BETWEEN '2005-10-1' AND '2005-10-1'
ORDER BY PoundRecord.vRecoreId

Answer 2

谢谢bugchen888(臭虫)，按你的方法我成功了。
现在去给你加分！