SQL UNION重复的问题与多个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 
    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

比分被送回距离，因此没有独特的行。

我该如何改变这种情况？

'UNION'要求在所有UNIONed查询中列数相同，并且数据类型在每个位置匹配。 – 2012-02-17 05:32:38

结果列的名称将取自UNION中的第一个SELECT; _primus inter pares_也在这里工作。也许你打算做某种连接而不是工会？至少，如果您想要某些行的分数以及其他分数的距离，那么您有一些关于如何对查询进行短语的思考。这可能是因为你将UNION作为子查询来识别有趣的工作，然后对每一个有趣的工作，在最终结果查询中计算（重新？）距离和得分。 – 2012-02-17 05:47:34

考虑在第一个UNION的表表达式中添加'distance'的默认值，并在第二个表中添加'score'的默认值。 – onedaywhen 2012-02-17 08:18:39

答

尝试使用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

答

我会使用子查询和无序结果按分数和距离字段，以便用同样的比分行将通过距离进行排序。我从最终结果集中删除了分数列，我不确定在这种情况下是否需要它。

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

SQL UNION重复的问题与多个AS语句

相关推荐