SQL qn:- 比较行中的数据
我想比较行中的数字数据。例如,我有一个表,其中有一列:-
Number
======
1.88
9.99
8.76
9.88
我想将第二个值、第三个值、第四个值与第一个值进行比较。然后第三个、第四个值到第二个。然后是第四到第三。
我怎样才能构造一个sql来做到这一点?
i would like to compare numeric data in rows. for eg, i have a table that has a column as such:-
Number
======
1.88
9.99
8.76
9.88
I want to compare 2nd value, 3rd value, 4th value to the 1st value. And then 3rd, 4th value to the 2nd. then 4th to 3rd.
How can i construct an sql to do this?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
您需要有某种方法为行分配序列号。
考虑下表:
创建为:
然后您可以执行:
这将为您提供:
You need to have some way of assigning sequence numbers to the rows.
Consider the following table:
created with:
You can then execute:
which will give you:
不确定这个的实用性,但也许这就是您正在寻找的......
比较值的一种更简单的方法是使用 ORDER BY 子句。
我冒昧地向表中添加了一个 id 列,我将其称为 tblTst。这可以方便地选择一些特定值(请参阅 (...) 子句中的 WHERE id),并且它还用于仅避免叉积中的重复。要比较整个表,只需省略 WHERE 子句即可。连接条件可以更改为严格小于 (<),以防止将值与自身进行比较(只是方便测试我的“=”)
表中有 100 个值,矩阵的底部(或顶部)三角形将包括 5,000比较。 (再次,我很困惑这如何有用或有趣......)
Not sure of the utility of this but maybe that is what you are looking for...
An easier way to compare values is to use the ORDER BY clause.
I took the liberty of adding an id column to the table which I call tblTst. This is convenient to select some specific values (see the WHERE id in (...) clause), and also it is used to only avoid duplicates in the cross product. To compare the whole table, simply omit the WHERE clause. The join condition can be changed to strictly less than (<) to prevent comparing values with themselves (was just convenient to test my '=')
With 100 values in the table the bottom (or top) triangle of the matrix will include 5,000 comparisons. (again, I'm puzzled as to how this can be useful or fun...)
为了我的理解,只是将 @paxdiablo 的工作简化为 t-sql 用语:
给出
Just simplifying @paxdiablo's work into t-sql parlance for my understanding:
gives