Question

样本表: 我的表1

Col1    |    Col2     |     Col3
   3           10            5
  10           9             40
   1           2             6

产出必须是:

NewColumn
    10
    40
     6

如你所见,我只需要从每一行得到最高值...

使用 sql 选择查询是否可行?

提前感谢

Answer 1

您需要使用 MS Access 功能 IIIF ()

IIF(condition, valueiftrue, valueiffalse)

条件是要测试的值。

valueiftrue is the value that is returned if condition evaluates to TRUE.

valueiffalse is the value that is returned if condition evaluates to FALSE.

您的查询将会是

SELECT IIf(C12 > Col3, C12, Col3) as newColumn
FROM
(
    select IIF(Col1 > Col2,  Col1,  Col2) as C12, Col3
    from mytable1
) x

Answer 2

我想这在MS Access(我恐怕能验证我自己):

SELECT
  (
    SELECT MAX(Col)
    FROM (
      SELECT Col1 AS Col UNION ALL
      SELECT Col2        UNION ALL
      SELECT Col3
    ) s
  ) AS NewColumn
FROM yourtable

这在数据库产品中的另一家 由同一销售商组成的数据库产品系列中起作用, 所以我想他们可能刚好在MS Access中对此也增加了支持。

Answer 3

起作用的示例:

<强>表1:

Col - Text
Col1 - Number
Col2 - Number
Col3 - Number
Col4 - Number

<强 > 数据:

col; col1; col2; col3; col4;
a;      1;    4;    6;    7;
b;      3;   66;   23;  235;
c;     34;  634;   11;   23;

(强/强)

SELECT Col, Max(colx) AS MaxOfColx
FROM 
(
SELECT Col, Col1 AS Colx From Table1 UNION ALL
SELECT Col, Col2 AS Colx From Table1 UNION ALL
SELECT Col, Col3 AS Colx From Table1 UNION ALL
SELECT Col, Col4 AS Colx From Table1
)
group by Col

<强>Result:

Col; MaxOfColx
 a;          7
 b;        235
 c;        634

这将对您需要的多个列有效。

友情链接