SQL动态位置和分组依据

本文介绍了SQL动态位置和分组依据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用存储过程并将其与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 []..

感谢拂 stackoverflow/a/206645/6240803