我想以某种方式合并MySQL 5.6的结果。这个想法是从每个id的最新和最旧行中获取数据。对时间/id值是唯一的。
表格为:
| time | id | titulo | precio | vendidos |
+---------------------+----+----------+--------+----------+
| 2019-10-26 19:12:14 | 1 | apple_a | 2 | 10 |
| 2019-10-26 19:12:14 | 2 | pea | 3 | 7 |
| 2019-10-26 19:12:14 | 3 | orange_a | 1 | 4 |
| 2019-10-28 19:12:14 | 3 | orange_a | 2 | 12 |
| 2019-10-28 19:12:14 | 4 | banana | 5 | 7 |
| 2019-10-28 19:12:14 | 5 | peach | 9 | 1 |
| 2019-10-29 19:12:14 | 1 | apple_b | 2 | 12 |
| 2019-10-29 19:12:14 | 2 | pea | 3 | 9 |
| 2019-10-29 19:12:14 | 3 | orange_b | 2 | 19 |
| 2019-10-29 19:12:14 | 4 | banana | 6 | 14 |
| 2019-10-30 19:12:14 | 1 | apple_b | 3 | 17 |
| 2019-10-30 19:12:14 | 2 | pea | 3 | 11 |
带代码:
-- Get latest rows for each id:
SELECT b.*
FROM (SELECT t.id, MAX(time) AS latest
FROM srapedpubs t GROUP BY id) a
INNER JOIN srapedpubs b ON b.id = a.id AND b.time = a.latest
ORDER BY id ASC
;
-- Get oldest rows for each id:
SELECT b.*
FROM (SELECT t.id, MIN(time) AS oldest
FROM srapedpubs t GROUP BY id) a
INNER JOIN srapedpubs b ON b.id = a.id AND b.time = a.oldest
ORDER BY id ASC
;
结果是:
| time | id | titulo | precio | vendidos |
|---------------------|----|----------|--------|----------|
| 2019-10-30 19:12:14 | 1 | apple_b | 3 | 17 |
| 2019-10-30 19:12:14 | 2 | pea | 3 | 11 |
| 2019-10-29 19:12:14 | 3 | orange_b | 2 | 19 |
| 2019-10-29 19:12:14 | 4 | banana | 6 | 14 |
| 2019-10-28 19:12:14 | 5 | peach | 9 | 1 |
| time | id | titulo | precio | vendidos |
|---------------------|----|----------|--------|----------|
| 2019-10-26 19:12:14 | 1 | apple_a | 2 | 10 |
| 2019-10-26 19:12:14 | 2 | pea | 3 | 7 |
| 2019-10-26 19:12:14 | 3 | orange_a | 1 | 4 |
| 2019-10-28 19:12:14 | 4 | banana | 5 | 7 |
| 2019-10-28 19:12:14 | 5 | peach | 9 | 1 |
SQL Fiddle:
http://sqlfiddle.com/#!9/a9fafc/1
如何合并这两个选择以从最旧和最新的行中获取数据?最好去掉相同的最老和最新的行(如id 5,"桃"(
所需输出:
| time | id | titulo | precio | vendidos | oldest_time | oldest_precio | oldest_vendidos |
|---------------------|----|----------|--------|----------|---------------------|---------------|-----------------|
| 2019-10-30 19:12:14 | 1 | apple_b | 3 | 17 | 2019-10-26 19:12:14 | 2 | 10 |
| 2019-10-30 19:12:14 | 2 | pea | 3 | 11 | 2019-10-26 19:12:14 | 3 | 7 |
| 2019-10-29 19:12:14 | 3 | orange_b | 2 | 19 | 2019-10-26 19:12:14 | 1 | 4 |
| 2019-10-29 19:12:14 | 4 | banana | 6 | 14 | 2019-10-28 19:12:14 | 5 | 7 |
我不明白这是怎么做到的。我尝试了一些结果不正确的事情。这里有人知道怎么做吗?
这是一个适合您的解决方案吗:
select * from (
SELECT b.*
FROM (SELECT t.id, MAX(time) AS latest
FROM srapedpubs t GROUP BY id) a
INNER JOIN srapedpubs b ON b.id = a.id AND b.time = a.latest
ORDER BY id ASC) one_t
left join
(SELECT b.*
FROM (SELECT t.id, MIN(time) AS oldest
FROM srapedpubs t GROUP BY id) a
INNER JOIN srapedpubs b ON b.id = a.id AND b.time = a.oldest
ORDER BY id ASC) two_t
on one_t.id = two_t.id
where one_t.vendidos <> two_t.vendidos
演示
因此,结果与您的问题相同:
select one_t.time
, one_t.id
, one_t.titulo
, one_t.precio
, one_t.vendidos
, two_t.time as oldest_time
, two_t.precio as oldest_precio
, two_t.vendidos as oldest_vendidos from (
SELECT b.*
FROM (SELECT t.id, MAX(time) AS latest
FROM srapedpubs t GROUP BY id) a
INNER JOIN srapedpubs b ON b.id = a.id AND b.time = a.latest
ORDER BY id ASC) one_t
left join
(SELECT b.*
FROM (SELECT t.id, MIN(time) AS oldest
FROM srapedpubs t GROUP BY id) a
INNER JOIN srapedpubs b ON b.id = a.id AND b.time = a.oldest
ORDER BY id ASC) two_t
on one_t.id = two_t.id
where one_t.vendidos <> two_t.vendidos
演示
您就快到了。现在,您有两个查询返回所需的信息。你只需要把它们结合在一起。
SELECT a.latest as time,a.id,a.titulo,a.precio,a.vendidos,b.oldest as oldest_time, b.precio as oldest_precio, b.vendidos as oldest_vendidos
FROM (
SELECT id, MAX(time) AS latest, title, precious, vendidos
FROM srapedpubs t
GROUP BY id
) a
INNER JOIN (
SELECT id, MAX(time) AS oldest, title, precious, vendidos
FROM srapedpubs
GROUP BY id
) b
ON b.id=a.id
WHERE b.oldest <> a.latest
ORDER BY id ASC;