表oid mid a b c d e
1 1 0.1 0.2 0.3 0.4 0.5
2 2 0.2 0.1 0.4 0.2 0.1表st
id mid type value time
1 1 f 1.1 2009-10-09 12:12:00
2 1 f 1.2 2009-10-09 12:14:00
3 2 g 2.1 2009-10-09 12:11:00
4 2 f 1.3 2009-10-09 12:12:00
5 1 g 1.3 2009-10-09 12:13:00
两个表 以 mid 关联希望select的结果如下id mid a b c d e f g
1 1 0.1 0.2 0.3 0.4 0.5 1.2 1.3
2 2 0.2 0.1 0.4 0.2 0.1 1.3 2.1
f 和g 是 st里 时间 时间比较大的。
1 1 0.1 0.2 0.3 0.4 0.5
2 2 0.2 0.1 0.4 0.2 0.1表st
id mid type value time
1 1 f 1.1 2009-10-09 12:12:00
2 1 f 1.2 2009-10-09 12:14:00
3 2 g 2.1 2009-10-09 12:11:00
4 2 f 1.3 2009-10-09 12:12:00
5 1 g 1.3 2009-10-09 12:13:00
两个表 以 mid 关联希望select的结果如下id mid a b c d e f g
1 1 0.1 0.2 0.3 0.4 0.5 1.2 1.3
2 2 0.2 0.1 0.4 0.2 0.1 1.3 2.1
f 和g 是 st里 时间 时间比较大的。
(select `value` from st where mid=o.mid and type='f' order by `time` desc limit 1) as f,
(select `value` from st where mid=o.mid and type='g' order by `time` desc limit 1) as g
from o
`id` int,
`mid` int,
`a` numeric(2,1),
`b` numeric(2,1),
`c` numeric(2,1),
`d` numeric(2,1),
`e` numeric(2,1)
);
insert `o` values(1,1,0.1,0.2,0.3,0.4,0.5);
insert `o` values(2,2,0.2,0.1,0.4,0.2,0.1);create table `st`(
`id` int,
`mid` int,
`type` varchar(1),
`value` numeric(2,1),
`time` datetime
);
insert `st` values(1,1,'f',1.1,'2009-10-09 12:12:00');
insert `st` values(2,1,'f',1.2,'2009-10-09 12:14:00');
insert `st` values(3,2,'g',2.1,'2009-10-09 12:11:00');
insert `st` values(4,2,'f',1.3,'2009-10-09 12:12:00');
insert `st` values(5,1,'g',1.3,'2009-10-09 12:13:00');select
o.*,
st.f,
st.g
from
o
left join
(
select
mid,
max(if(type='f',value,0)) as f,
max(if(type='g',value,0)) as g
from
st
where
not exists(select 1 from st s where s.mid=st.mid and s.type=st.type and s.time>st.time)
group by mid
) as st
on o.mid=st.middrop table o,st/**
id mid a b c d e f g
------ ------ ------ ------ ------ ------ ------ ------ ------
1 1 0.1 0.2 0.3 0.4 0.5 1.2 1.3
2 2 0.2 0.1 0.4 0.2 0.1 1.3 2.1
**/
select
o.*,
st.f,
st.g
from
o
left join
(
select
mid,
max(if(type='f',value,0)) as f,
max(if(type='g',value,0)) as g
from
st
where
time=(select max(time) from st s where s.mid=st.mid and s.type=st.type)
group by mid
) as st
on o.mid=st.mid
mysql> select * from o;
+------+------+------+------+------+------+------+
| id | mid | a | b | c | d | e |
+------+------+------+------+------+------+------+
| 1 | 1 | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 |
| 2 | 2 | 0.2 | 0.1 | 0.4 | 0.2 | 0.1 |
+------+------+------+------+------+------+------+
2 rows in set (0.00 sec)mysql> select * from st;
+------+------+------+-------+---------------------+
| id | mid | type | value | ttime |
+------+------+------+-------+---------------------+
| 1 | 1 | f | 1.1 | 2009-10-09 12:12:00 |
| 2 | 1 | f | 1.2 | 2009-10-09 12:14:00 |
| 3 | 2 | g | 2.1 | 2009-10-09 12:11:00 |
| 4 | 2 | f | 1.3 | 2009-10-09 12:12:00 |
| 5 | 1 | g | 1.3 | 2009-10-09 12:13:00 |
+------+------+------+-------+---------------------+
5 rows in set (0.00 sec)
mysql> select o.*,st1.f,st2.g
-> from o,
-> (select mid,max(value) as f from st
-> where type='f' group by mid) st1,
-> (select mid,max(value) as g from st
-> where type='g' group by mid) st2
-> where o.mid = st1.mid and o.mid=st2.mid;
+------+------+------+------+------+------+------+------+------+
| id | mid | a | b | c | d | e | f | g |
+------+------+------+------+------+------+------+------+------+
| 1 | 1 | 0.1 | 0.2 | 0.3 | 0.4 | 0.5 | 1.2 | 1.3 |
| 2 | 2 | 0.2 | 0.1 | 0.4 | 0.2 | 0.1 | 1.3 | 2.1 |
+------+------+------+------+------+------+------+------+------+
2 rows in set (0.00 sec)
(重开一贴)ACMAIN_CHM 请答,我选择用你的语句简单好理解,当然其他的也不错。谢谢大家