从两个表中选择交替记录 [英] Selecting alternating records from two tables

问题描述

我的数据库中有两个表:events_users 和 events_admins.两者几乎相同(除了少数字段).两个表也有一个 promoted 字段(1 或 0).

I have two tables in my database: events_users and events_admins. The two are nearly identical (except for a few fields). Both tables also have a promoted field (1 or 0).

+-----------------+-----------------------+-------------------+-------------------+
| events_users_id | events_users_promoted | events_users_name | events_users_date |
+-----------------+-----------------------+-------------------+-------------------+
|               1 |                     0 | Users foo         | 2012-11-15        |
|               2 |                     1 | Users bar         | 2012-11-15        |
|               3 |                     0 | Users foobar      | 2012-11-14        |
+-----------------+-----------------------+-------------------+-------------------+

+------------------+------------------------+--------------------+--------------------+
| events_admins_id | events_admins_promoted | events_admins_name | events_admins_date |
+------------------+------------------------+--------------------+--------------------+
|                1 |                      0 | admins foo         | 2012-11-14         |
|                2 |                      0 | admins bar         | 2012-11-15         |
|                3 |                      1 | admins foobar      | 2012-11-16         |
+------------------+------------------------+--------------------+--------------------+

由于各种原因，我不能将两种类型的事件放在一张表中.但是，我确实想要一个结果，如下所示:

I cannot put both types of events in one table for various reasons. However, I do want one result, as follows:

所有事件都按日期排序，最近的事件在前，但是管理事件在前，并且总是与用户事件交替出现(交替每条记录).首先显示升级的用户和管理员事件.

All events are ordered by date, most recent first, however an admin event comes first and always alternates with a user event (alternating every record). Promoted user AND admin events are shown first.

+------------------+-----------------------+-------------------+-------------------+
|              a 3 |                     1 | admins foobar     | 2012-11-16        |
|              u 2 |                     1 | Users bar         | 2012-11-15        |
|              a 1 |                     0 | admins foo        | 2012-11-14        |
|              u 3 |                     0 | Users foobar      | 2012-11-14        |
|              a 2 |                     0 | admins bar        | 2012-11-15        |
|              u 1 |                     0 | Users foo         | 2012-11-15        |
+------------------+-----------------------+-------------------+-------------------+

我想知道我是否可以通过一个查询来做到这一点，使用 UNION 来合并两个表(一个表中缺少的几个字段，我将标记为空)，但是，我不知道如何对它们进行排序.我目前看到的唯一出路是使用两个不同的查询和一个简单的 ORDER BY 提升 DESC，日期 ASC，将结果放入两个数组中，然后将它们与 PHP 交替合并.

I was wondering if I could do this with one query, using UNION to merge the two tables (the few missing fields in the one table, I'll mark as null), however, I wouldn't know how to sort them. The only way out I see at the moment is using two different queries with a simple ORDER BY promoted DESC, date ASC, put the results in two arrays and merge them alternating with PHP.

编辑:似乎我没有很好地解释我的目标，因为目前的两个答案并不能完全解决我的问题.

EDIT: It seems I haven't explained my goal well enough, as the two current answers don't exactly solve my problem.

建议的查询首先给我一个类别的所有提升事件，然后是另一种类型的所有提升事件，一种类型的正常事件，最后是另一种类型的剩余事件.但是，这些也需要交替:一名用户升级，一名管理员升级，一名用户升级，一名管理员升级......当我用完管理员升级事件时，我想要一个管理员事件，一个用户事件，一个管理员事件...

The suggested queries first give me all promoted events from one category, then all promoted events of the other type, the normal events of the one type and at last the remaining events of the other type. However, these need to alternate as well: one user promoted, one admin promoted, one user promoted, one admin promoted.... When I've run out of admin promoted events, I want one admin event, one user event, one admin event...

推荐答案

SELECT * FROM (
  SELECT
    'u' AS TYPE,
    id ,
    events_users_id AS events_users_id,
    events_users_promoted AS promoted,
    events_users_name AS name,
    events_users_date AS date
FROM eventusers
UNION ALL
SELECT
    'a' AS TYPE,
    id,
    events_admin_id AS events_admins_id,
    events_admin_promoted AS promoted,
    events_admin_name AS name,
    events_admin_date AS date
FROM eventadmins
) AS tmp
ORDER BY ID, TYPE DESC

看看这个http://www.sqlfiddle.com/#!2/5b9a5/10

两个表中又添加了一列.

One more column added to both the tables.

CREATE TABLE `eventusers` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`events_users_id` INT(10),
`events_users_promoted` INT(10),
`events_users_name` varchar(50),
`events_users_date` date,
PRIMARY KEY (`id`)

);
CREATE TABLE `eventadmins` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`events_admin_id` INT(10),
`events_admin_promoted` INT(10),
`events_admin_name` varchar(50),
`events_admin_date` date,
PRIMARY KEY (`id`)

);

这篇关于从两个表中选择交替记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！