我有两个表(问题和答案(,一个行号用于问题,另一个用于答案。问题的行号随着问题的变化而增加。答案的行号将递增每个答案,并为每个新问题重置为 1。两个行编号都按预期递增,但排序顺序错误。另外,我不希望查询输出检查当前记录的问题是否与最后一个记录问题匹配的变量。
我相信问题是记录排序是最后执行的,而最后发生的应该是变量的赋值。我试图在 MySQL 获取行位置上调整解决方案,ORDER BY 编号不正确,但我不断收到错误。 我不知道如何"关闭"正在显示的@currQ。
表问题有(question_id,问题,display_order( 表 answrs 有 (answer_id, question_id_fk, 答案, display_order(
SET @q_row=0,@a_row=0,@currQ='';
SELECT
@q_row:=CASE WHEN q.question=@currQ THEN @q_row ELSE @q_row+1 END AS
'Question No.',
q.question,
@a_row:=CASE WHEN q.question=@currQ THEN @a_row+1 ELSE 1 END AS
'Answer
No.',
a.answer, @currQ:=q.question
FROM
questions q
INNER JOIN
answers a ON q.question_id=a.question_id_fk
ORDER BY
q.question,a.answer ASC;
动态编号有效,但顺序错误。.
Question No. question Answer No. answer @currQ:=q.question
4 Favourite excercise 3 Archery Favourite excercise
4 Favourite excercise 1 Running Favourite excercise
4 Favourite excercise 2 Sailing Favourite excercise
2 Favourite food 3 French Favourite food
2 Favourite food 2 South... Favourite food
2 Favourite food 1 Indian Favourite food
2 Favourite food 4 Vietnam..Favourite food
1 Favourite band 2 Deep P.. Favourite band
1 Favourite band 1 Jimi H.. Favourite band
1 Favourite band 3 Eagles Favourite band
1 Favourite pet 1 Dog Favourite pet
这就是我希望它的外观(使用不同的数据(
QRow Question Q.ID Q.disp_ord ARow Answer A.ID A.disp_ord
1 Favourite Pet 19 6 1 Dog 17 4
2 Favourite Band 8 9 1 The Who 3 1
2 Favourite Band 8 9 2 Dire Straits 69 3
2 Favourite Band 8 9 3 The Doors 103 15
3 Best Food 26 15 1 Thai 76 1
3 Best Food 26 15 2 Japanese 233 2
从Q.disp_ord订购,A.disp_ord。结果表明,这两个disp_ord都必须升序,但请注意它们可能不是连续的(因为序列中的其他问题和答案可能会被过滤掉(。
我将最终结果(从SQLFiddle复制(留在这里以供参考:
我们基本上有三个步骤。
- 以正确的顺序获取所有数据 (
FullData
(
按 - 正确的顺序添加
QRow
和ARow
(OrderedData
( - 过滤掉我们真正想看的列(最外
SELECT
语句(
SET @q_row=0,@a_row=0,@currQ='';
SELECT QRow, Question, ARow, Answer
FROM
(
SELECT
@q_row:=CASE WHEN Question=@currQ THEN @q_row ELSE @q_row+1 END AS QRow,
Question, Q_ID, Q_DO,
@a_row:=CASE WHEN Question=@currQ THEN @a_row+1 ELSE 1 END AS ARow,
Answer, A_ID, A_DO,
@currQ:=Question
FROM
(
SELECT
q.question AS Question,
q.question_id AS Q_ID,
q.display_order AS Q_DO,
a.answer AS Answer,
a.answer_id AS A_ID,
a.display_order AS A_DO
FROM questions q
INNER JOIN answers a ON q.question_id=a.question_id_fk
ORDER BY q.display_order, a.display_order
) FullData
) OrderedData
我们得到了以下架构和数据
CREATE TABLE IF NOT EXISTS `questions` (
`question_id` int(6) unsigned NOT NULL,
`question` varchar(20) NOT NULL,
`display_order` int(6) unsigned NOT NULL,
PRIMARY KEY (`question_id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `questions` (`question_id`, `question`,`display_order`) VALUES
(1, 'Favourite bands', 3),
(3, 'Favourite pet type', 1),
(4, 'Favourite sport', 2),
(2, 'Favourite foods', 4);
CREATE TABLE IF NOT EXISTS `answers` (
`answer_id` int(6) unsigned NOT NULL,
`question_id_fk` int(6) unsigned NOT NULL,
`answer` varchar(20) NOT NULL,
`display_order` int(6) unsigned NOT NULL,
PRIMARY KEY (`answer_id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `answers` (`answer_id`, `question_id_fk`, `answer`,`display_order`) VALUES
(15, 1, 'Bruce Springsteen',2),
(23, 1, 'Jimi Hendrix',3),
(32, 1, 'The Beatles',1),
(25, 2, 'Street Vietnamese',2),
(14, 2, 'Spicy Indian',1),
(62, 3, 'Dog',2),
(17, 3, 'Cat',1),
(83, 4, 'NBA',3),
(119, 4, 'Australian Rules',2),
(11, 4, 'Golf',4),
(12, 4, 'Sailing',1)