复杂的SQL选择查询 [英] Complex SQL selection query
问题描述
大家好,
希望每个人都做得很好。
我有一个存储过程,需要一个不同的if条件才能正常工作。
该过程有2个参数,即@CategoryID和@ClassID,它们基本上来自UI树视图功能。 @CategoryID对应于父节点,而@ClassID对应于子节点。
基于以上参数,我需要从中选择(列代码)具有CategoryID和ClassID作为列的表。
现在有2种情况:
情形1
@ CategoryID:A
@ClassID:B(这是CategoryID A的子节点)
所需结果:仅对应于ClassID B的代码,基本上是交叉点/>
场景2
@CategoryID:A
@ClassID:C(不是CategoryID A的子节点) )
需要的结果:对应于CategoryID A的代码,以及ClassID B,基本上是一个联盟
我写的程序给出了我对第二种情况的正确答案,但第一种情况失败了。以下是我的程序:
ALTER PROCEDURE [dbo]。[uspGetCodes]
@ CategoryID varchar ( 50 ),
@ ClassID varchar ( 50 )
AS
BEGIN
BEGIN TRY
DECLARE @ SQLQuery NVARCHAR (MAX)
SET @ SQLQuery = N ' SELECT代码FROM dbo.ClassToCategoryMapping WHERE'
IF ( @ CategoryID IS NULL OR @ CategoryID = ' ')
BEGIN
SET @ SQLQuery = @ SQLQuery + ' ClassId IN(' + @ ClassID + ' )'
PRINT ( @ SQLQuery )
EXEC ( @SQLQuery )
END
ELSE < span class =code-keyword> IF ( @ ClassID IS NULL OR @ ClassID = ' ')
BEGIN
SET @ SQLQuery = @SQLQuery + ' CategoryID IN(' + @ CategoryID + ' )'
PRINT ( @ SQLQuery )
EXEC ( @ SQLQuery )
END
ELSE
BEGIN
SET @ SQLQuery = @ SQLQuery + ' (CategoryID IN(' + @ CategoryID + ' )或ClassId IN(' + @ ClassID + ' ))'
PRINT ( @ SQLQuery )
EXEC ( @ SQLQuery )
END
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ' ErrorNumber',ERROR_MESSAGE() AS ' ErrorMessage',ERROR_SEVERITY() AS ' ErrorSeverity',ERROR_STATE() AS ' ErrorState',ERROR_LINE() AS ' ErrorLine
RETURN ERROR_NUMBER()
END CATCH
END
最后一个part实际上是一个'OR',它给了我CategoryID和ClassID的代码的联合,不管给定的ClassID是否是给定CategoryID的子代。
我在这里的问题是,如何编写实现这两种情况的条件。
样本数据:
情景1
@ CategoryId = 2,@ ClassID = 10(这里10是孩子,2是父,CategoryID 2对应ClassID 10,11,12)
预期结果:10
场景2
@ CategoryID = 2,@ ClassID = 13(13是不同父级的子级,CategoryID 2对应于ClassID的10,11,12)
预期成果:10,11,12,13
我想我已经做了我的问题很清楚,如果没有,人们可以让我编辑它。我很乐意这样做。我敦促专家帮我解决这个问题。任何指针也会非常感激。
问候
Anurag
嘿那里,
在你的上一个ELSE
部分尝试这个:< pre lang =SQL> BEGIN
IF EXISTS ( SELECT 代码 FROM dbo.ClassToCategoryMapping WHERE CategoryID = @ CategoryID 和 ClassID = @ClassID )
BEGIN
SET @ SQLQuery = @ SQLQuery + ' (CategoryID IN(' + @ CategoryID + ' )和ClassId IN(' + @ ClassID +' ))'
END
ELSE
BEGIN
SET @ SQLQuery = @ SQLQuery + ' (CategoryID IN(' + @ CategoryID + < span class =code-string>' )或ClassId IN(' + @ ClassID + ' ))'
END
PRINT ( @ SQLQuery )
EXEC ( @ SQLQuery )
END
注意:如果参数不包含多个值,则不要使用IN
,只需使用=
。
Ho它有帮助,请告诉我。
Azee ......
Hi people,
Hope everyone is doing good.
I have a stored procedure which needs a different if condition to work properly.
The procedure has 2 parameter namely, @CategoryID and @ClassID, which basically come from a UI tree view functionality. @CategoryID corresponds to the parent nodes, while @ClassID corresponds to the child nodes.
Based upon the above parameters I need to make a selection(Column Code) from a table which has CategoryID and ClassID as columns.
Now there are 2 scenarios:
Scenario 1
@CategoryID:A
@ClassID:B (which is a child node of CategoryID A)
Result needed: Codes corresponding to only ClassID B, which is basically the intersection
Scenario 2
@CategoryID:A
@ClassID: C (which is not a child node for CategoryID A)
Result needed: Codes corresponding to the CategoryID A, as well as ClassID B, basically a union
The procedure which I wrote gives me correct answer for the second scenario, but the first scenario it fails. Below is my procedure:
ALTER PROCEDURE [dbo].[uspGetCodes]
@CategoryID varchar(50),
@ClassID varchar(50)
AS
BEGIN
BEGIN TRY
DECLARE @SQLQuery NVARCHAR(MAX)
SET @SQLQuery=N'SELECT Code FROM dbo.ClassToCategoryMapping WHERE '
IF (@CategoryID IS NULL OR @CategoryID='')
BEGIN
SET @SQLQuery=@SQLQuery + 'ClassId IN ('+@ClassID+')'
PRINT(@SQLQuery)
EXEC(@SQLQuery)
END
ELSE IF (@ClassID IS NULL OR @ClassID='')
BEGIN
SET @SQLQuery=@SQLQuery+'CategoryID IN ('+@CategoryID+')'
PRINT(@SQLQuery)
EXEC(@SQLQuery)
END
ELSE
BEGIN
SET @SQLQuery=@SQLQuery+'(CategoryID IN ('+@CategoryID+') OR ClassId IN ('+@ClassID+') )'
PRINT(@SQLQuery)
EXEC(@SQLQuery)
END
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS 'ErrorNumber', ERROR_MESSAGE() AS 'ErrorMessage', ERROR_SEVERITY() AS 'ErrorSeverity', ERROR_STATE() AS 'ErrorState', ERROR_LINE() AS 'ErrorLine'
RETURN ERROR_NUMBER()
END CATCH
END
The Last Else part actually does an 'OR', which gives me the union of the Codes for CategoryID's and ClassID's irrespective whether the given ClassID is a child of the given CategoryID or not.
My question over here would be, how to write the condition to achieve both the scenarios.
Sample Data:
Scenario 1
@CategoryId=2, @ClassID=10 (Here 10 is the child while 2 is the parent, CategoryID 2 corresponds to ClassID's 10, 11, 12)
Expected Result: 10
Scenario 2
@CategoryID=2, @ClassID=13 (13 is the child of a different parent, CategoryID 2 corresponds to ClassID's 10, 11 ,12)
Expected Result: 10, 11, 12, 13
I guess I have made my question quite clear, if no then, folks can ask me to edit it. I would be happy to do so. I urge the experts to assist me in this problem. Any pointers too will be quite appreciated.
Regards
Anurag
Hey there,
Try this in your lastELSE
part:BEGIN IF EXISTS(SELECT Code FROM dbo.ClassToCategoryMapping WHERE CategoryID = @CategoryID and ClassID = @ClassID) BEGIN SET @SQLQuery=@SQLQuery+'(CategoryID IN ('+@CategoryID+') and ClassId IN ('+@ClassID+') )' END ELSE BEGIN SET @SQLQuery=@SQLQuery+'(CategoryID IN ('+@CategoryID+') or ClassId IN ('+@ClassID+') )' END PRINT(@SQLQuery) EXEC(@SQLQuery) END
NOTE: If you parameters don't contain multiple values, then don't useIN
, just use=
.
Hope it helps, do let me know.
Azee...
这篇关于复杂的SQL选择查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!