gpt4 book ai didi

php - SQL - 在单个查询中计算多个列并加入

转载 作者:行者123 更新时间:2023-11-29 13:08:59 25 4
gpt4 key购买 nike

我正在尝试从单行中获取具有“N/A”以外的值的字段(AAA - ZZZ)的数量,以与基本信息一起显示。我有 2 个查询,它们分别作为 SQL 命令进行查找,但我希望将它们合并在一起以在 foreach 语句中工作。

表结构:

ID  UserID  Date    Assignment  AAA BBB CCC DDD
1 1 1/27/2014 Test 5.25 N/A 4 N/A
2 4 1/27/2014 Test2 N/A N/A 3.5 2.75
3 1 1/29/2014 Test3 1.25 N/A N/A 4.5

例如,使用上面的信息,计数将为:

ID 1, Count = 3
ID 2, Count = 2
ID 3, Count = 1

PHP 表代码:

    foreach ($pdo->query($sql) as $row) {
echo '<tr>';
echo '<td>'. $row['Date'] . '</td>';
echo '<td>'. $row['UserName'] . '</td>';
echo '<td>'. $row['Assignment'] . '</td>';
echo '<td>'. $row['Count'] . '</td>';
echo '</tr>';
}
}

检索查询:

$sql = "SELECT db_log.ID, CONCAT(db_users.FName, ' ', db_users.LName) AS UserName, db_log.Date, db_log.Assignment
FROM `db_log`
INNER JOIN `db_users` ON
db_log.UserID=db_users.ID
ORDER BY `ID` DESC LIMIT 0,20";

替代查询:

$sql = "SELECT db_log.ID, CONCAT(db_users.FName, ' ', db_users.LName) AS UserName, db_log.Date, db_log.Assignment
FROM `db_log`, `db_users`
WHERE db_log.UserID=db_users.ID
ORDER BY `ID` DESC LIMIT 0,20";

计数查询:请注意,ID=1 应该是第一个查询的 ID:

SELECT COUNT(AAA) FROM (
SELECT `AAA` FROM `db_log` WHERE `AAA` <> 'N/A' AND `ID`=1 UNION ALL
SELECT `BBB` FROM `db_log` WHERE `BBB` <> 'N/A' AND `ID`=1 UNION ALL
SELECT `CCC` FROM `db_log` WHERE `CCC` <> 'N/A' AND `ID`=1 UNION ALL
SELECT `DDD` FROM `db_log` WHERE `DDD` <> 'N/A' AND `ID`=1 UNION ALL
SELECT `EEE` FROM `db_log` WHERE `EEE` <> 'N/A' AND `ID`=1) AS A

我研究过联接和其他解决方法,但运气不佳。预先感谢您的帮助:)

最佳答案

以下内容应为您提供单个查询中非 N/A 字段的计数。

   SELECT id, CONCAT(db_users.FName, ' ', db_users.LName) AS UserName,
(IF (aaa = 'N/A', 0, 1) +
IF (bbb = 'N/A', 0, 1) +
IF (ccc = 'N/A', 0, 1) +
IF (ddd = 'N/A', 0, 1) ) AS count
FROM db_log
JOIN db_users on db_log.userId = db_users.userId

我设置了一个 SqlFiddle 来玩一下这里:http://sqlfiddle.com/#!2/d6990/5

关于php - SQL - 在单个查询中计算多个列并加入,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/22340655/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com