dqk94069
dqk94069
2014-01-19 16:12
浏览 51
已采纳

为COUNT查询连接表

I'm using the following script to display pages, where $URL matches a page URL (e.g. MySite/People/Carl_Sagan)...

$sql= "SELECT COUNT(URL) AS num FROM people WHERE URL = :url";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':url',$MyURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();

switch($Total['num'])
{
 case 1:
 break;
 case 2:
 break;
 default:
 break;
}

On another site, I want to join several tables together, forming sort of a mini-encyclopedia. I know how to use the UNION command, but it isn't working with this query. Note that the target field in the table gz_life is named Taxon, not URL. I thought I could somehow alias it - Taxon AS URL - but that doesn't seem to be working, either.

$sql= "SELECT COUNT(URL) AS num FROM pox_topics WHERE URL = :url
UNION ALL
SELECT COUNT(URL) AS num FROM people WHERE URL = :url
UNION ALL
SELECT COUNT(Taxon) AS num FROM gz_life WHERE Taxon = :url";

Can anyone tell me the best way to join tables together in a PDO query?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dtkyayvldeaqhl7151
    dtkyayvldeaqhl7151 2014-01-19 18:15
    已采纳

    There are a few ways to do this (if I understand what you're trying to achieve). One would be use what you already have, but do the last step to add up the counts:

    SELECT SUM(num) FROM (
      SELECT COUNT(URL) AS num FROM pox_topics WHERE URL = :url
      UNION ALL
      SELECT COUNT(URL) AS num FROM people WHERE URL = :url
      UNION ALL
      SELECT COUNT(Taxon) AS num FROM gz_life WHERE Taxon = :url
    ) as subquery
    

    Note that you will need the alias for the subquery to make the query correct.

    点赞 评论
  • duanming7833
    duanming7833 2014-01-19 17:34

    If you could live with the counts on one line, you could write a query that uses :url exactly once:

    select tp.num_topic, tp.num_people, count(*) as num_taxon
    from (select t.url, t.num_topic, count(*) as num_people
          from (SELECT t.url, COUNT(URL) AS num_topic
                from pox_topics t
                where t.URL = :url
               ) t join
               people p
               on t.url = p.url
        ) tp join
        gz_life gl
        on gl.Taxon = tp.url;
    

    This turns the :url into a column and then uses successive layers of subqueries to calculate the counts.

    EDIT:

    It is possible that some of the counts would be 0. To handle that situation:

    select tp.num_topic, tp.num_people, count(gl.taxon) as num_taxon
    from (select t.url, t.num_topic, count(p.url) as num_people
          from (SELECT const.url, COUNT(t.URL) AS num_topic
                from (select :url as url) const left outer join
                     pox_topics t
                     on t.url = const.url
               ) t left outer join
               people p
               on t.url = p.url
        ) tp left outer join
        gz_life gl
        on gl.Taxon = tp.url;
    
    点赞 评论

相关推荐