如何通过除以相同的列值来获取值 [英] How to get the value by dividing the same column value

问题描述

我有下面给出的桌子样本

Iam having a table sample as given below

Sno	ColA	ColB	ColC	Perc
1	AA	A01	10	12.50
2	AA	A02	25	31.25
3	AA	A03	15	18.75
4	AA	A04	30	37.50
5	AA	AT	80	100.00
6	BB	B01	20	30.77
7	BB	B02	20	30.77
8	BB	B03	15	23.08
9	BB	B04	10	15.38
10	BB	BT	65	100.00

我必须到达Perc专栏。

Perc = ColC * 100 /（ColC = Col'='AT'）其中ColA ='AA'同样

Perc = ColC * 100 /（ColC = Col'='BT'）其中ColA ='BB'



（即）

Perc = 10 * 100 /80 = 12.50

Perc = 25 * 100/80 = 31.25



我的尝试：



如何通过除以同一列中的值来到达Perc列

I have to arrive the column Perc.
Perc = ColC*100/(ColC where ColB='AT') where ColA='AA' similarly
Perc = ColC*100/(ColC where ColB='BT') where ColA='BB'

(ie)
Perc=10*100/80=12.50
Perc=25*100/80=31.25

What I have tried:

How to arriver Perc Column by dividing values in the same column

推荐答案

这样的事情应该有效：

Something like this should work:

SELECT
    A.Sno,
    A.ColA,
    A.ColB,
    A.ColC,
    Round(100. * A.ColC / B.ColC, 2) As Perc
FROM
    YourTable As A
    INNER JOIN YourTable As T
    ON T.ColA = A.ColA
    And Len(T.ColB) = 2
    And T.ColB = STUFF(T.ColA, 2, 1, 'T')
;

输出：

Sno    ColA    ColB    ColC    Perc
-----------------------------------------------
1      AA      A01     10      12.50000000000
2      AA      A02     25      31.25000000000
3      AA      A03     15      18.75000000000
4      AA      A04     30      37.50000000000
5      AA      AT      80      100.00000000000
6      BB      B01     20      30.77000000000
7      BB      B02     20      30.77000000000
8      BB      B03     15      23.08000000000
9      BB      B04     10      15.38000000000
10     BB      BT      65      100.00000000000

这篇关于如何通过除以相同的列值来获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！