php - Select Statement Conditional WHERE Clause - 如何在同一查询中添加不受 WHERE 子句影响的变量
问题描述
我有这个 SQL 查询:如果可能的话,我需要某种条件 WHERE 子句,以便在除此行下方之外的所有内容上使用 WHERE 子句 -> $row['value_count_deaths']。
我不希望这个被过滤并在被过滤之前显示整数。这可能在同一个查询中吗?如果是这样……怎么办?
IMG:我想在他们的名字后面累积死亡数字,必须在WHERE子句之外
代码重点:
echo "<td style='text-align: center'>" . $row['pe_DataPlayers_lastname'] . " " . $row['value_count_deaths'] . "</td>";
所有整体编码查询语句:
echo "<h3> All Pilot Statistics (Realistic/Hardcore) ... only populate stats after last pilot death for each pilot</h3>";
if ($result = $mysqli->query("SELECT a.`pe_LogEvent_pilotname`,
COUNT(a.`pe_LogEvent_deaths`) AS value_count_deaths,
COUNT(a.`pe_LogEvent_kills_planes`) AS value_count_kills_planes,
COUNT(a.`pe_LogEvent_kills_helicopters`) AS value_count_kills_helicopters,
COUNT(a.`pe_LogEvent_kills_armor`) AS value_count_kills_armor,
b.`pe_DataPlayers_lastname`,
b.`pe_DataPlayers_updated`,
b.`pe_DataPlayers_id`,
c.`pe_LastPilotDeath_pilotname`,
c.`pe_LastPilotDeath_datetime`
FROM `pe_LogEvent` AS a
INNER JOIN `pe_DataPlayers` AS b
ON a.pe_LogEvent_pilotname = b.pe_DataPlayers_lastname
LEFT JOIN `pe_LastPilotDeath` AS c
ON b.pe_DataPlayers_lastname=c.pe_LastPilotDeath_pilotname
WHERE a.`pe_LogEvent_datetime` > c.`pe_LastPilotDeath_datetime`
GROUP BY b.`pe_DataPlayers_lastname`
ORDER BY b.`pe_DataPlayers_updated` DESC")) {
echo "<table>";
echo "<table class='table_stats'>";
echo "<tr class='table_header'><th>Pilot</th><th>Last Flight Log</th><th>A2A Kills</th><th>A2G Kills</th><th>Sorties</th><th>Landing%</th><th>Deaths</th><th>Ejections</th><th>Crashes</th><th>PVP Kills</th><th>Team Kills</th><th>Planes</th><th>Helos</th><th>Armor</th><th>Light Vehicles</th><th>UnArmored</th><th>Air Defense</th><th>Artillery</th><th>Infantry</th><th>Ships</th><th>Structures</th></tr>"; // First Code
while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td style='text-align: center'>" . $row['pe_DataPlayers_lastname'] . " " . $row['value_count_deaths'] . "</td>";
echo "<td style='text-align: center'>" .
$row['value_count_deaths'] . "</td>";
echo "<td style='text-align: center'>" . $row['value_count_ejections'] . "</td>";
echo "<td style='text-align: center'>" . $row['value_count_crashes'] . "</td>";
//echo "<td style='text-align: center'>" . $row['value_sum_pvp'] . "</td>";
echo "</tr>";
}
echo "</table>";
$result->close();
}
解决方案
一种典型的方法是使用子查询来创建标志。
您的代码看起来像 MySQL,因此它使用 MySQL 语法约定:
SELECT SELECT pe_LogEvent_pilotname, pe_LogEvent_friendly_fire_killer,
COUNT(*) as total,
SUM(pe_LogEvent_crashes IS NOT NULL AND flag) AS value_count_crashes,
. . .
FROM (SELECT le.*,
. . . , -- other columns you want
(le.pe_LogEvent_datetime > lpd.pe_LastPilotDeath_datetime) as flag
FROM pe_LogEvent le JOIN
pe_DataPlayers dp
ON le.pe_LogEvent_pilotname = dp.pe_DataPlayers_lastname LEFT JOIN
pe_LastPilotDeath lpd
ON dp.pe_DataPlayers_lastname = lpd.pe_LastPilotDeath_pilotname
) x
GROUP BY . . .
推荐阅读
- r - 解释 p 值 线性混合模型
- java - 如何超时 queueConnectionFactory 创建连接
- javascript - 为什么这个 React 教程 App.js 缺少导入,如 Mozilla 教程中所示?
- reactjs - 如何使用 expo 相机在反应 natie 中实现对焦点的点击?
- amazon-web-services - 用户数据在自定义 AMI(基于 Windows 2019 基础 AMI)中不起作用?
- c++ - 在 C++ 中放置指针和获取指针
- angular - 将由多个表单组组成的表单数组动态绑定到Angular 12的反应形式的模板时出错
- amazon-web-services - 如何跨多个 AWS Cloudwatch 日志流查找我的 Python lambda 函数的错误日志?
- python - 如何以管理员形式显示模型的外键字段
- javascript - Livewire Competent 重新渲染时更新 Alpinejs 绑定