>我有以下简单的表格
Item (id, name, date, fixed_position)
(1, 'first entry', '2016-03-09 09:00:00', NULL)
(2, 'second entry', '2016-03-09 04:00:00', 1)
(3, 'third entry', '2016-03-09 05:00:00', NULL)
(4, 'fourth entry', '2016-03-09 19:00:00', NULL)
(5, 'fifth entry', '2016-03-09 13:00:00', 4)
(6, 'sixth entry', '2016-03-09 21:00:00', 2)
项目的数量不是固定的,实际上可以从~100到~1000不等。
我想要实现的是执行查询以返回按字段排序date
项目集,该字段考虑了fixed_position
字段,该字段代表将"固定"结果之类的内容"固定"到特定位置。如果给定条目的fixed_position
不是 NULL,则结果应固定到第 n 个位置,如果 fixed_position
为 NULL,则ORDER BY
应优先。
所需的查询输出以获得更明亮的解释:
(2, 'second entry', '2016-03-09 04:00:00', 1) // pinned to 1-st position
(6, 'sixth entry', '2016-03-09 21:00:00', 2) // pinned to 2-nd position
(3, 'third entry', '2016-03-09 05:00:00', NULL) // ORDER BY `date`
(5, 'fifth entry', '2016-03-09 13:00:00', 4) // pinned to 4-th position
(1, 'first entry', '2016-03-09 09:00:00', NULL) // ORDER BY `date`
(4, 'fourth entry', '2016-03-09 19:00:00', NULL) // ORDER BY `date`
当某些项目具有固定位置时,我已经尝试了在排序MySql结果中发布的解决方案,但即使使用复制粘贴方法,这似乎也根本不起作用。
到目前为止,我尝试的是这个查询:
SELECT
@i := @i +1 AS iterator,
t.*,
COALESCE(t.fixed_position, @i) AS positionCalculated
FROM
Item AS t,
(
SELECT
@i := 0
) AS foo
GROUP BY
`id`
ORDER BY
positionCalculated,
`date` DESC
其中返回:
iterator | id | name | date | fixed_position | positionCalculated
1 1 first entry 2016-03-09 09:00:00 NULL 1
2 2 second entry 2016-03-09 04:00:00 1 1
6 6 sixth entry 2016-03-09 21:00:00 2 2
3 3 third entry 2016-03-09 05:00:00 NULL 3
4 4 fourth entry 2016-03-09 19:00:00 NULL 4
5 5 fifth entry 2016-03-09 13:00:00 4 4
MySQL是否可以执行这样的任务,或者我应该采用后端方法并在两个结果集上执行PHP的array_merge()
?
解决此问题的蛮力方法是首先创建一个行数大于原始表的计数表:
SELECT @rn := @rn + 1 AS rn
FROM (
SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS t1
CROSS JOIN (
SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS t2
CROSS JOIN (SELECT @rn := 0) AS v
然后,您可以将此表左键连接到包含原始表的所有固定位置的派生表:
SELECT Tally.rn
FROM (
... tally table query here
) AS Tally
LEFT JOIN (
SELECT fixed_position
FROM Item
) AS t ON Tally.rn = t.fixed_position
WHERE t.t.fixed_position IS NULL
上面返回待填充的缺失订单仓位。
在这里演示
现在,您可以将上述查询用作连接到原始表的另一个派生表,以实现所需的排序:
SELECT id, name, `date`, fixed_position, Gaps.rn,
derived.seq, Gaps.seq
FROM (
SELECT id, name, `date`, fixed_position,
@seq1 := IF(fixed_position IS NULL, @seq1 + 1, @seq1) AS seq
FROM Item
CROSS JOIN (SELECT @seq1 := 0) AS v
ORDER BY `date`
) AS derived
LEFT JOIN (
SELECT Tally.rn,
@seq2 := @seq2 + 1 AS seq
FROM (
SELECT @rn := @rn + 1 AS rn
FROM (
SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS t1
CROSS JOIN (
SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS t2
CROSS JOIN (SELECT @rn := 0) AS v
) AS Tally
LEFT JOIN (
SELECT fixed_position
FROM Item
) AS t ON Tally.rn = t.fixed_position
CROSS JOIN (SELECT @seq2 := 0) AS v
WHERE t.t.fixed_position IS NULL
ORDER BY rn
) AS Gaps ON (derived.seq = Gaps.seq) AND (derived.fixed_position IS NULL)
ORDER BY COALESCE(derived.fixed_position, Gaps.rn)
在这里演示
我遇到了同样的问题(按日期排序+注入具有固定位置值的行)。上述解决方案似乎有效。但是您必须知道您的表有多少值。该行:
SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
如果有更多行,则必须扩展,因为临时表要缩短。在我的数据库中进行了一些谷歌搜索结果和测试后,我想出了一个适合您需求的解决方案,并且我认为更聪明地理解。
SELECT *
FROM
(SELECT
create_date,
fixedposition,
@rownum:=@rownum + 1 AS colposition,
1 AS majorEntry
FROM myTable
JOIN (SELECT @rownum:=0) r
WHERE fixedposition IS NULL
ORDER BY crdate DESC) AS orderedFixed
UNION ALL
(SELECT create_date,
fixedposition,
fixedposition AS colposition,
0 AS majorEntry
FROM myTable
WHERE fixedposition IS NOT NULL
ORDER BY fixedposition ASC)
ORDER BY colposition ASC, majorEntry
所以这就是它的工作原理:有两个 SELECT 语句。第一个 SELECT 搜索所有没有固定位置的列,并按日期对它们进行排序。此外,它还创建一个 JOIN 来添加行计数器列。第二个搜索具有固定位置的所有行,并按"并列"返回排序。
两个选择语句由 UNION ALL 组合。联合首先按 ASCend 并列排序,在第二步按 majonEntry-值排序,该值指示具有固定位置的行将放在具有相同位置的其他行之前。
如果有人偶然发现这一点,我在 dk1 的答案的帮助下提出了一个很好的解决方案。虽然他的回答原则上是好的,但它有一个缺陷,即固定行的每次迭代都会增加一个位置。例如:固定到第
1 个位置的帖子位于第 1 位 (+0),固定到第 2 个位置的帖子位于第 3 位 (+1),固定到第 4 个位置的帖子位于第 6 位(+2)。我通过添加第二个变量来减少固定帖子的最终位置来解决此问题。
代码说明:
-
minor
= 具有未固定帖子的派生表 -
major
= 具有固定帖子的派生表 -
@rownum
= 次要表中帖子的迭代器 -
@decrease
= 主表中帖子的迭代器
SELECT
final.*
FROM
(
(
# see note #1 about this wrapper query
SELECT * FROM
(
SELECT
minor.*,
(@rownum := @rownum + 1) AS 'rowPosition',
0 AS 'rowDecrease',
0 AS 'majorEntry'
FROM table_with_your_posts minor
JOIN (@rownum := 0) x
WHERE minor.your_fixed_position IS NULL
ORDER BY minor.your_date DESC
) y
ORDER BY y.your_date DESC
)
UNION ALL
(
SELECT
major.*,
major.your_fixed_position AS 'rowPosition',
(@decrease := @decrease + 1) AS 'rowDecrease',
1 AS 'majorEntry'
FROM table_with_your_posts major
JOIN (@decrease := -1) x
WHERE major.your_fixed_position IS NOT NULL
ORDER BY major.your_fixed_position ASC
)
) final
# see note #2 about this ORDER BY clause
ORDER BY final.rowPosition - final.rowDecrease ASC, final.majorEntry DESC
注意#1:现在我不是专家,但是如果您不将次要表的查询包装在另一个查询中,并且不按与内部查询相同的方式对其进行排序,则次要表中的排序将不起作用。也许有人可以对此有更多的了解。
注意#2:ORDER BY final.rowPosition - final.rowDecrease - final.majorEntry / 2 ASC
也可以工作,看起来更酷
请注意,如果要进一步筛选结果(此处的好示例是仅显示已发布的帖子),则很可能需要将筛选条件添加到两个派生表的 WHERE 子句中。否则,最终位置将不正确。
最后,我想补充一点,这不是万无一失的。例如,如果有两个位置相同的固定帖子,它将把结果移动一个。这可以通过使具有固定位置的列成为唯一来解决。我想到的另一件事是,如果表格的结果少于固定帖子的位置值,则该帖子显然不会在其所需位置(例如,如果表格包含 5 个帖子,而您将帖子固定到第 8 个位置,则它最终可能会成为第 5 个)。请记住,我没有对此进行有意义的测试,请确保您的查询对您的需求有效。