SQL查询不应返回数据透视表中包含空值的行

I have created a query using a pivot table to extract some metadata (from my wordpress database) for latitude and longitude values associated with posts. I would like to only return posts that have these particular pieces of metadata, and ignore all other posts, but I am unable to filter out those posts that don't have values or these meta keys. Here is my SQL:

我使用数据透视表创建了一个查询,以提取与帖子相关的纬度和经度值的一些元数据(来自我的wordpress数据库)。我想只返回具有这些特定元数据的帖子,并忽略所有其他帖子,但我无法过滤掉那些没有值或这些元键的帖子。这是我的SQL:

SELECT wp_posts.ID,   
wp_posts.post_title, wp_posts.post_modified_gmt,
MAX(CASE WHEN wp_postmeta.meta_key = "_wp_geo_longitude" then wp_postmeta.meta_value ELSE NULL END) as longitude,
MAX(CASE WHEN wp_postmeta.meta_key = "_wp_geo_latitude" then wp_postmeta.meta_value ELSE NULL END) as latitude
FROM wp_posts JOIN wp_postmeta  ON ( wp_postmeta.post_id = wp_posts.ID)  
WHERE wp_posts.post_status = 'publish'                  
GROUP BY wp_posts.ID, wp_posts.post_title;

I have tried adding the following to the WHERE clause with zero effect:

我尝试将以下内容添加到WHERE子句中,但效果为零:

WHERE longitude IS NOT NULL
WHERE wp_postmeta.meta_value IS NOT NULL

And variations of that, but all posts are still returned, regardless of null values.

不同的变化,但所有帖子仍然返回,无论空值如何。

1 个解决方案

#1

Just add a having clause:

只需添加一个having子句:

having longitude is not null and latitude is not null

This is a situation where the join method of pivoting might be more efficient:

这种情况下,旋转的连接方法可能更有效:

SELECT p.ID, p.post_title, p.post_modified_gmt,
       lng.meta_value as longitude, lat.meta_value as latitude
FROM wp_posts p JOIN
     wp_postmeta lat
     ON wp_postmeta.post_id = p.ID AND lat.meta_key = '_wp_geo_latiitude' JOIN
     wp_postmeta lng
     ON wp_postmeta.post_id = p.ID AND lng.meta_key = '_wp_geo_longitude'
WHERE p.post_status = 'publish';

In general, I prefer the aggregation method more than the join method because it is more flexible. However, you are only getting two fields and you want to ensure that both are there. That means that the option of using joins is quite reasonable in this case.

一般来说,我更喜欢聚合方法而不是连接方法,因为它更灵活。但是,您只获得两个字段,并且您希望确保两个字段都在那里。这意味着在这种情况下使用连接的选项非常合理。

#1