表 1 table1 字段 t记录 11000
11001
11002表2 table2 字段 t type time记录 11000 0 8:43
11000 1 8:43
11000 0 9:40
11000 1 9:40 11001 0 8:43
11001 1 9:30 11002 0 8:20
11002 1 9:10
11002 0 10:30希望去到结果如下 11000 0 9:40
11000 1 9: 40
11001 0 8:43
11001 1 9:30 11002 0 10:30
11002 1 9:10意思是 table2 记录了一些变动过程, select的时候 选择type 里时间最新的那个记录,其他的舍弃请教这个结果如何实现。谢谢。
11001
11002表2 table2 字段 t type time记录 11000 0 8:43
11000 1 8:43
11000 0 9:40
11000 1 9:40 11001 0 8:43
11001 1 9:30 11002 0 8:20
11002 1 9:10
11002 0 10:30希望去到结果如下 11000 0 9:40
11000 1 9: 40
11001 0 8:43
11001 1 9:30 11002 0 10:30
11002 1 9:10意思是 table2 记录了一些变动过程, select的时候 选择type 里时间最新的那个记录,其他的舍弃请教这个结果如何实现。谢谢。
忘了补充了。有时候 table2里没有记录比如
记录 11003
表2 table2 字段 t type time 记录 11000 0 8:43
11000 1 8:43
11000 0 9:40
11000 1 9:40 也要显示 11003 0 0
11003 1 0 假设就两个type的话。
table2 是针对table1记录的,所以抛开table1是不行的。不知道我的意思表达清楚没有。
(SELECT TIME FROM ty2 WHERE TYPE=0 AND a.t=t ORDER BY TIME DESC LIMIT 1) AS a0,
(SELECT TIME FROM ty2 WHERE TYPE=1 AND a.t=t ORDER BY TIME DESC LIMIT 1) AS a1
FROM ty1 a
t1.*,
t2.`time`
from
(select * from table1 t1,(select distinct `type` from table2)t) t1
left join
(select * from table2 t2 where not exists(select 1 from table2 where t=t2.t and `type`=t2.`type` and `time`>t2.`time`)) t2
on
t1.t=t2.t and t1.`type`=t2.`type`
order by
t1.t
mysql> select * from t1;
+-------+
| t |
+-------+
| 11000 |
| 11001 |
| 11002 |
| 11003 |
+-------+
4 rows in set (0.00 sec)mysql> select * from t2;
+-------+------+----------+
| t | type | ttime |
+-------+------+----------+
| 11000 | 0 | 08:43:00 |
| 11000 | 1 | 08:43:00 |
| 11000 | 0 | 09:40:45 |
| 11000 | 1 | 09:40:30 |
| 11001 | 0 | 08:43:20 |
| 11001 | 1 | 09:30:30 |
| 11002 | 0 | 08:20:20 |
| 11002 | 1 | 09:10:32 |
| 11002 | 0 | 10:30:00 |
+-------+------+----------+
9 rows in set (0.00 sec)
mysql> select t1.t t,if(t2.type is null,0,t2.type) type,
-> if(max(t2.ttime) is null,0,max(t2.ttime)) ttime
-> from t2 right join t1
-> on t2.t=t1.t
-> group by t2.t,t2.type;
+-------+------+----------+
| t | type | ttime |
+-------+------+----------+
| 11003 | 0 | 0 |
| 11000 | 0 | 09:40:45 |
| 11000 | 1 | 09:40:30 |
| 11001 | 0 | 08:43:20 |
| 11001 | 1 | 09:30:30 |
| 11002 | 0 | 10:30:00 |
| 11002 | 1 | 09:10:32 |
+-------+------+----------+
7 rows in set (0.00 sec)
SELECT a.t,MAX(a.time),a.type FROM ty2 a WHERE TYPE=0 GROUP BY a.t
UNION ALL
SELECT a.t,MAX(a.time),a.type FROM ty2 a WHERE TYPE=1 GROUP BY a.t) a2
ON a3.t=a2.t ORDER BY a3.t,a2.type