mysql从同一个表上的2个查询获得不同顺序的混合结果

Question

thanks for dedicating time.

I have two tables for a kind of social network, table A for contents and table B for likes:

TABLE A:

CREATE TABLE `A` (
`id` int(10) unsigned NOT NULL,
  `id_user` int(10) unsigned NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `path` text,
  `ext` varchar(15) DEFAULT NULL,
  `image_type` varchar(100) DEFAULT NULL,
  `link_url` text,
  `creation_date` datetime DEFAULT NULL,
  `size` float DEFAULT NULL,
  `type` int(10) unsigned DEFAULT NULL,
  `number_share` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `A`
 ADD PRIMARY KEY (`id`), ADD KEY `id_user` (`id_user`);

And TABLE B:

CREATE TABLE B (
  `id_b` int(10) unsigned NOT NULL,
  `id_user` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `B`
 ADD PRIMARY KEY (`id_b`,`id_user`), ADD KEY `id_user` (`id_user`);

I'd like to retrieve from mysql records with this order:

    1st most popular 
     1st most recent
     2nd most popular 
     2nd most recent



    .....

Can anyone tell me how to combine this by mysql (if possibile) or something easy to use in php wich is not 2 different queries?

EDIT 1:

If i have this situation:

results from first select (popular): id 3 5 12 4 65 2

and

results from second select (recent):

id 
100 
121 
3 

i will go to print my elements width id:

id
3 
100 
5 
121 
12 

i want that id=3 from the second query is not considered because already posted

Thanks!

Answer 1

plan

• use variable, and order by to rank popularity
• use variable, and order by to rank recent
• union all
• re order by rnk, cat
• group by id to restrict to first id

input [sample]

+----+---------+--------+--------+--------+------------+----------+--------------------------+--------+--------+--------------+
| id | id_user | title  |  path  |  ext   | image_type | link_url |      creation_date       |  size  |  type  | number_share |
+----+---------+--------+--------+--------+------------+----------+--------------------------+--------+--------+--------------+
|  1 |      22 | (null) | (null) | (null) | (null)     | (null)   | August, 09 2015 10:54:57 | (null) | (null) |            0 |
|  2 |      33 | (null) | (null) | (null) | (null)     | (null)   | August, 09 2015 07:54:57 | (null) | (null) |            0 |
|  3 |      44 | (null) | (null) | (null) | (null)     | (null)   | August, 09 2015 08:54:57 | (null) | (null) |            0 |
+----+---------+--------+--------+--------+------------+----------+--------------------------+--------+--------+--------------+

+------+---------+
| id_b | id_user |
+------+---------+
|    3 |       8 |
|    3 |       9 |
|    3 |      21 |
|    3 |      22 |
|    1 |      55 |
|    3 |      55 |
|    1 |      66 |
|    1 |      77 |
+------+---------+

query

set @rnk_pop := 0;
set @rnk_rec := 0;

-- popularity ranking
select cat, id, rnk
from
(
select cat, id, rnk
from
(
  select cat, id, occurs, @rnk_pop := @rnk_pop + 1 as rnk
  from
  (
    select 'popularity' as cat, A.id, count(B.id_b) as occurs
    from A
    left join B
    on B.id_b = A.id
    group by A.id
    order by occurs desc
  ) q1
) pop_sort
union all
-- recent ranking
select cat, id, rnk
from
(
  select 'recent' as cat, id, @rnk_rec := @rnk_rec + 1 as rnk
  from A
  order by creation_date desc
) rec_sort
order by rnk, cat
) all_q
group by id
order by rnk, cat
;

output

+------------+----+-----+
|    cat     | id | rnk |
+------------+----+-----+
| popularity |  3 |   1 |
| recent     |  1 |   1 |
| popularity |  2 |   3 |
+------------+----+-----+

sqlfiddle

notes

ranking output above would be used to join to display whatever information is useful about the content..