douci1918
2012-08-10 09:49
浏览 5
已采纳

将一列拆分为3并对每行进行计算

I have a normal table:

id |value
12 |1
24 |3
35 |20

..and so on to id lets say 100. I wonder if there's a query to select all, but split the column into 3 equally-separated columns and numerate the 1st from 1 to 33, 2nd 34-66, 67-100 (or anything close to that logic)

expected output: I don't care about the ids so what need is something like

order1|value1  order2|value2 order3|value3
    1 |1           34|80          67|206
    2 |4           35|100         68|207
    3 |6           36|102         69|280
    ...            ....            ...
    33|60          66|201        100|810

图片转代码服务由CSDN问答提供 功能建议

我有一个普通的表:

  id | value \  n12 | 1 
24 | 3 
35 | 20 
   
 
 

..依此类推id说100。我想知道是否有一个查询来选择所有,但是 将列拆分为3个相等分隔的列,并将第1个从1到33,第2个34-66,67-100(或接近该逻辑的任何值)进行计算

预期输出 : 我不关心id,所以需要的是

  order1 | value1 order2 | value2 order3 | value3 
 1 | 1 34 |  80 67 | 206 
 2 | 4 35 | 100 68 | 207 
 3 | 6 36 | 102 69 | 280 
 ...... .... ... 
 33 | 60 66 | 201 100 | 810 \  n   
 
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • doubingguan3425 2012-08-10 10:13
    已采纳

    I think this is what you are looking for:

    SELECT *
    FROM (
          SELECT (@var_count := @var_count + 1) AS order1, value AS value1, 0 order2, 0 value2, 0 order3, 0 value3
          FROM table_name, (SELECT @var_count := 0) a
          LIMIT 33
    
          UNION ALL
    
          SELECT 0 order1, 0 value1, (@var_count := @var_count + 1) AS order2, value AS value2, 0 order3, 0 value3
          FROM table_name, (SELECT @var_count := 33) a
          LIMIT 34, 33
    
          UNION ALL
    
          SELECT 0 order1, 0 value1, 0 order2, 0 value2, (@var_count := @var_count + 1) AS order3, value AS value3
          FROM table_name, (SELECT @var_count := 66) a
          LIMIT 67, 33
         )a;
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题