DB2使用with子查询的时候,添加where条件无法进行除法运算,
刚开始:
WITH
T1 AS (SELECT * FROM Track T LEFT JOIN Customer C on C.CustomerId =T.TrackId WHERE T.UnitPrice =0.99 and T.Bytes >500000),
T2 AS (SELECT SUM(Bytes)/COUNT(Name) FROM T1),
T3 AS (SELECT COUNT(Name) num_one FROM T1),
T4 AS (SELECT COUNT(*) AS number_two FROM T1 WHERE Bytes <(SELECT * FROM T2)),
demo_a as(SELECT ((SELECT * FROM T4)+(SELECT * FROM T3))AS addition),
demo_b as(SELECT ((SELECT * FROM T4)-(SELECT * FROM T3))AS subtraction),
demo_c as(SELECT ((SELECT * FROM T4)*(SELECT * FROM T3))AS multiplication),
demo_d as(SELECT ((SELECT * FROM T4)/(SELECT * FROM T3))AS division)
SELECT * FROM T3,T4,demo_a,demo_b,demo_c,demo_d;
数据库返回:
num_one|number_two|addition|subtraction|multiplication|division|
-------+----------+--------+-----------+--------------+--------+
3284| 1880| 5164| -1404| 6173920| 0|
简化SQL后:
WITH
T1 AS (SELECT COUNT(FirstName) NUMBER_ONE FROM Customer),
T2 AS (SELECT SUM (CustomerId) NUMBER_TWO FROM Customer WHERE CustomerId=9),
DEMO_A AS (SELECT((SELECT * FROM T2)/(SELECT * FROM T1))AS DIVISION)
SELECT * FROM T2,T1,DEMO_A
SQL还是无法相除:
NUMBER_TWO|NUMBER_ONE|DIVISION|
----------+----------+--------+
9| 59| 0|
第三次手残简化,去除where条件,竟然成功了:
WITH
T1 AS (SELECT COUNT(FirstName) NUMBER_ONE FROM Customer),
T2 AS (SELECT SUM (CustomerId) NUMBER_TWO FROM Customer),
DEMO_A AS (SELECT((SELECT * FROM T2)/(SELECT * FROM T1))AS DIVISION)
SELECT * FROM T2,T1,DEMO_A
NUMBER_TWO|NUMBER_ONE|DIVISION|
----------+----------+--------+
1770| 59| 30|
