Question

让我们采取这一简单表格:

CREATE TABLE correlation (  1  REAL,  2  REAL,  3  REAL)
INSERT INTO correlation VALUES ( 1.0 , -0.4 , 0.6 )
INSERT INTO correlation VALUES ( -0.4 , 1.0 , 0.2 )
INSERT INTO correlation VALUES ( 0.6 , 0.2 , 1.0 )

现在希望在囚室里形成一种观点并做一些简单的数学:

CREATE VIEW correlation_codes AS SELECT 
CASE 
WHEN * > 0 THEN 3
WHEN * < 0 THEN 5
WHEN * == 0 THEN 8
WHEN * == 1.0 THEN 4
ELSE 99
END
FROM correlation

预期成果:

 1.0  -0.4  0.6
-0.4   1.0  0.2
 0.6   0.2  1.0

成为:

 4      5     3
 5      4     3
 3      3     4

我不能在化学文摘社国际协会中使用*,但我想在整个桌旁有一个化学文摘社。

我应该做些什么?

Answer 1

首先,你为什么将数字(人数、oka、可直接投向数字的插图)作为栏目? 这只是要求麻烦,特别是在<条码>、<>条/条码>或<条码>、、声明(<条码>、WHERE 1 >0中,最好能找到。

第二,你在一份<条码>说明中重新正确,因为你可以使用<条码>*。页: 1 然而,当一栏名数(基本上)为:

select case when 1 >0 then 3 when 1 <0 then 5 when 1 ==0 then 8 when 1 ==1.0 then 4 else 99 end from correlation;

产量

3
3
3

相反,请举一栏号:

sqlite> create table correlation2 (c1 real,c2 real,c3 real);
sqlite> insert into correlation2 values (1.0,-0.4,0.6);
sqlite> insert into correlation2 values (-0.4,1.0,0.2);
sqlite> insert into correlation2 values (0.6,0.2,1.0);

现在,案件陈述表明:

sqlite> select case when c1>0 then 3 when c1<0 then 5 when c1==0 then 8 when c1==1.0 then 4 else 99 end from correlation2;
case when c1>0 then 3 when c1<0 then 5 when c1==0 then 8 when c1==1.0 then 4 else 99 end
3
5
3

现在,你想要一栏数值。

4
5
3

However, that s not how the case statement works: Since 1.0>1, the first branch of the case statement is evaluated (and hence the first entry is assigned to 3, not 4). Is this what you meant?

sqlite> select case when c1>0 and c1!=1 then 3 when c1<0 then 5 when c1==0 then 8 when c1==1 then 4 else 99 end from correlation2;
case when c1>0 and c1!=1 then 3 when c1<0 then 5 when c1==0 then 8 when c1==1 then 4 else 99 end
4
5
3

Answer 2

You could create a UDF and run each column through the UDF, to do the transformation. I think you can use the UDF in a view:

           create view myView as
           select udf(col1), udf(col2), udf(col3) ...
           from T

友情链接