无法弄清楚内部联接 [英] Can not figure out Inner join

问题描述

我已经尝试了6个小时，为此存储过程编写一个内部联接.由于某种原因，无论我做什么，它都会为另一张表的每一行添加一张表的每一行.

这是我的工作代码，但是如您所见，它的性能非常低

I have been trying for 6 hours now to write an inner join for this stored proc. For some reason no matter what I do it adds everyline of 1 table for each line of the other table.

This is my working code but as you can see, it has very low performance

USE [RHINO]
GO
/****** Object:  StoredProcedure [dbo].[spSetFeatures]    Script Date: 12/17/2011 10:31:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spSetFeatures]
-- Parameters ---------------------------------
@VehicleID int,
@VINVehicleID int,
@FeatureID int,
@VehicleValue nvarchar(100)
AS
BEGIN
Insert Into VehicleFeatures(
VehicleID,
FeatureName,
FeatureGroup,
FeatureAbbr,
FeatureCat,
FeatureUnit,
FeatureValue,
VehicleValue,
FeatureID)
Values(
@VehicleID,
(Select FeatureName From VININFO_Features WHere FeatureID=@FeatureID),
(Select GroupName From VININFO_Features Where FeatureID=@FeatureID),
(Select Abbreviation From VININFO_Features WHere FeatureID=@FeatureID),
(Select FeatureCategory From VININFO_Features Where FeatureID=@FeatureID),
(Select FeatureUnit From vwVININFO_Vehicles Where  VINVehicleID=@VINVehicleID AND FeatureID=@FeatureID),
(Select FeatureValue From vwVININFO_Vehicles Where  VINVehicleID=@VINVehicleID AND FeatureID=@FeatureID),
@VehicleValue,
@FeatureID)
--Code for spSetFeatures-------
END

我可以张贴尝试编写它的10,000种方法之一，但是如果有人可以给我答案，我将看到正确的编写方法，以备将来使用

I could post one of the 10,000 ways I tried to write it but if someone can please give me the answer, I will see the right way to write it as have it for use in the future

推荐答案

您发布的语句应仅添加一行.如果任何选择返回多行，则会产生错误.

如果结果是添加了几行，您确定不重复调用此SP吗?

The statement you posted should add only one row. If any of the selects returns multiple rows an error is generated.

If the result is that several rows are added, are you sure you''re not calling this SP repeatedly?

您不能对插入语句的各个值使用select语句.

您应该首先将它们存储在本地变量中，然后在您的语句中使用该变量.

所以你需要像这样的东西:

You can not use select statement for individual values of insert statement.

You should first store them in a local variable and then use that variable in your statement.

So you need to have something like this :

declare @FeatureName  varchar(max)
Select @FeatureName = FeatureName From VININFO_Features WHere FeatureID=@FeatureID
.
.
.
Insert Into VehicleFeatures(
VehicleID,
FeatureName,
FeatureGroup,
FeatureAbbr,
FeatureCat,
FeatureUnit,
FeatureValue,
VehicleValue,
FeatureID)
Values(
@VehicleID,
 @FeatureName ,
.
.
.
)

希望对您有所帮助.

Hope it helps.

这篇关于无法弄清楚内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！