mysql - 使用 SQL 建表

Question

我如何转换表格:

 drop table if exists simon;
 Create table simon (userId int, passageId int, score int);
 INSERT INTO simon (userId, passageId, score )
 VALUES
 (10, 1, 2),
 (10, 1, 3),
 (10, 2, 1),
 (10, 2, 3),
 (10, 2, 5),
 (11, 1, 1),
 (11, 2, 2),
 (11, 2, 3),
 (11, 3, 4);

收件人:

 userId passageId   score1 score2
 10    1            2      3
 10    2            1      3
 11    1            1      null
 11    2            2      3
 11    3            4      null

请注意，只保留前两个分数，因此 (10, 2, 5) 被忽略。我正在使用 sql 在 mySQL 数据库中工作。

http://rextester.com/UYDRBF97169

Answer 1

首先，您应该定义一个顺序来获取值。例如，每个 userId 和 passageId 的 2 个最小分数或 2 个最大分数。

以下查询将为每对 (userId,passageId) 获取 2 个最最小 分数，并将其存储在表 simon2 中作为 score1 和 score2 分别。

Create table simon2 (
    userId int, passageId int,
    score1 int,score2 int);    

insert into simon2(userId,passageId,score1,score2)
select
t1.userId,t1.passageId,t1.s1,
(select min(distinct score) 
 from simon s 
 where s.userId = t1.userId
 and   s.passageId = t1.passageId
 and   s.score > t1.s1
) as s2
 from 
(select userId,passageId,min(score) as S1
 from simon 
 group by userId,passageId) t1;

Click for the Demo

编辑:

这是我针对同一任务的另一种方法，目的是提供可用于更大数据集的高效解决方案。

老实说，我不知道下面的方法是否比以前的方法更好，因为它包含像 Group_concat() 和 Substring_Index() 这样的函数，它们不是以性能友好着称。

但我的以下解决方案的优点在于它不需要对数据进行排序(或扫描组内的整列)，这在我之前的解决方案中发生了两次。

尽管如此，这是我的解决方案:

insert into simon2(userId,passageId,score1,score2)
select t.userId, t.passageId,
substring_index(t.scores,',',1) as score1,
(case when length(t.scores) > 1 then substring_index(t.scores,',',-1)
      else null
 end
) as score2
from 
(select userId,passageId,
substring_index (group_concat(score separator ','),',',2) as scores
from simon
group by userId,passageId) t;

Click here for Demo 2

注意:解决方案 2 不是基于 score 的任何递增或递减顺序。它将遵循与您在以下查询的结果集中获得的序列相同的序列:

select *from simon;

我认为这就是您在问题中实际要求的

希望对您有所帮助!