- 爱易网页
-
MySQL教程
- 关于四表连接来达到横向显示同一个字段中不同的内容,该怎么解决
日期:2014-05-16 浏览次数:20804 次
关于四表连接来达到横向显示同一个字段中不同的内容
这是一个sql语句
String sql="select o.SourceIP,o.DestinationIP,o.Content as'user',j.Content,p.Content,q.Content from pop3 o left join pop3 j on j.SourceIP=o.SourceIP left join pop3 p on p.SourceIP=o.SourceIP left join pop3 q on q.SourceIp=o.SourceIP where o.Content like 'USER%'and j.Content like 'PASS%'and p.Content like 'LIST%'and q.Content like 'Received:%'";
这是我师姐留得注释 //四表连接来达到横向显示同一个字段中不同的内容
这是表pop3
表3-12 POP3表
字段名 packetnumber Time SourceIP DestinationIP
含义 编号 时间 源IP地址 目的IP地址
字段名 SourcePort DestinationPort Content Mean
含义 源端口 目的端口 邮件内容 命令含义
最后是要获取邮箱用户名,用户密码,邮件列表,邮件内容
我真的搞不懂上面的sql语句查询的是什么??
谁能给我讲讲
最好讲的详细点
------解决方案--------------------
create table pop3(
`packetnumber` int(30) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号' ,
`Time` datetime default null COMMENT '时间',
`SourceIP` char(20) default null COMMENT '源IP地址',
`DestinationIP` char(20) default null COMMENT '目的IP地址',
`SourcePort` int(8) default null COMMENT '源端口',
`DestinationPort` int(8) default null COMMENT '目的端口',
`Content` TEXT default null comment '邮件内容',
`Mean` char(20) default null comment '命令含义',
PRIMARY KEY (`packetnumber`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312
select o.SourceIP,o.DestinationIP,
o.Content as'user',j.Content,p.Content,
q.Content
from pop3 o
left join pop3 j on j.SourceIP=o.SourceIP
left join pop3 p on p.SourceIP=o.SourceIP
left join pop3 q on q.SourceIp=o.SourceIP
where
o.Content like 'USER%'and
j.Content like 'PASS%'and
p.Content like 'LIST%'and
q.Content like 'Received:%';
这个句子的含义是查询邮箱用户名,用户密码,邮件列表,邮件内容,自连接了4次,是为了在一条中显示同时显示4个content,
测试数据:
INSERT INTO `pop3` VALUES (1,'2008-10-01 10:30:00','192.168.1.132','192.168.1.155',367,47,'USER123123','');
INSERT INTO `pop3` VALUES (2,'2008-10-01 10:30:00','192.168.1.132','192.168.1.155',367,47,'PASS123123','');
INSERT INTO `pop3` VALUES (3,'2008-10-01 10:30:00','192.168.1.132','192.168.1.155',367,47,'Received:123123','');
INSERT INTO `pop3` VALUES (4,'2008-10-01 10:30:00','192.168.1.132','192.168.1.155',367,47,'LIST123123','');
INSERT INTO `pop3` VALUES (5,'2008-10-01 10:30:00','192.168.1.134','192.168.1.155',367,47,'USER123123','');
INSERT INTO `pop3` VALUES (8,'2008-10-01 10:30:00','192.168.1.134','192.168.1.155',367,47,'PASS123123','');
INSERT INTO `pop3` VALUES (9,'2008-10-01 10:30:00','192.168.1.134','192.168.1.155',367,47,'LIST123123','');
INSERT INTO `pop3` VALUES (10,'2008-10-01 10:30:00','192.168.1.134','192.168.1.155',367,47,'Received:123123','');
INSERT INTO `pop3` VALUES (11,'2008-10-01 10:30:00','192.168.1.132','192.168.1.156',367,47,'USER123123','');
INSERT INTO `pop3` VALUES (12,'2008-10-01 10:30:00','192.168.1.132','192.168.1.156',367,47,'PASS123123','');
INSERT INTO `pop3` VALUES (13,'2008-10-01 10:30:00','192.168.1.132','192.168.1.156',367,47,'LIST123123','');
INSERT INTO `pop3` VALUES (14,'2008-10-01 10:30:00','192.168.1.132','192.168.1.156',367,47,'Received:123123','');
个人认为句子末尾应该加上group by DestinationIp,SourceIp,这还要看逻辑上怎么安排的
------解决方案--------------------
select o.SourceIP,o.DestinationIP,o.Content as'user',j.Content,p.Content,q.Content from pop3 o
left join pop3 j on j.SourceIP=o.SourceIP
left join pop3 p on p.SourceIP=o.SourceIP
left join pop3 q on q.SourceIp=o.SourceIP
where o.Content like 'USER%'and j.Content like 'PASS%'and p.Content like 'LIST%'and q.Content like 'Received:%'";
这是对POP3表进行自连接查询,得到
SourceIP、DestinationIP、Content内容,条件是SourceIP相同,筛选Content有USER、PASS、LIST、Received
字样的记录