mysql - 按 nid 分组和按日期排序并使用组 ID Drupal

Question

我正在使用drupal，并且我有nodeaccess模块​​。我对单个用户有很多角色。我已经为单个节点授予了许多角色。

我需要如下输出。

a) 订购日期(第一优先)
b)按照日期顺序我需要按gid分组

示例

我写了一个查询

SELECT n.nid, na.gid, UNIX_TIMESTAMP(fav.field_date_posted_value), fav.field_date_posted_value
FROM node as n
JOIN nodeaccess AS na ON na.nid = n.nid 
LEFT JOIN field_data_field_date_posted AS fav ON fav.entity_id = n.nid
ORDER BY fav.field_date_posted_value DESC, n.nid ASC
LIMIT 10

我得到的结果如下

+-------+-----+-------------------------+
| nid   | gid | field_date_posted_value |
+-------+-----+-------------------------+
| 12501 |   1 | 2014-12-07              |
| 12501 |  10 | 2014-12-07              |
| 12502 |   1 | 2014-12-07              |
| 12502 |   3 | 2014-12-07              |
| 12502 |   8 | 2014-12-07              |
| 12502 |  10 | 2014-12-07              |
| 12502 |  11 | 2014-12-07              |
| 12505 |   1 | 2014-05-05              |
| 12505 |  10 | 2014-05-05              |
| 12575 |   1 | 2014-12-24              |
| 12575 |  10 | 2014-12-24              |
| 12576 |   1 | 2013-05-26              |
| 12576 |  10 | 2013-05-26              |
| 12577 |   1 | 2013-05-14              |
| 12577 |  10 | 2013-05-14              |
+-------+-----+-------------------------+

但是当我尝试像下面这样按 nid 分组时

SELECT n.nid, na.gid, UNIX_TIMESTAMP(fav.field_date_posted_value), fav.field_date_posted_value
FROM node as n
JOIN nodeaccess AS na ON na.nid = n.nid 
LEFT JOIN field_data_field_date_posted AS fav ON fav.entity_id = n.nid
GROUP BY n.nid ORDER BY fav.field_date_posted_value DESC, n.nid ASC
LIMIT 10

结果呢

+-------+-----+-------------------------+
| nid   | gid | field_date_posted_value |
+-------+-----+-------------------------+
| 12501 |  01 | 2014-12-24              |
| 12502 |  11 | 2014-12-07              |
| 12505 |  01 | 2014-12-07              |
| 12575 |  01 | 2014-05-26              |
| 12576 |  01 | 2013-05-14              |
| 12577 |  01 | 2013-05-05              |
+-------+-----+-------------------------+

但是我的预期结果是

+-------+-----+-------------------------+
| nid   | gid | field_date_posted_value |
+-------+-----+-------------------------+
| 12501 |  10 | 2014-12-24              |
| 12502 |  11 | 2014-12-07              |
| 12505 |  10 | 2014-12-07              |
| 12575 |  10 | 2014-05-26              |
| 12576 |  10 | 2013-05-14              |
| 12577 |  10 | 2013-05-05              |
+-------+-----+-------------------------+

有什么办法可以做到这一点吗？

我想将结果重新排序为按 nid 分组、按日期排序(DESC)和按 gid 排序 (DESC)

Answer 1

您可以尝试在 gid 和 field_date_posted_value 列上使用聚合函数，这是您应该在正确的 GROUP BY 中执行的操作查询:

SELECT n.nid,
       MAX(na.gid),
       MAX(fav.field_date_posted_value)
FROM node AS n
JOIN nodeaccess AS na
    ON na.nid = n.nid 
LEFT JOIN field_data_field_date_posted AS fav
    ON fav.entity_id = n.nid
GROUP BY n.nid
ORDER BY fav.field_date_posted_value DESC,
         n.nid ASC
LIMIT 10

实际上，由于 nid 列似乎与 field_date_posted_value 列完全相关，因此您可以对这两列进行分组，即使用以下内容:

SELECT n.nid, MAX(na.gid), fav.field_date_posted_value
FROM ...
...
GROUP BY n.nid, fav.field_date_posted_value