如何为此编写SQL查询？ [英] How to write sql query for this ?

问题描述

我有这么多长数据库，所以我在逗号中使用seq_no，在单列中使用多个一个序列存储，但现在我想要在一个列中的所有序列，所以我很困惑如何为此创建此sql结果。



前例。



<前lang =text> SR_NO SEQ_NO
1 1839073，
2 1850097,1850098，
3 1850099,1850100,1850110

我得到了这样的结果

 SEQ_NO 
 1839073 
 1850097 
 1850098 
 1850099 
 1850100 
 1850110 

谢谢。！



SHOUTING删除，代码块固定 - OriginalGriff [ / edit]

解决方案

最好的建议是不要。

始终将值存储为最合适的类型：这意味着数字存储在数字列中，日期存储在DATETIME列中，等等。



当涉及数字序列时，将它们存储为逗号分隔值很好，对于初始插入操作很简单，但很快就会变成一个主要的PITA来做任何有用的事情。正如你开始看到的那样！现在想象一下，你需要稍后将1850100更改为1850275 - 并认为这是一项困难，笨拙和一般尴尬的工作。



相反，改变您的数据库使用第二个表，该表将序列号与SR_NO相关联，并使用SQL来检索它们。最初设置起来有点困难，但是以后会节省很多时间。



但是如果你认为必须保持一个非常糟糕的数据库设计那么这：将列中的逗号分隔数据转换为行选择 [ ^ ]将向您展示如何执行此任务 - 您可能会改变主意！ ：笑：

I have so many long database so i used seq_no in commas separate using more then one sequence store in single column but now i want all sequence in a single column so i confused how to create this sql result for this.

For ex.

SR_NO   SEQ_NO
  1     1839073,
  2     1850097,1850098,
  3     1850099,1850100,1850110    

I got like this resule

SEQ_NO 
1839073
1850097
1850098
1850099
1850100
1850110

Thanks.!

[edit]SHOUTING removed, Code block fixed- OriginalGriff[/edit]

解决方案

The best suggestion is "don't".
Always store values as the most appropriate type: which means that numbers get stored in numeric columns, dates in DATETIME columns, and so on.

And when it comes to sequences of numbers, storing them as comma separated values is nice and simple to do for the initial insert, but quickly becomes a major PITA to do anything useful with. As you are starting to see! Now imagine that you need to change "1850100" to "1850275" later on - and think what a difficult, clumsy and generally awkward job it is.

Instead, change your DB to use a second table which correlates a sequence number to a SR_NO and use SQL to retrieve them. It's a little more difficult to set up initially, but it will save you a serious amount of time later.

But if you think must keep a very bad database design then this: Converting comma separated data in a column to rows for selection[^] will show you how to do this task - and you might change your mind! :laugh:

这篇关于如何为此编写SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！