mysql - WordPress Mysql查询和重复

Question

我的 wordpress 数据库上的 mysql 查询有问题。

我有自定义字段国家和城市。

目前我有这个查询显示“英国”国家/地区的所有城市

$querystr = "
SELECT wposts.* 
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id 
AND wpostmeta.meta_key  = 'Country' 
AND wpostmeta.meta_value  = 'UK' 
AND wposts.post_type = 'post' 
ORDER BY wpostmeta.meta_value DESC
";
$pageposts = $wpdb->get_results($querystr, OBJECT);

这是显示所有城市的 php foreach 查询

  <?php

 $querystr = "
 SELECT wposts.* 
 FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
 WHERE wposts.ID = wpostmeta.post_id 
 AND wpostmeta.meta_key  = 'Country' 
 AND wpostmeta.meta_value  = 'UK' 
 AND wposts.post_type = 'post' 
 ";

 $pageposts = $wpdb->get_results($querystr, OBJECT);

 ?>
 <?php if ($pageposts): ?>

  <?php global $post; ?>
  <?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post); ?>

    <?php echo get_post_meta($post->ID, 'city', true) ?> <br />

  <?php endforeach; ?>

 <?php endif; ?>

此查询列出了所有英国城市，从我的 WordPress 自定义字段“城市”中获取数据。

此查询返回如下城市列表:

Aberdeen
Aberdeen
Aberdeen
Aberdeen
Aberdeen
Belfast
Belfast
Belfast
Belfast
Belfast
Birchington
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Blackpool
Bournemouth
Bournemouth
Bournemouth
Bournemouth
Bournemouth
Bournemouth
Bournemouth
Bournemouth
Brighton
    Brighton

等等!!!

如您所见，“城市”字段值有重复，我需要显示一次城市并获得如下列表

      Aberdeen
      Aberdeen
      Belfast
      Birchington
      Birmingham
      Blackpool
      Bournemouth
      Brighton
      Bristol

我怎样才能得到这个？谢谢大家!!

Answer 1

试试这个

 $querystr = "
 SELECT  DISTINCT meta_value  FROM $wpdb->postmeta WHERE post_id IN(     
 SELECT DISTINCT wpostmeta.post_id
 FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
 WHERE wposts.ID = wpostmeta.post_id 
 AND wpostmeta.meta_key  = 'Country' 
 AND wpostmeta.meta_value  = 'UK' 
 AND wposts.post_type = 'post' ) AND meta_key ='city'
 ";
 $cities = $wpdb->get_results($querystr, OBJECT);

 <?php if ($cities ): ?>

  <?php global $post; ?>
  <?php foreach ($cities as $c): ?>    
    <?php echo $c->meta_value; ?> <br />    
  <?php endforeach; ?>

 <?php endif; ?>