- 使用with语法,创建子查询T3,T4
T3+T4正常
T3-T4正常
T3*T4正常
T3/T4异常
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|
简化,除法还是异常
WITH
T1 AS (SELECT COUNT(FirstName) NUMBER_ONE FROM Customer),
T2 AS (SELECT COUNT(FirstName) NUMBER_TWO FROM Customer WHERE SupportRepId=3),
DEMO_A AS (SELECT((SELECT * FROM T2)/(SELECT * FROM T1)))
SELECT * FROM T1,T2,DEMO_A
NUMBER_ONE|NUMBER_TWO|((SELECT * FROM T2)/(SELECT * FROM T1))|
----------+----------+---------------------------------------+
59| 21| 0|
简化过程中,手残,好像又发现个问题,T1/T1支持,T2/T1不支持
WITH
T1 AS (SELECT COUNT(FirstName) NUMBER_ONE FROM Customer),
T2 AS (SELECT COUNT(FirstName) NUMBER_TWO FROM Customer WHERE SupportRepId=3),
DEMO_A AS (SELECT((SELECT * FROM T1)/(SELECT * FROM T1))AS DIVISION)
SELECT * FROM T1,T2,DEMO_A
NUMBER_ONE|NUMBER_TWO|DIVISION|
----------+----------+--------+
59| 21| 1|
换个明显的思路,同公式可以相除,尝试去除where条件
WITH
T1 AS (SELECT COUNT(FirstName) NUMBER_ONE FROM Customer),
T3 AS (SELECT SUM(CustomerId) NUMBER_TWO FROM Customer),
DEMO_A AS (SELECT((SELECT * FROM T3)/(SELECT * FROM T1))AS DIVISION)
SELECT * FROM T1,T3,DEMO_A
NUMBER_ONE|NUMBER_TWO|DIVISION|
----------+----------+--------+
59| 1770| 30|
去除where条件可以…
问题在哪里
|