多个AS语句的sql联合重复问题

发布于 2025-01-06 01:56:33 字数 752 浏览 0 评论 0原文

使用以下 sql 查询：

  SELECT id, title, description, publisher,
         city, state, date,
         MATCH (title, description, publisher) AGAINST ('pizza+view' IN BOOLEAN MODE) AS score 
    FROM job 
  HAVING score > 0.01 
  UNION 
  SELECT id, title, description, publisher,
         city, state, date, ( 3959 * acos( cos( radians('37') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('-122') ) + sin( radians('37') ) * sin( radians( latitude ) ) ) ) AS distance
    FROM job
  HAVING distance < '175'
ORDER BY distance DESC
   LIMIT 0, 30

结果返回为：

id  title   description publisher   city    state   date    distance

分数作为距离发送回，因此没有唯一的行。

我怎样才能改变这个？

原文

Using the following sql query:

  SELECT id, title, description, publisher,
         city, state, date,
         MATCH (title, description, publisher) AGAINST ('pizza+view' IN BOOLEAN MODE) AS score 
    FROM job 
  HAVING score > 0.01 
  UNION 
  SELECT id, title, description, publisher,
         city, state, date, ( 3959 * acos( cos( radians('37') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('-122') ) + sin( radians('37') ) * sin( radians( latitude ) ) ) ) AS distance
    FROM job
  HAVING distance < '175'
ORDER BY distance DESC
   LIMIT 0, 30

Results come back as:

id  title   description publisher   city    state   date    distance

score is being sent back as distance, thus no unique rows.

how can I change this up?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

思念绕指尖 2025-01-13 01:56:33

我将使用子查询并按分数和距离字段对结果进行排序，因此具有相同分数的行将按距离排序。我已从最终结果集中删除了分数列，我不确定在这种情况下您是否需要它。

select id, title, description, publisher, city, state, date from (
    SELECT 
        id, title, description, publisher, city, state, date,
        MATCH (title, description, publisher) AGAINST ('pizza+view' IN BOOLEAN MODE) AS score,
        (3959 * acos( cos( radians('37') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('-122') ) + sin( radians('37') ) * sin( radians( latitude ) ) ) ) AS distance
    FROM job 
) t
where score > 0.01 or distance < 175
order by score desc, distance desc
limit 0, 30

I would use subquery and order result by score and distance field so rows with same score will be sorted by distance. I've dropped score column from final result set, I wasn't sure whether you would need it in this case.

select id, title, description, publisher, city, state, date from (
    SELECT 
        id, title, description, publisher, city, state, date,
        MATCH (title, description, publisher) AGAINST ('pizza+view' IN BOOLEAN MODE) AS score,
        (3959 * acos( cos( radians('37') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('-122') ) + sin( radians('37') ) * sin( radians( latitude ) ) ) ) AS distance
    FROM job 
) t
where score > 0.01 or distance < 175
order by score desc, distance desc
limit 0, 30

回复收藏 0 原文

南城旧梦 2025-01-13 01:56:33

尝试对第一个语句使用 CTE。
像这样的事情：

WITH score as (  SELECT id, title, description, publisher, 
     city, state, date, 
     MATCH (title, description, publisher) AGAINST ('pizza+view' IN BOOLEAN MODE) AS score  
FROM job  
HAVING score > 0.01 )

SELECT j.id, j.title, j.description, j.publisher, 
     j.city, j.state, j.date, ( 3959 * acos( cos( radians('37') ) * cos( radians( latitude ) )   
     *        cos( radians( longitude ) - radians('-122') ) + sin( radians('37') ) * sin(   
radians  ( latitude ) ) ) ) AS distance, s.score
FROM job j
LEFT OUTER JOIN score s ON s.ID = j.ID
HAVING j.distance < '175' 
ORDER BY distance DESC 
LIMIT 0, 30

Try using a CTE for the first statement.
Something like this:

WITH score as (  SELECT id, title, description, publisher, 
     city, state, date, 
     MATCH (title, description, publisher) AGAINST ('pizza+view' IN BOOLEAN MODE) AS score  
FROM job  
HAVING score > 0.01 )

SELECT j.id, j.title, j.description, j.publisher, 
     j.city, j.state, j.date, ( 3959 * acos( cos( radians('37') ) * cos( radians( latitude ) )   
     *        cos( radians( longitude ) - radians('-122') ) + sin( radians('37') ) * sin(   
radians  ( latitude ) ) ) ) AS distance, s.score
FROM job j
LEFT OUTER JOIN score s ON s.ID = j.ID
HAVING j.distance < '175' 
ORDER BY distance DESC 
LIMIT 0, 30

回复收藏 0 原文

~没有更多了~