如下所示,我有三个表,其中一个表具有参考值。我需要计算大小,例如小于20行的2行,然后乘以&;smallerthan20 &;引用表中的值作为输出表中的col: 2*20。我可以想到如何根据表1中的范围进行计数,例如sum (case when t1.size <20 then 1.0 else 0 end) as rownumlessthan20
,但真的不知道如何查找或连接第二个表。非常感谢你的帮助。
产品信息:
+-----------+---+
|. product |size|
+-----------+---+
| apple| 10|
| orange| 5 |
| pear| 30|
参考信息:
+---------------+------------+-------------
| smallerThan20 |largerThan20| largerThan50
+---------------+------------+------------
| 10 | 20 |. 30
您可以尝试使用cross join
select
sum(case when t1.size <20 then 1 else 0 end)*smallerThan20 as rownumlessthan20,
sum(case when t1.size>20 then 1 else 0 end)*largerThan20 as rownumlargerthan20,
sum(case when t1.size>50 then 1 else 0 end)*largerThan50 as rownumlargerthan50
from product_info cross join reference_info