SQL将B列中的值除以取决于A列中条目的不同值 [英] SQL divide values in column B by distinct values dependent on entries in column A

问题描述

给出A和B列:

A B

Small     3

Med       4

Med       1

Large     2

Small     1

假设我有X，Y和Z数值.我想创建一个新列，仅当A列的同一行中的条目为"Small"时，才输出B列和X列的值的商.如果A列具有"Med"，则除以Y；如果A列具有"Large"，则除以Z.它将继续逐行检查此行.例如，在B列的第一行中，我们有'3'，然后在A列的同一行中，我们有'Small'，因此我们将输出3/X.因此，我们将其称为C的预期列将是:

Let's say I have numerical values X, Y, and Z. I want to create a new column that outputs the quotients of values of column B and X only if the entry in the same row in column A is "Small". If column A has "Med", we divide by Y and if it has "Large", we divide by Z. It would continue to check this row by row. For example, in the first row of column B, we have '3', then on the same row in column A, we have 'Small', so we would output 3/X. So the expected column, let's call it C, would be:

C

3/X

4/Y

1/Y

2/Z

1/X

如何使用SQL输出C?

How would I output C using SQL?

推荐答案

将ColumnA除以Switch()函数返回的值.使用Switch是因为它比嵌套的IIf()更简单.当然，这是假设实际数字将代替x，y，z，因此不会将其括在撇号或引号中.

Divide ColumnA by a value returned from Switch() function. Use Switch because it is simpler than nested IIf(). Of course, this assumes actual numbers will be in place of x, y, z and therefore don't enclose in apostrophes or quotes.

SELECT ColumnA, ColumnB,
    ColumnB / Switch(
        ColumnA="Small", x,
        ColumnA="Med", y,
        ColumnA="Large", z
    ) AS Qt
FROM table;

这篇关于SQL将B列中的值除以取决于A列中条目的不同值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！