下面的mysql查询
SELECT *
FROM alerts_list l, alerts_data d, alerts_push_data p
WHERE p.push_data_hash = d.alerts_data_hash
AND p.push_data_alert_id = l.alerts_id
AND d.alerts_data_id = l.alerts_id
AND d.alerts_data_hash = 'JiaYRSVNZxgE'
通过连接三个表显示
JiaYRSVNZxgE的结果。下面是我使用的表和要在它们之间连接的列：
表警报列表
列：
alerts_id表警报推送数据
列：
push_data_alert_id列：
push_data_hash表警报数据
列：
alerts_data_id列：
alerts_data_hash我想要实现的是：
将
push_data_alert_id与alerts_id连接将
alerts_data_id与alerts_id连接但只显示
alerts_data_hash和push_data_hash是"abcdef"的结果不幸的是我的查询结果没有找到结果，但现实中有结果。
我做错什么了？
最佳答案
您可以使用MySQL连接很容易地执行该操作，如下所示：
<?php
// USING NORMAL JOIN:
// WE NOW ADD AN EXTRA LAYER (THE VARIABLE $fldVal)
// IN THE CASE THAT YOUR VALUE ('JiaYRSVNZxgE') IS DYNAMIC...
$fldVal = 'JiaYRSVNZxgE';
$sql = "SELECT DISTINCT *
FROM alerts_list l
JOIN alerts_data d ON d.alerts_data_id=l.alerts_id
JOIN alerts_push_data p ON p.push_data_alert_id=l.alerts_id
WHERE d.alerts_data_hash='" . $fldVal . "'";
// USING LEFT JOIN:
// WE NOW ADD AN EXTRA LAYER (THE VARIABLE $fldVal)
// IN THE CASE THAT YOUR VALUE ('JiaYRSVNZxgE') IS DYNAMIC...
$fldVal = 'JiaYRSVNZxgE';
$sql = "SELECT DISTINCT *
FROM alerts_list l
LEFT JOIN alerts_data d ON d.alerts_data_id=l.alerts_id
LEFT JOIN alerts_push_data p ON p.push_data_alert_id=l.alerts_id
WHERE d.alerts_data_hash='" . $fldVal . "'";
// USING INNER JOIN:
// WE NOW ADD AN EXTRA LAYER (THE VARIABLE $fldVal)
// IN THE CASE THAT YOUR VALUE ('JiaYRSVNZxgE') IS DYNAMIC...
$fldVal = 'JiaYRSVNZxgE';
$sql = "SELECT DISTINCT *
FROM alerts_list l
INNER JOIN alerts_data d ON d.alerts_data_id=l.alerts_id
INNER JOIN alerts_push_data p ON p.push_data_alert_id=l.alerts_id
WHERE d.alerts_data_hash='" . $fldVal . "'";
：：但使用GROUP BY子句进行了新的更新：
<?php
// WE NOW ADD AN EXTRA LAYER (THE VARIABLE $fldVal)
// IN THE CASE THAT YOUR VALUE ('JiaYRSVNZxgE') IS DYNAMIC...
$fldVal = 'JiaYRSVNZxgE';
$sql2 = "SELECT DISTINCT *
FROM alerts_list AS A_LIST
LEFT JOIN alerts_push_data A_PUSH ON A_PUSH.push_data_alert_id=A_LIST.alerts_id
LEFT JOIN alerts_data A_DATA ON A_DATA.alerts_data_hash=A_PUSH.push_data_hash
WHERE A_DATA.alerts_data_hash='" . $fldVal . "'
GROUP BY A_LIST.alerts_id";
测试用例查询
SELECT DISTINCT *
FROM alerts_list AS A_LIST
LEFT JOIN alerts_push_data A_PUSH ON A_PUSH.push_data_alert_id=A_LIST.alerts_id
LEFT JOIN alerts_data A_DATA ON A_DATA.alerts_data_hash=A_PUSH.push_data_hash
WHERE A_DATA.alerts_data_hash='iSg2loGJDaWs'
GROUP BY A_LIST.alerts_id
结果
这是意料之中的，因为我在所有其他表中只模拟了2行，除了alerts_list有10行。
转储上述查询的结果
表：警报列表
表：警报数据
表：警报推送数据
关于php - 尝试加入3个mysql表，但没有得到预期的结果。什么错，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37468538/