如何在SQL中从同一列中划分两个值

发布时间：2020-12-12 07:08:43 所属栏目：MsSql教程来源：网络整理

导读：正如标题所述,我想将两个值彼此分开,这两个值在同一列中. 例如. A B C D Shirts 2011 85 0 Shirts 2012 92 percent change from 2011 to 2012 Shirts 2013 100 percent change from 2012 to 2013 Pants 2011 31 0 Pants 2012 42 percent change from 2011 to

正如标题所述,我想将两个值彼此分开,这两个值在同一列中.
例如.

A             B              C            D
  Shirts         2011           85            0
  Shirts         2012           92      percent change from 2011 to 2012
  Shirts         2013          100      percent change from 2012 to 2013
  Pants          2011           31            0
  Pants          2012           42      percent change from 2011 to 2012
  Pants          2013           55      percent change from 2012 to 2013
  Jacket         2011           10            0
  Jacket         2012           16      percent change from 2011 to 2012
  Jacket         2013           18      percent change from 2012 to 2013

在此示例中,列D将从列C派生,其中2012的值从2011中减去,然后乘以100以获得百分比.

我不知道如何设置查询我尝试做一堆子查询但不知道如何将它们链接在一起.任何帮助将不胜感激.

解决方法

这是一个选择,让你得到你想要的东西

SELECT t1.a,t1.b,t1.c,CASE WHEN t2.c IS NULL THEN 0 ELSE t1.c - t2.c END AS d,CASE WHEN t2.c IS NULL THEN 0 ELSE (1.0 * t1.c - t2.c) / t1.c * 100.0 END AS pct
FROM t t1
LEFT OUTER JOIN t t2 ON t1.a = t2.a
    AND t1.b = t2.b + 1

SQL Fiddle Example

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!