php - 将 LIMIT 1 添加到 COUNT 查询

Question

这是我用来显示页面的 COUNT 查询:

$sql = "
SELECT SUM(num) as num FROM (
  SELECT
    COUNT(URL) AS num
    , 'World' AS GoSection
    , 'GW' AS MySite
  FROM gw_geog
  WHERE URL = :MyURL AND G1 = 1
UNION ALL
  SELECT
    COUNT(URL) AS num
    , 'World' AS GoSection
    , 'GW' AS MySite
  FROM gw_geog_political
  WHERE URL = :MyURL
 ) AS X";

$stmt = $pdo->prepare($sql);
$stmt->bindParam(':MyURL',$MyURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();

我想做两个改变:

1) 从每一行中删除 WHERE URL = :MyURL 并将其合并到查询末尾的一行中

2) 通过添加 LIMIT 1

消除重复

这就是我现在正在使用的东西。

$sql = "SELECT SUM(num) as num FROM (
 SELECT COUNT(URL) AS num, 'World' AS GoSection, 'GW' AS MySite FROM gw_geog WHERE URL = :MyURL AND G1 = 1
 UNION ALL
 SELECT COUNT(URL) AS num, 'World' AS GoSection, 'GW' AS MySite FROM gw_geog_political WHERE URL = :MyURL
 ) AS X
WHERE X.URL LIKE :MyURL LIMIT 1";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':MyURL',$MyURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();

但是，我收到错误消息Unknown column X.SUM(或 X.URL 等)。

有人可以告诉我正确的方法吗？

Answer 1

使用如下查询，您将获得所需的输出:

 SELECT
    SUM(num) AS num
FROM
(
    SELECT
        COUNT(URL) AS num,
        'World' AS GoSection,
        'GW' AS MySite,
        gw_geog.`URL` as URL  
    FROM
        gw_geog
    WHERE
        URL = :MyURL
    AND G1 = 1
    UNION ALL
        SELECT
            COUNT(URL) AS num,
            'World' AS GoSection,
            'GW' AS MySite,
             gw_geog_political.`URL` as URL
        FROM
            gw_geog_political
        WHERE
            URL = :MyURL
) AS X
WHERE
    X.URL like  :MyURL
LIMIT 1