命名一系列子查询（PHP / MySQL）

Question

I figured out how to display a list of duplicate values in a particular database table...

$stm = $pdo->prepare("SELECT URL, COUNT(*) tot
 FROM people
 GROUP BY URL
 HAVING tot > 1");
$stm->execute(array(
  'Total'=>$Total
));

while ($row = $stm->fetch())
{
 $URL_Dupe = $row['URL'];
 $tot = $row['tot'];
 $Dupe2[] = ''.$URL_Dupe.''.$tot.'';
}

I'd like to do the same thing with a series of tables linked together with a UNION ALL clause...

$stm = $pdo->prepare("SELECT 'GZ' AS GSiteID, NULL as Site, 'Life' AS GSection, GZL.Taxon AS URL
 FROM gz_life GZL WHERE GZL.Taxon = :MyURL
 UNION ALL
 SELECT 'All' AS GSiteID, NULL as Site, 'World' AS GSection, GG.Name AS URL FROM gw_geog GG WHERE GG.Name = :MyURL
 UNION ALL
 SELECT 'PX' AS GSiteID, Site, 'People' AS GSection, Ppl.URL FROM people Ppl WHERE Ppl.URL = :MyURL");
$stm->execute(array(
 'MyURL'=>$MyURL
));

In other words, I want to track down any word that appears more than once in a single table or once (or more) in two or more different tables.

But I think I have to first create a single name for all these tables (e.g. "Todo"), then perform the operation on that name, right? How do I do that? I have a similar table that simply ends with "AS X." But I can't do that on this query without getting errors.

Answer 1

You can make the union all query a subquery and then aggregate them like:

select url, count(*)
from ((SELECT 'GZ' AS GSiteID, NULL as Site, 'Life' AS GSection, GZL.Taxon AS URL
       FROM gz_life GZL WHERE GZL.Taxon = :MyURL
      ) UNION ALL
      (SELECT 'All' AS GSiteID, NULL as Site, 'World' AS GSection, GG.Name AS URL
       FROM gw_geog GG WHERE GG.Name = :MyURL
      ) UNION ALL
      (SELECT 'PX' AS GSiteID, Site, 'People' AS GSection, Ppl.URL
       FROM people Ppl WHERE Ppl.URL = :MyURL
      )
     ) t
group by url;

If you really want to meet the condition "I want to track down any word that appears more than once in a single table or once (or more) in two or more different tables". That is equivalent to the count being at least 2, so add a having clause:

select url, count(*)
from ((SELECT 'GZ' AS GSiteID, NULL as Site, 'Life' AS GSection, GZL.Taxon AS URL
       FROM gz_life GZL WHERE GZL.Taxon = :MyURL
      ) UNION ALL
      (SELECT 'All' AS GSiteID, NULL as Site, 'World' AS GSection, GG.Name AS URL
       FROM gw_geog GG WHERE GG.Name = :MyURL
      ) UNION ALL
      (SELECT 'PX' AS GSiteID, Site, 'People' AS GSection, Ppl.URL
       FROM people Ppl WHERE Ppl.URL = :MyURL
      )
     ) t
group by url
having count(*) > 1;