SQL语句（NULL值的问题--在线等待--50分）
有(Sample,Experimenter,Company,Person)4个表
SAMPLE表中有5个字段分别连接其他3个表（Experimenter 1个，Company 2个，Person 2个）
以下存储过程运行的时候。
如果5个连接字段中没有1个为NULL，则返回正常（1条记录）。
如果5个其中有1个为NULL，则没有数据返回。
请高手指点，急。。。在线等待
USE [QIIMS]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N '[dbo].[spSelectSample] ') AND type in (N 'P ', N 'PC '))
DROP PROCEDURE [dbo].[spSelectSample]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spSelectSample]
@SampleID nvarchar(50)=null
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ExperimenterID int
DECLARE @CommissionCompanyID int
DECLARE @ProduceCompanyID int
DECLARE @SamplePerson int
DECLARE @LinkPerson int
SELECT @ExperimenterID=[ExperimenterID]
,@CommissionCompanyID=[CommissionCompanyID]
,@ProduceCompanyID=[ProduceCompanyID]
,@SamplePerson=[SamplePerson]
,@LinkPerson=[LinkPerson]
FROM [QIIMS].[dbo].[Sample]
WHERE SampleID=@SampleID;
SELECT *
FROM
(
SELECT [SampleID]
,[Name]
,[BatchNumber]
,[Amount]
,[TypeLevel]
,[InspectionType]
,[NationalStandard]
,[LimitTime]
,[RegistrationTime]
,[InvoiceNumber]
,[Heading]
,[Result]
,[State]
,[Remark]
,[Time]
FROM [QIIMS].[dbo].[Sample]
WHERE SampleID=@SampleID
) AS A
,
(
SELECT [ExperimenterID]
,[Name]
,[Address]
,[Telephone]
,[Email]
FROM [QIIMS].[dbo].[Experimenter]
WHERE ExperimenterID=@ExperimenterID
) AS B
,
(
SELECT isnull([CompanyID], ' ')as bbb
--,[Name]
--,[Address]
--,[Postalcode]
--,[Telephone]
FROM [QIIMS].[dbo].[Company]
WHERE CompanyID=@CommissionCompanyID
) AS C
,
(
SELECT [CompanyID]
,[Name]
,[Address]
,[Postalcode]
,[Telephone]
FROM [QIIMS].[dbo].[Company]
WHERE CompanyID=@ProduceCompanyID
) AS D
,
(
SELECT [PersonID]
,[Name]
,[Telephone]
FROM [QIIMS].[dbo].[Person]
WHERE PersonID=@SamplePerson
) AS E
,
(
SELECT [PersonID]
,[Name]
,[Telephone]
FROM [QIIMS].[dbo].[Person]
WHERE PersonID=@LinkPerson
) AS F
END
[解决办法]
要看你的需求阿
如果是N个结果集，可以分成N个select
如果要关联，用left join指定连接条件，第一张表不为空就行了
如果你要像上面的全关联，用动态sql，将有值的表进行拼接
[解决办法]
Left join 可以关联N个表的啊。
关注中。
