不能拉出不同的记录 [英] cant pull up distinct records

问题描述

这是我的存储过程，除了它的独特功能之外它做了它应该做的事情，现在它为一个城市提取了超过1个结果。



我在sql中是非常新的，请原谅我，如果这个问题听起来很奇怪你需要

this is my stored procedure, its doing what its supposed to do except its "distinct" function, right now its pulling up more than 1 result for one city.

I am very new in sql so forgive me if this question sounds weird for youq

  SELECT distinct city.name, jp.id
, ROW_NUMBER() OVER (order by city.name) [sr_no]
, COUNT(jp.id) OVER (partition by name) as no_of_posts
, COUNT(od.id) OVER (partition by name) as no_of_employers
, CONVERT(varchar(12), jp.posting_date, 103) [date_created]
   FROM rs_job_posting jp

INNER JOIN rs_job_posting_location jpl on jp.id = jpl.id
INNER JOIN rs_cor_city city on jpl.city_fk = city.id
INNER JOIN rs_organization_detail od on od.id = jp.id
order by city.name ASC
where cast(city.name as int) <= 1

推荐答案

由于我们在这里缺乏信息，我们只能猜测是什么你试图实现。

但是在分析函数之前执行 DISTINCT 子句 DISTINCT 实际上是在city.name，jp.id和od.id上制作的，这可能不是你的意图。



< edit>正如你所拥有的那样在您的加入中，od.id = jp.id，您如何期望no_of_posts和no_of_employers彼此不同？< / edit>

As we're lacking information here we can only guess what you're trying to achieve.
But as the DISTINCT clause is performed BEFORE the analytic functions the DISTINCT is actually made on city.name, jp.id AND od.id, which might not be your intention.

<edit>As you have od.id = jp.id in your join, how do you expect no_of_posts and no_of_employers to be different from each other?</edit>

这篇关于不能拉出不同的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！