PHP的MySQL查询工作不正常
问题描述:
我有一个表名tblnetworkstatus,我有11列PHP的MySQL查询工作不正常
- 编号
- issue_name
- affected_server
- affected_service
- ISSUE_TYPE
- 优先
- 持续时间
- 状态
- 起始日期
- END_DATE
- 描述
我在affected_server越来越ID和affected_service,我在我的数据库正在存储,现在我有三种情况
- 要么都有, affected_server和affected_service已被选中
- 只选择了affected_server
- 只有affected_service选择
我正在从分别使用连接在三个表tblnetworkstatus,tblserver和tblproduct我已经下面的查询从这里
SELECT
m.issue_name ,m.issue_type ,
m.priority ,m.status,m.description ,
m.start_date,m.end_date,m.duration,
s.name as server_name,p.name as product_name
from mod_networkstatus as m
LEFT JOIN tblservers as s ON m.affected_server=s.id
LEFT JOIN tblproducts as p ON m.affected_service=p.id
了tblserver和tblproducts affected_server和affected_service的名字
但是,如果我有受影响的服务器和affected_service的多个id,就像我将1,2,3,4存储在单个列affected_server或affected_service那么我该怎么做?
答
无需使用union,因为它会给很多重复数据
要实现可以通过简单的left join
或inner join
SELECT
m.issue_name ,m.issue_type ,
m.priority ,m.status,m.description ,
m.start_date,m.end_date,m.duration,
s.name as server_name,p.name as product_name
from mod_networkstatus as m
LEFT JOIN tblservers as s ON m.affected_server=s.id
LEFT JOIN tblproducts as p ON m.affected_service=p.id
用于做什么一列中有多个ID
SELECT
m.issue_name ,m.issue_type ,
m.priority ,m.status,m.description ,
m.start_date,m.end_date,m.duration,
s.name as server_name,p.name as product_name
from mod_networkstatus as m
LEFT JOIN tblservers as s ON FIND_IN_SET(m.affected_server,s.id)
LEFT JOIN tblproducts as p ON m.affected_service=p.id
看看这是否适合你。
答
即使您的要求不是很清楚,仍按照您的要求,您可以使用下面的查询。
SELECT m.issue_name ,m.issue_type ,m.priority ,m.status,m.description ,m.start_date,m.end_date,m.duration,s.name AS server_name,p.name AS product_name
FROM tblnetworkstatus m
LEFT JOIN tblservers s ON m.affected_server=s.id
LEFT JOIN tblproducts p ON m.affected_service=p.id
WHERE ((m.affected_server='Y' AND m.affected_service='Y') OR m.affected_server='Y' OR m.affected_service='Y');
注:如果不是你想要的,那么请共享所有3个表样本数据和所需输出,这样我可以帮助你。
根据您的最新更新,您可以尝试它 -
SELECT m.issue_name ,m.issue_type ,m.priority ,m.status,m.description ,m.start_date,m.end_date,m.duration,s.name AS server_name,p.name AS product_name
FROM tblnetworkstatus m
LEFT JOIN tblservers s ON find_in_set(s.id,m.affected_server)
LEFT JOIN tblproducts p ON find_in_set(p.id,m.affected_service)
WHERE ((m.affected_server='Y' AND m.affected_service='Y') OR m.affected_server='Y' OR m.affected_service='Y');
注:但是,这不是一个好主意,因为它不会使用索引和缓慢的过程。
+0
是的,但它给了我重复的记录,我不想; 我想要显示受影响服务器= server1,server2 ,server3等 –
我的荣幸.... :) –
如果我有多个项目选择,然后受感染的服务器列有id的那个1,2,3那么我怎么能做到这一点在单列上的多个ID ... –
你需要使用'field_in_set'就像这样'ON FIND_IN_SET(sometable.id,othertable.ids)' –