Question

我先拿到一些“我一夫”会分几行的数据,我想从前几行中分出一行,再说一遍。

因此,下表是:

CREATE TABLE foo (
  id,
  length
)

INSERT INTO foo (id,length) VALUES(1,1090)
INSERT INTO foo (id,length) VALUES(2,888)
INSERT INTO foo (id,length) VALUES(3,545)
INSERT INTO foo (id,length) VALUES(4,434)
INSERT INTO foo (id,length) VALUES(5,45)

我想,结果显示第三栏“差异”,即从以下一栏中减去最后的零减。

+------+------------------------+
| id   |length |  difference  |
+------+------------------------+
|    1 | 1090  |  202         |
|    2 |  888  |  343         |
|    3 |  545  |  111         |
|    4 |  434  |  389         |
|    5 |   45  |   45         |

我尝试了自我参与,但我并不确切地确定如何限制结果,而不是让结果本身循环。我可以肯定的是,如果某个特定结果设定,那么不使用这种价值,那么这种补贴的价值将依次计算。我可以扩大该表,以包括某种顺序价值。

这是我所尝试的:

SELECT id, f.length, f2.length, (f.length - f2.length) AS difference
FROM foo f, foo f2

请你协助。

Answer 1

这可能帮助你(有些东西)。


select a.id, a.length, 
coalesce(a.length - 
    (select b.length from foo b where b.id = a.id + 1), a.length) as diff
from foo a

Answer 2

! trick:

SELECT  f.id, f.length, 
    (f.length - ISNULL(f2.length,0)) AS diff
FROM foo f
LEFT OUTER JOIN foo f2
ON  f2.id = (f.id +1)

Please check for other cases also, it is working for the values you posted! Note this is for SQL Server 2005

Answer 3

因此,他们只是被命令为最小的人?

SELECT f.id, f.length, (f.length - ISNULL(t.length, 0)) AS difference
FROM foo AS f
LEFT JOIN (
    SELECT f1.id
        ,MAX(f2.length) as length
    FROM foo AS f1
    INNER JOIN foo AS f2
        ON f1.length > f2.length
    GROUP BY f1.id
) AS t -- this is the triangle
    ON t.id = f.id

您可使用MySQL的COALESCE(或IFNUL,而不是ISNUL。

Answer 4

这一点:

SELECT T2.ID, T2.[Length], T2.[Length]-T1.[Length] AS  Difference 
FROM Foo AS T1 RIGHT OUTER JOIN Foo AS T2 ON ( T1.ID = (T2.ID-1) )
ORDER BY T1.ID

Answer 5

Select f1.id, f1.seqnum, f2.seqnum, f1.length, f2.length, f1.length-f2.length 

From (

Select Id, length, row_number(order by length)  seqnum 
From
foo

) f1

Inner join (

Select 
Id, length, row_number(order by length)  seqnum  from foo union select 0, 0, 0

) f2 

On f1.seqnum = f2.seqnum + 1

Order by f1.length desc

Answer 6

编辑:在重读Q时确定

SELECT f.id, 
       f2.id, 
       f.length, 
       f2.length, 
       (f.length -f2.length) AS difference
FROM foo f, 
     foo f2 
where f2.id = f.id+1

背景

edit: note: test in mysql 5.0

Answer 7

I had this problem and it was interesting to look at your solutions. I find it strange that such a normal-life problem is so complicated in SQL. As I need the values in a report only, I chose a completely different solution. I m running Ruby on Rails as the front end of my sqlite3 database, and just did the subtraction in the view like this:

在您的废墟控制器中,有一个物体变异的@foo,由您的问答退还。

在这种观点中,只有这样做。

<table border=1>
  <tr>
    <th>id</th>
    <th>length</th>
    <th>difference</th>
  </tr>

<% for i in 0..@foo.length-1 do %>
  <tr>
    <td><%=h @foo[i].id %></td>
    <td><%=h @foo[i].length %></td>
    <td><% if (i==@foo.length-1) then %>
        <%=  @foo[i].length %>
      <% else %>
        <%= @foo[i+1].length.to_i - @foo[i].length.to_i %>
      <% end %>
    </td>
  </tr>
<% end %>
</table>

种子比“质量办法”更强。

友情链接