SQL动态位置和分组依据 [英] SQL Dynamic Where and Group by

问题描述

我正在使用存储过程并将其与ASP C#链接，下面的查询成功运行且没有错误:

I'm using stored procedure and linking it with ASP C#, the below query runs successfully with no errors:

SELECT       loginInfo.ApplicantID, Personal.FirstName, Personal.LastName, Personal.Nationality, Job.PositionType, 
                      Job.InterestedArea, loginInfo.ApplicantStatus, Latest.HighestDegree, Latest.CompletionDate, 
                      Latest.LatestJobTitle, Latest.LatestCompanyIndustry, Latest.StartDate, Latest.EndDate,
                      SUM(DATEDIFF(YEAR,Exper.StartDate,Exper.EndDate)) AS TotalYearsExp
FROM         dbo.ApplicantLoginInfo AS loginInfo INNER JOIN
                      dbo.PersonalInfo AS Personal ON loginInfo.ApplicantID = Personal.ApplicantID INNER JOIN
                      dbo.InterestedJob AS Job ON loginInfo.ApplicantID = Job.ApplicantID INNER JOIN
                      dbo.LatestAcadExpInfo AS Latest ON loginInfo.ApplicantID = Latest.ApplicantID INNER JOIN
                      dbo.Experience AS Exper ON loginInfo.ApplicantID = Exper.ApplicantID

WHERE 1=1


GROUP BY loginInfo.ApplicantID, Personal.FirstName, Personal.LastName, Personal.Nationality, Job.PositionType, 
                      Job.InterestedArea, loginInfo.ApplicantStatus, Latest.HighestDegree, Latest.CompletionDate, 
                      Latest.LatestJobTitle, Latest.LatestCompanyIndustry, Latest.StartDate, Latest.EndDate

ORDER BY ApplicantID DESC

现在，我需要添加DYNAMIC where子句，因此我将查询分为三部分:@ SQL，@ WHERE，@ GROUPBY.

Now I need to add DYNAMIC where clauses, so I break the query into 3 parts @SQL,@WHERE,@GROUPBY.

请注意，我有一个汇总函数总和.

Please note that I have a aggregate function sum.

    if @gender <> '' SET @WHERE = ' AND Gender='''+@gender+''''
    if @nationality <> '' SET @WHERE = @WHERE + ' AND Nationality='''+@nationality+''''
    if @highestdegree <> '' SET @WHERE = @WHERE + ' AND HighestDegree='''+@highestdegree+''''
    if @latestcompanyindustry <> '' SET @WHERE = @WHERE + ' AND LatestCompanyIndustry='''+@latestcompanyindustry+''''
    if @interestedarea <> '' SET @WHERE = @WHERE + ' AND InterestedArea='''+@interestedarea+''''

    SET @SQL = 'SELECT loginInfo.ApplicantID, Personal.FirstName, Personal.LastName, Personal.Nationality, Job.PositionType,
    Job.InterestedArea, loginInfo.ApplicantStatus, Latest.HighestDegree, Latest.CompletionDate, Latest.LatestJobTitle, Latest.LatestCompanyIndustry, Latest.StartDate, Latest.EndDate,
    SUM(DATEDIFF(YEAR,Exper.StartDate,Exper.EndDate)) AS TotalYearsExp
    FROM dbo.ApplicantLoginInfo AS loginInfo INNER JOIN dbo.PersonalInfo AS Personal ON loginInfo.ApplicantID = Personal.ApplicantID INNER JOIN
    dbo.InterestedJob AS Job ON loginInfo.ApplicantID = Job.ApplicantID INNER JOIN 
dbo.LatestAcadExpInfo AS Latest ON loginInfo.ApplicantID = Latest.ApplicantID INNER JOIN dbo.Experience AS Exper ON loginInfo.ApplicantID = Exper.ApplicantID
    WHERE 1=1'

    SET @GROUPBY = 'GROUP BY loginInfo.ApplicantID, Personal.FirstName, Personal.LastName, Personal.Nationality, Job.PositionType, 
                                  Job.InterestedArea, loginInfo.ApplicantStatus, Latest.HighestDegree, Latest.CompletionDate, 
                                  Latest.LatestJobTitle, Latest.LatestCompanyIndustry, Latest.StartDate, Latest.EndDate

            ORDER BY ApplicantID DESC'

            EXEC(@SQL+@WHERE+@GROUPBY)

我不断收到错误消息

Error: The multi-part identifier "dbo.LatestAcadExpInf" could not be bound. The multi-part identifier "dbo.Experience.StartDate" could not be bound. The multi-part identifier "dbo.Experience.EndDate" could not be bound

推荐答案

奇怪的是，我要做的就是将列放在[]内.

Weird, all i had to do was place the columns inside []..

感谢拂 https://stackoverflow.com/a/206645/6240803

这篇关于SQL动态位置和分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！