原始表结构：
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO | MUL | NULL | |
| clock | int(11) | NO | | 0 | |
| value | bigint(20) unsigned | NO | | 0 | |
| ns | int(11) | NO | | 0 | |
+--------+---------------------+------+-----+---------+-------+
1，查询条件1的sql
mysql> SELECT from_unixtime(clock) as date,value FROM history_uint where itemid=34355 limit 10 ;
+---------------------+-------------+
| date | value |
+---------------------+-------------+
| 2020-12-05 13:40:05 | 24119272824 |
| 2020-12-05 13:45:05 | 22787732696 |
| 2020-12-05 13:50:05 | 22848448840 |
| 2020-12-05 13:55:05 | 22960730112 |
| 2020-12-05 14:00:05 | 23003952456 |
| 2020-12-05 14:05:05 | 23412661728 |
| 2020-12-05 14:10:05 | 23055990464 |
| 2020-12-05 14:15:05 | 22797073752 |
| 2020-12-05 14:20:05 | 22790025048 |
| 2020-12-05 14:25:05 | 22653347256 |
+---------------------+-------------+
10 rows in set (0.00 sec)
2，查询条件2的sql；
mysql> SELECT from_unixtime(clock) as date,value FROM history_uint where itemid=34192 limit 10 ;
+---------------------+------------+
| date | value |
+---------------------+------------+
| 2020-12-05 13:40:05 | 4396917536 |
| 2020-12-05 13:45:05 | 4043267368 |
| 2020-12-05 13:50:05 | 4064662864 |
| 2020-12-05 13:55:05 | 4095379176 |
| 2020-12-05 14:00:05 | 3998435896 |
| 2020-12-05 14:05:05 | 4166576464 |
| 2020-12-05 14:10:05 | 4285009608 |
| 2020-12-05 14:15:05 | 4230219352 |
| 2020-12-05 14:20:05 | 4244117736 |
| 2020-12-05 14:25:05 | 4106498928 |
+---------------------+------------+
10 rows in set (0.00 sec)
需求，想实现如下场景；
+---------------------+------------+-------------+
| date | in | out |
+---------------------+------------+-------------+
| 2020-12-05 13:40:05 | 4396917536 | 24119272824 |
| 2020-12-05 13:45:05 | 4043267368 | 22787732696 |
| 2020-12-05 13:50:05 | 4064662864 | 22848448840 |
| 2020-12-05 13:55:05 | 4095379176 | 22960730112 |
| 2020-12-05 14:00:05 | 3998435896 | 23003952456 |
| 2020-12-05 14:05:05 | 4166576464 | 23412661728 |
| 2020-12-05 14:10:05 | 4285009608 | 23055990464 |
| 2020-12-05 14:15:05 | 4230219352 | 22797073752 |
| 2020-12-05 14:20:05 | 4244117736 | 22790025048 |
| 2020-12-05 14:25:05 | 4106498928 | 22653347256 |
+---------------------+------------+-------------+
10 rows in set (0.00 sec)
求大神不吝赐教！！！
将两个查询的结果通过date做一次关联查询就成了
select
t1.date,t2.value,t1.value
from
( SELECT from_unixtime(clock) as date,value FROM history_uint where itemid=34355) t1
join
(SELECT from_unixtime(clock) as date,value FROM history_uint where itemid=34192) t2
on
t1.date=t2.date
;
为啥两个语句的itemid不一样？
select from_unixtime(clock) as `date`
,tb_in.value as `in`
,tb_in.value as `out`
from history_uint tb_in
left join history_uint tb_out
on from_unixtime(tb_in.clock)=from_unixtime(tb_out.clock)
and tb_in.itemid=34192 and tb_out.itemid=34355;