问题描述:

I have 2 table with columns, looking like this:

Table A

rows, key_a, date_a

1, 'k1', '2015-11-12'

2, 'k2', '2015-11-20'

3, 'k3', '2015-12-01'

Table B

row, key_b, date_b, Code

1, 'k1', '2015-10-12', C1

2, 'k1', '2015-09-12', C2

3, 'k1', '2015-11-01', C3

4, 'k1', '2015-10-20', C4

5, 'k1', '2015-08-19', C5

6, 'k1', '2015-11-02', C6

7, 'k2', '2015-10-12', C7

8, 'k2', '2015-09-12', C8

9, 'k2', '2015-11-01', C9

10, 'k2', '2015-10-20', C10

11, 'k2', '2015-08-19', C11

12, 'k2', '2015-11-02', C12

13, 'k3', '2015-10-12', C13

14, 'k3', '2015-09-12', C14

15, 'k3', '2015-11-01', C15

16, 'k3', '2015-10-20', C16

17, 'k3', '2015-08-19', C17

18, 'k3', '2015-11-02', C18

I want to find row in table B have date_b is first with same key_b and only select data in table B with date_b between from date_a to date_a - 30

It mean:

 'k1' => '2015-11-12' to '2015-10-12' => rows view (1,3,4,6)

'k2' => '2015-11-20' to '2015-10-20' => rows view (9,10,12)

'k3' => '2015-12-01' to '2015-11-01' => rows view (15,18)

Result

key_a, date_a, Code

'k1', '2015-11-12', C1

'k2', '2015-11-20', C10

'k3', '2015-12-01', C15

How can I do this?

网友答案:
SELECT key_a, date_a, code
FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY key_a ORDER BY date_b) AS num
  FROM (
    SELECT  key_a, date_a, date_b, code,
    FROM    table_a AS a 
    JOIN    table_b AS b
    ON      a.key_a = b.key_b
    WHERE date_b BETWEEN DATE(DATE_ADD(TIMESTAMP(date_a), -1, "MONTH")) AND date_a 
  )
)
WHERE num = 1
ORDER BY key_a

Result:

key_a   date_a      code
k1      2015-11-12  C1
k2      2015-11-20  C10
k3      2015-12-01  C15
网友答案:

This is definitely a hacky way to go, but this should work

Select key_a, first(date_a), RIGHT(first(amalgam),3) from( select key_a, date_a, date_b + ' ' + code as amalgam from (
select * from (select 1 as rows,  'k1' as key_a, date('2015-11-12') as date_a),
(select 2 as rows,  'k2' as key_a, date('2015-11-20') as date_a),
(select 3 as rows,  'k3' as key_a, date('2015-12-01') as date_a)
) table_a
join (select * from
(select 1 as rows,  'k1' as key_b, date('2015-10-12') as date_b, 'C1' as code),
(select 2 as rows,  'k1' as key_b, date('2015-09-12') as date_b, 'C2' as code),
(select 3 as rows,  'k1' as key_b, date('2015-11-01') as date_b, 'C3' as code),
(select 4 as rows,  'k1' as key_b, date('2015-10-20') as date_b, 'C4' as code),
(select 5 as rows,  'k1' as key_b, date('2015-08-19') as date_b, 'C5' as code),
(select 6 as rows,  'k1' as key_b, date('2015-11-02') as date_b, 'C6' as code),
(select 7 as rows,  'k2' as key_b, date('2015-10-12') as date_b, 'C7' as code),
(select 8 as rows,  'k2' as key_b, date('2015-09-12') as date_b, 'C8' as code),
(select 9 as rows,  'k2' as key_b, date('2015-11-01') as date_b, 'C9' as code),
(select 10 as rows,  'k2' as key_b, date('2015-10-20') as date_b, 'C10' as code),
(select 11 as rows,  'k2' as key_b, date('2015-08-19') as date_b, 'C11' as code),
(select 12 as rows,  'k2' as key_b, date('2015-11-02') as date_b, 'C12' as code),
(select 13 as rows,  'k3' as key_b, date('2015-10-12') as date_b, 'C13' as code),
(select 14 as rows,  'k3' as key_b, date('2015-09-12') as date_b, 'C14' as code),
(select 15 as rows,  'k3' as key_b, date('2015-11-01') as date_b, 'C15' as code),
(select 16 as rows,  'k3' as key_b, date('2015-10-20') as date_b, 'C16' as code),
(select 17 as rows,  'k3' as key_b, date('2015-08-19') as date_b, 'C17' as code),
(select 18 as rows,  'k3' as key_b, date('2015-11-02') as date_b, 'C18' as code)
) table_b
on table_a.key_a=table_b.key_b
where timestamp(date_b) between DATE_ADD(timestamp(date_a), -1, "MONTH") and timestamp(date_a)) 
group by 1

If you want a cleaner solution, instead of adding a blank between date_b and code, you can always add them without the space and then do a RIGHT(2) instead of a RIGHT(3), depending on the length of 'amalgam'. I much prefer Mikhail's solution here to be honest :)

网友答案:

So this is you might want:

select key_a,first(date_a) as first_date_a,group_concat(code) as all_codes,first(code) as first_code from (
select * from (select 1 as rows,  'k1' as key_a, date('2015-11-12') as date_a),
(select 2 as rows,  'k2' as key_a, date('2015-11-20') as date_a),
(select 3 as rows,  'k3' as key_a, date('2015-12-01') as date_a)
) table_a
join (select * from
 (select 1 as rows,  'k1' as key_b, date('2015-10-12') as date_b, 'C1' as code),
 (select 2 as rows,  'k1' as key_b, date('2015-09-12') as date_b, 'C2' as code),
 (select 3 as rows,  'k1' as key_b, date('2015-11-01') as date_b, 'C3' as code),
 (select 4 as rows,  'k1' as key_b, date('2015-10-20') as date_b, 'C4' as code),
 (select 5 as rows,  'k1' as key_b, date('2015-08-19') as date_b, 'C5' as code),
 (select 6 as rows,  'k1' as key_b, date('2015-11-02') as date_b, 'C6' as code),
 (select 7 as rows,  'k2' as key_b, date('2015-10-12') as date_b, 'C7' as code),
 (select 8 as rows,  'k2' as key_b, date('2015-09-12') as date_b, 'C8' as code),
 (select 9 as rows,  'k2' as key_b, date('2015-11-01') as date_b, 'C9' as code),
 (select 10 as rows,  'k2' as key_b, date('2015-10-20') as date_b, 'C10' as code),
 (select 11 as rows,  'k2' as key_b, date('2015-08-19') as date_b, 'C11' as code),
 (select 12 as rows,  'k2' as key_b, date('2015-11-02') as date_b, 'C12' as code),
 (select 13 as rows,  'k3' as key_b, date('2015-10-12') as date_b, 'C13' as code),
 (select 14 as rows,  'k3' as key_b, date('2015-09-12') as date_b, 'C14' as code),
 (select 15 as rows,  'k3' as key_b, date('2015-11-01') as date_b, 'C15' as code),
 (select 16 as rows,  'k3' as key_b, date('2015-10-20') as date_b, 'C16' as code),
 (select 17 as rows,  'k3' as key_b, date('2015-08-19') as date_b, 'C17' as code),
 (select 18 as rows,  'k3' as key_b, date('2015-11-02') as date_b, 'C18' as code)
 ) table_b
 on table_a.key_a=table_b.key_b
 where timestamp(date_b) between DATE_ADD(timestamp(date_a), -1, "MONTH") and timestamp(date_a)
 group by 1

returns:

+-----+-------+--------------+-------------+------------+---+
| Row | key_a | first_date_a |  all_codes  | first_code |   |
+-----+-------+--------------+-------------+------------+---+
|   1 | k1    | 2015-11-12   | C1,C3,C4,C6 | C1         |   |
|   2 | k2    | 2015-11-20   | C9,C10,C12  | C9         |   |
|   3 | k3    | 2015-12-01   | C15,C18     | C15        |   |
+-----+-------+--------------+-------------+------------+---+
相关阅读:
Top