有两个结构完全相同的表 odds,odds_his
内有字段OddsID(自动增长),id1,id2,id3
我要从odds,odds_his这两个表中搜索 id1 in (1,2,3)的并且group by id1,id2,id3的最新的数据(OddsID大的为新)
如何写这个sql?
内有字段OddsID(自动增长),id1,id2,id3
我要从odds,odds_his这两个表中搜索 id1 in (1,2,3)的并且group by id1,id2,id3的最新的数据(OddsID大的为新)
如何写这个sql?
from odds
where id1=1 or id1=2 or id1=3
order by oddsid desc
limit 1,1
# 表的结构 `odds_his`
#CREATE TABLE `odds_his` (
`OddsID` int(11) unsigned NOT NULL auto_increment,
`id1` int(11) NOT NULL default '0',
`id2` int(11) NOT NULL default '0',
`id3` int(11) NOT NULL default '0',
`hodds` varchar(10) NOT NULL default '',
`godds` varchar(10) NOT NULL default '',
PRIMARY KEY (`OddsID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;#
# 导出表中的数据 `odds_his`
#INSERT INTO `odds_his` VALUES (10, 101, 10, 0, '1.2', '1.3');
INSERT INTO `odds_his` VALUES (11, 101, 10, 0, '1.4', '1.5');
INSERT INTO `odds_his` VALUES (12, 101, 10, 1, '1.5', '1.6');
INSERT INTO `odds_his` VALUES (13, 102, 10, 0, '1.6', '1.7');
INSERT INTO `odds_his` VALUES (14, 102, 11, 0, '1.8', '1.9');
INSERT INTO `odds_his` VALUES (15, 103, 10, 0, '1.9', '2.0');
INSERT INTO `odds_his` VALUES (16, 103, 10, 0, '2.0', '2.1');
[code][code=SQL]#
# 表的结构 `odds`
#CREATE TABLE `odds` (
`OddsID` int(11) unsigned NOT NULL auto_increment,
`id1` int(11) NOT NULL default '0',
`id2` int(11) NOT NULL default '0',
`id3` int(11) NOT NULL default '0',
`hodds` varchar(10) NOT NULL default '',
`godds` varchar(10) NOT NULL default '',
PRIMARY KEY (`OddsID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;#
# 导出表中的数据 `odds`
#INSERT INTO `odds` VALUES (17, 103, 10, 1, '3.1', '3.2');
INSERT INTO `odds` VALUES (18, 103, 10, 1, '3.5', '3.6');要得到的结果:
11 101 10 0 1.4 1.5
12 101 10 1 1.5 1.6
13 102 10 0 1.6 1.7
14 102 11 0 1.8 1.9
16 103 10 0 2.0 2.1
18 103 10 1 3.5 3.6
from `odds_his` t1
where not exists (select OddsID from `odds_his` where id1=t1.id1 and id2=t1.id2 and id3=t1.id3 and OddsID>t1.OddsID)
and not exists (select OddsID from `odds` where id1=t1.id1 and id2=t1.id2 and id3=t1.id3 and OddsID>t1.OddsID)
union all
select *
from `odds` t2
where not exists (select OddsID from `odds_his` where id1=t2.id1 and id2=t2.id2 and id3=t2.id3 and OddsID>t2.OddsID)
and not exists (select OddsID from `odds` where id1=t2.id1 and id2=t2.id2 and id3=t2.id3 and OddsID>t2.OddsID)
当 id1 in (101,102,103)时,结果
11 101 10 0 1.4 1.5
12 101 10 1 1.5 1.6
13 102 10 0 1.6 1.7
14 102 11 0 1.8 1.9
16 103 10 0 2.0 2.1
18 103 10 1 3.5 3.6 当 id1 in (101,102)时,结果
11 101 10 0 1.4 1.5
12 101 10 1 1.5 1.6
13 102 10 0 1.6 1.7
14 102 11 0 1.8 1.9 当 id1 in (101,103)时,结果
11 101 10 0 1.4 1.5
12 101 10 1 1.5 1.6
16 103 10 0 2.0 2.1
18 103 10 1 3.5 3.6
from `odds_his` t1
where id1 in (101,102)
and not exists (select OddsID from `odds_his` where id1=t1.id1 and id2=t1.id2 and id3=t1.id3 and OddsID>t1.OddsID)
and not exists (select OddsID from `odds` where id1=t1.id1 and id2=t1.id2 and id3=t1.id3 and OddsID>t1.OddsID)
union all
select *
from `odds` t2
where id1 in (101,102)
and not exists (select OddsID from `odds_his` where id1=t2.id1 and id2=t2.id2 and id3=t2.id3 and OddsID>t2.OddsID)
and not exists (select OddsID from `odds` where id1=t2.id1 and id2=t2.id2 and id3=t2.id3 and OddsID>t2.OddsID)