如何使用UNION ALL计算两个查询的结果数

10 浏览
0 Comments

如何使用UNION ALL计算两个查询的结果数

我有这个查询：

    SELECT ava_users.*, 0 AS ord
    FROM ava_friend_requests
    LEFT JOIN ava_users 
    ON ava_friend_requests.from_user = ava_users.id
    WHERE ava_friend_requests.to_user = $user[id]
    UNION ALL
    SELECT ava_users.*, 1 AS ord
    FROM ava_friends
    LEFT JOIN ava_users 
    ON ava_friends.user2 = ava_users.id
    WHERE ava_friends.user1 = $user[id]
    ORDER BY ord
    LIMIT $from, $display_num

你可以看到有两个带有 UNION ALL 的查询。

现在我的问题是：我怎样才能计算EACH（每个）查询？我怎样才能检索每个计数值？我想要一个查询编号1的计数结果以及一个查询编号2的计数结果。

更新：

我想要一个像这样的计数结果：计数1：34，计数2：45

请看这里：select count from multiple tables

admin 更改状态以发布 2023年5月21日
0

2 答案

0 Comments

你可以尝试这样做：\n

select count(*) from (SELECT ava_users.*, 0 AS ord
FROM ava_friend_requests
LEFT JOIN ava_users 
ON ava_friend_requests.from_user = ava_users.id
WHERE ava_friend_requests.to_user = $user[id]
UNION ALL
SELECT ava_users.*, 1 AS ord
FROM ava_friends
LEFT JOIN ava_users 
ON ava_friends.user2 = ava_users.id
WHERE ava_friends.user1 = $user[id]
ORDER BY ord
LIMIT $from, $display_num) as users;

\n这意味着将union all处理成类似于表格的形式，并对所有元素进行计数。

0
0 Comments

如果你想从这两个子查询中计算返回的行数，可以这样：

SELECT 'Query1' as which, count(*) as cnt
FROM ava_friend_requests
LEFT JOIN ava_users 
ON ava_friend_requests.from_user = ava_users.id
WHERE ava_friend_requests.to_user = $user[id]
UNION ALL
SELECT 'Query2', count(*)
FROM ava_friends
LEFT JOIN ava_users 
ON ava_friends.user2 = ava_users.id
WHERE ava_friends.user1 = $user[id];

编辑：

如果想要在限制后进行这个操作，可以将当前查询作为子查询，并进行计数：

select ord, count(*) as cnt
from (SELECT ava_users.*, 0 AS ord
      FROM ava_friend_requests
      LEFT JOIN ava_users 
      ON ava_friend_requests.from_user = ava_users.id
      WHERE ava_friend_requests.to_user = $user[id]
      UNION ALL
      SELECT ava_users.*, 1 AS ord
      FROM ava_friends
      LEFT JOIN ava_users 
      ON ava_friends.user2 = ava_users.id
      WHERE ava_friends.user1 = $user[id]
      ORDER BY ord
      LIMIT $from, $display_num
     ) t
group by ord;

在应用层级别计算ord列可能更容易。

0