MySQL UNION 与 COUNT 别名

发布于 2024-09-12 01:39:03 字数 780 浏览 3 评论 0原文

我正在尝试将两个单独的表中的两个计数检索到一个 SQL 查询中以与 PHP 一起使用。这是当前的 SQl 查询：

SELECT COUNT(entryid) AS total FROM rh_entries UNION SELECT COUNT(pentryid) AS attended FROM rh_playerentries WHERE playerid=79

这是我用来利用数据的 PHP： $结果 = mysql_query($query);

$attendance = mysql_fetch_assoc($result);
echo "Total: " . $attendance['total'] 
 . " Attended: " . $attendance['attended'] 
 . " Percentage: " 
 . (int)$attendance['total'] / (int)$attendance['attended'] 
 . " <br />";

我得到这个输出：

Warning: Division by zero in /home/content/g/V/i/gViscardi/html/guilds/sanctum/raidinfo/player.php on line 41
Total: 6 Attended: Percentage:

显然 $attendance['attended'] 没有正确设置。我是否遗漏了 UNION 或 COUNT 或 AS 的工作原理？

原文

I am trying to retrieve two counts from two separate tables into one SQL query to use with PHP. This is the current SQl query:

SELECT COUNT(entryid) AS total FROM rh_entries UNION SELECT COUNT(pentryid) AS attended FROM rh_playerentries WHERE playerid=79

This is the PHP I am using to utilize the data:
$result = mysql_query($query);

$attendance = mysql_fetch_assoc($result);
echo "Total: " . $attendance['total'] 
 . " Attended: " . $attendance['attended'] 
 . " Percentage: " 
 . (int)$attendance['total'] / (int)$attendance['attended'] 
 . " <br />";

I am getting this output:

Warning: Division by zero in /home/content/g/V/i/gViscardi/html/guilds/sanctum/raidinfo/player.php on line 41
Total: 6 Attended: Percentage:

Apparently the $attendance['attended'] is not being set properly. Am I missing something on how UNION or COUNT or AS works?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

桃扇骨 2024-09-19 01:39:03

Union 将两个查询的内容合并到一个表中。您的查询具有不同的列名称，因此合并将无法正常工作。你会想要这样的东西：

SELECT 
    (SELECT COUNT(entryid) FROM rh_entries) as total, 
    (SELECT COUNT(pentryid) FROM rh_playerentries WHERE playerid=79) as attended;

Union combines the contents of two queries into a single table. Your queries have different column names, so the merge won't work properly. You'll want something like:

SELECT 
    (SELECT COUNT(entryid) FROM rh_entries) as total, 
    (SELECT COUNT(pentryid) FROM rh_playerentries WHERE playerid=79) as attended;

回复收藏 0 原文

青衫儰鉨ミ守葔 2024-09-19 01:39:03

为了扩展 amccausl 的技巧，一旦你有了那个表，你就可以对其进行操作：

select sum(total) from 
(
SELECT COUNT(entryid) FROM rh_entries as total
UNION
SELECT COUNT(pentryid) as total FROM rh_playerentries WHERE playerid=79
)

To expand on amccausl's tip, once you have that single table, you can do operations on it:

select sum(total) from 
(
SELECT COUNT(entryid) FROM rh_entries as total
UNION
SELECT COUNT(pentryid) as total FROM rh_playerentries WHERE playerid=79
)

回复收藏 0 原文

滴情不沾 2024-09-19 01:39:03

select sum(total) from 
(
SELECT COUNT(entryid) total FROM rh_entries
UNION
SELECT COUNT(pentryid) total FROM rh_playerentries WHERE playerid=79
) as t;

select sum(total) from 
(
SELECT COUNT(entryid) total FROM rh_entries
UNION
SELECT COUNT(pentryid) total FROM rh_playerentries WHERE playerid=79
) as t;

回复收藏 0 原文

~没有更多了~