我有一个有大量行(>10K)的表。大多数行都有与ques_id关联的重复角色值。我是sql的新手。我要做的是通过不同的和最新的ques_id添加选择行。这是我的表(tbl_questions)结构。
id | ques_id | question | ans
1 | 2 | HTML stands.. | 3
2 | 5 | PHP stands.. | 2
3 | 6 | CSS stands.. | 4
4 | 6 | CSS stands.. | 4
5 | 5 | PHP stands.. | 2
6 | 6 | CSS stands.. | 4
这将是期望的结果:
id | ques_id | question | ans
1 | 2 | HTML stands.. | 3
5 | 5 | PHP stands.. | 2
6 | 6 | CSS stands.. | 4
以下是我到目前为止尝试过的查询:
SELECT DISTINCT ques_id, question, ans FROM tbl_questions
只是通过按组给出行号的另一个透视图。
查询select t1.id, t1.ques_id, t1.question, t1.ans from
(
select id, ques_id, question, ans,
(
case ques_id when @curA
then @curRow := @curRow + 1
else @curRow := 1 and @curA := ques_id end
) as rn
from tbl_questions t,
(select @curRow := 0, @curA := '') r
order by ques_id,id desc
)t1
where t1.rn = 1;
SQL小提琴您想要每个question
的最新行?您可以使用NOT EXISTS
返回这些行:
SELECT ques_id, question, ans
FROM tbl_questions t1
where not exists (select 1 from tbl_questions t2
where t2.ques_id = t1.ques_id
and t2.id > t1.id)
SELECT a.*
FROM tbl_questions a
JOIN
( SELECT ques_id
, MAX(id) max_id
FROM tbl_questions
GROUP
BY ques_id
) b
ON b.max_id = a.id;
试试这个查询
SELECT
SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY id DESC),',',1) AS i_d,
ques_id,
question,
SUBSTRING_INDEX(GROUP_CONCAT(ans ORDER BY id DESC),',',1) AS Answer
FROM tbl_questions
GROUP BY ques_id
输出i_d |ques_id | question | Answer
1 2 HTML stands.. 3
5 5 PHP stands.. 2
6 6 CSS stands.. 4