sql查询只从多行中选择一行

I have this query:

我有这个问题:

SELECT p.[PostingID]
      ,[EmployerID]
      ,[JobTitle]                  
      ,pin.[IndustryID]         
FROM [Posting] p
INNER JOIN [City] c
  ON p.CityID = c.CityID
LEFT OUTER JOIN PostingIndustry pin
  ON p.PostingID = pin.PostingID
WHERE (c.CityID = @CityId OR @CityId IS NULL) 
  AND (p.StateProvinceID = @StateProvinceId OR @StateProvinceId IS NULL) 
  AND (pin.IndustryID = @IndustryId OR @IndustryId IS NULL) 
  AND 
  (
     (p.[Description] LIKE '%' + @Keyword + '%' OR @Keyword IS NULL) 
     OR (p.[JobTitle] LIKE '%' + @Keyword + '%'  OR @Keyword IS NULL)
  ) 
  AND p.StreetAddress IS NOT NULL 
  AND p.ShowOnMap = 1

Which returns results for all the pin.[IndustryID] if IndustryId is not selected or if all the industries are selected. If only one industry is selected I am getting one result which is good, but when one posting is included in multiple industries then I am getting multiple results as on the image shown below:

返回所有引脚的结果。[IndustryID]如果未选择IndustryId或选择了所有行业。如果只选择了一个行业,我会得到一个好的结果,但是当一个帖子包含在多个行业中时,我会得到多个结果,如下图所示:

So for example when thats happening I want to get only one result for that posting id otherwise I am getting multiple results for one google map marker per the image below:

因此,例如当发生这种情况时,我想只获得该发布ID的一个结果,否则我将获得一个谷歌地图标记的多个结果,如下图所示:

Is there a way how I can optimize the query above to do what I need? Thanks in advance, Laziale

有没有办法如何优化上面的查询来做我需要的?提前谢谢,Laziale

3 个解决方案

#1

What about selecting only the row with the smallest IndustryId:

那么只选择IndustryId最小的行:

SELECT [PostingID]
      ,[EmployerID]
      ,[JobTitle]                  
      ,MIN(pin.[IndustryID])         
FROM [Posting] p
INNER JOIN [City] c
  ON p.CityID = c.CityID
LEFT OUTER JOIN PostingIndustry pin
  ON p.PostingID = pin.PostingID
WHERE (c.CityID = @CityId OR @CityId IS NULL) 
  AND (p.StateProvinceID = @StateProvinceId OR @StateProvinceId IS NULL) 
  AND (pin.IndustryID = @IndustryId OR @IndustryId IS NULL) 
  AND 
  (
     (p.[Description] LIKE '%' + @Keyword + '%' OR @Keyword IS NULL) 
     OR (p.[JobTitle] LIKE '%' + @Keyword + '%'  OR @Keyword IS NULL)
  ) 
  AND p.StreetAddress IS NOT NULL 
  AND p.ShowOnMap = 1
GROUP BY [PostingID],[EmployerID],[JobTitle] 

Whenever you have only one, it returns that one, when you have more than one it returns only the one with the smallest IndustryId.

只要你只有一个,它就返回那个,当你有多个时它只返回一个具有最小的IndustryId的那个。

#2

Don't select industry ID and use "SELECT DISTINCT..."

不要选择行业ID并使用“SELECT DISTINCT ...”

#3

Try using a group by clause at the end

最后尝试使用group by子句

Use MAX(IndustryId).

Then also

GROUP BY PostingId,EmployerId,Jobtitle