我们如何通过表中的字段控制动态排序? [英] How do we control dynamic ordering by a Field in a table?
问题描述
我不知道问题是否与我想要的正确，但是:
I don't know if the question is correct with what I want, but:
我在表中有一组问题，将按照特定顺序向客户提出问题，有时我们需要插入新问题，还需要上下移动问题.
I have a set of questions in a table that will be asked to clients in an specific order, and sometimes we need to insert new questions and also we need to move questions down or up.
我创建了一个名为position的字段，并使用了一些按钮来增加和减少它的位置，因此我可以使用SELECT ... ORDER BY，但是它不是很好，因为有时两个或多个问题会得到相同的位置编号，而MySQL选择了它们的顺序
I made a field called position, and some buttons to increase and decrease it's position so I can use a SELECT ... ORDER BY but it is not very good because sometimes two or more questions get the same position number and the MySQL selects their order.
那么使它完美工作的正确方法是什么?
So what is the proper way to get this working perfectly?
注意:我不能使用索引来做到这一点.对某些人来说是显而易见的，但对其他人而言却不是...
推荐答案
如果我理解的正确，那么当您插入新问题时，您需要一种在position列中正确管理值序列的方法，请更改现有问题的位置或删除问题.
If I understand you right you need a way to properly manage sequence of values in position column when you insert new questions, change the position of an existing one or delete questions.
假设您的问题表具有以下DDL:
Let's say you have following DDL of your questions table:
CREATE TABLE `questions` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`question` VARCHAR(256) DEFAULT NULL,
`position` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
);
和类似这样的初始数据集
and intitial data set like this
+----+------------+----------+
| id | question | position |
+----+------------+----------+
| 1 | Question 1 | 1 |
| 2 | Question 2 | 2 |
| 3 | Question 3 | 3 |
+----+------------+----------+
要获得问题列表的顺序，您确实很明显
SELECT *
FROM questions
ORDER BY position;
要将新问题插入问题列表的末尾，您可以这样做
INSERT INTO questions (question, position)
SELECT 'New Question', COALESCE(MAX(position), 0) + 1
FROM questions;
结果将是:
+----+--------------+----------+
| id | question | position |
+----+--------------+----------+
| 1 | Question 1 | 1 |
| 2 | Question 2 | 2 |
| 3 | Question 3 | 3 |
| 4 | New Question | 4 |
+----+--------------+----------+
要将新问题插入列表中的特定位置(假设第3位)，您可以通过两个查询来完成此操作:
To insert a new question to a specific position (let's say to position 3) in list you do it with two queries:
UPDATE questions
SET position = position + 1
WHERE position >= 3;
INSERT INTO questions (question, position)
VALUES ('Another Question', 3);
现在有
+----+------------------+----------+
| id | question | position |
+----+------------------+----------+
| 1 | Question 1 | 1 |
| 2 | Question 2 | 2 |
| 5 | Another Question | 3 |
| 3 | Question 3 | 4 |
| 4 | New Question | 5 |
+----+------------------+----------+
要交换两个问题的职位(例如ID为2和5的问题)，您可以
To swap positions of two questions (e.g. questions with ids 2 and 5) you do
UPDATE questions AS q1 INNER JOIN
questions AS q2 ON q1.id = 2 AND q2.id = 5
SET q1.position = q2.position,
q2.position = q1.position
让我们看看我们拥有什么
Let's see what we've got
+----+------------------+----------+
| id | question | position |
+----+------------------+----------+
| 1 | Question 1 | 1 |
| 5 | Another Question | 2 |
| 2 | Question 2 | 3 |
| 3 | Question 3 | 4 |
| 4 | New Question | 5 |
+----+------------------+----------+
这就是您在用户单击向上和向下按钮并提供正确的问题ID时所做的事情.
That's exactly what you do when user clicks on your up and down buttons, supplying correct question ids.
现在，如果您想在删除问题时保持职位顺序无间隔，就可以这样做.
Now if you want to keep your positions sequence without gaps when you delete question you can do that.
要从列表末尾删除，请使用简单删除
DELETE FROM questions WHERE id=4;
结果
+----+------------------+----------+
| id | question | position |
+----+------------------+----------+
| 1 | Question 1 | 1 |
| 5 | Another Question | 2 |
| 2 | Question 2 | 3 |
| 3 | Question 3 | 4 |
+----+------------------+----------+
在列表的中间(或开头)删除问题需要做更多的工作.假设我们要删除ID = 5的问题
Deleting a question in the middle (or beginning) of the list requires more work. Let's say we want to delete the question with id=5
-- Get the current position of question with id=5
SELECT position FROM questions WHERE id=5;
-- Position is 2
-- Now delete the question
DELETE FROM questions WHERE id=5;
-- And update position values
UPDATE questions
SET position = position - 1
WHERE position > 2;
最后我们有
+----+--------------+----------+
| id | question | position |
+----+--------------+----------+
| 1 | Question 1 | 1 |
| 2 | Question 2 | 2 |
| 3 | Question 3 | 3 |
+----+--------------+----------+
更新:为了使我们的生活更轻松，我们可以将它们全部包装在存储过程中
UPDATE: To make our life easier we can wrap it all in stored procedures
DELIMITER $$
CREATE PROCEDURE add_question (q VARCHAR(256), p INT)
BEGIN
IF p IS NULL OR p = 0 THEN
INSERT INTO questions (question, position)
SELECT q, COALESCE(MAX(position), 0) + 1
FROM questions;
ELSE
UPDATE questions
SET position = position + 1
WHERE position >= p;
INSERT INTO questions (question, position)
VALUES (q, p);
END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE swap_questions (q1 INT, q2 INT)
BEGIN
UPDATE questions AS qs1 INNER JOIN
questions AS qs2 ON qs1.id = q1 AND qs2.id = q2
SET qs1.position = qs2.position,
qs2.position = qs1.position;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE delete_question (q INT)
BEGIN
SELECT position INTO @cur_pos FROM questions WHERE id=q;
SELECT MAX(position) INTO @max FROM questions;
DELETE FROM questions WHERE id=q;
IF @cur_pos <> @max THEN
UPDATE questions
SET position = position - 1
WHERE position > @cur_pos;
END IF;
END$$
DELIMITER ;
并像这样使用它们:
-- Add a question to the end of the list
CALL add_question('How are you today?', 0);
CALL add_question('How are you today?', NULL);
-- Add a question at a specific position
CALL add_question('How do you do today?', 3);
-- Swap questions' positions
CALL swap_questions(1, 7);
-- Delete a question
CALL delete_question(2);
这篇关于我们如何通过表中的字段控制动态排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
