有这样一个 sql
SELECT (SELECT AVG(L_QUANTITY)
FROM LINEITEM JOIN PART
ON L_PARTKEY = P_PARTKEY),
(SELECT AVG(L_QUANTITY)
FROM LINEITEM JOIN PART
ON L_PARTKEY = P_PARTKEY
WHERE P_RETAILPRICE > 100),
(SELECT AVG(L_QUANTITY)
FROM LINEITEM JOIN PART
ON L_PARTKEY = P_PARTKEY
WHERE P_RETAILPRICE <= 100)
FROM (SELECT *
FROM LINEITEM JOIN PART
ON L_PARTKEY = P_PARTKEY
WHERE P_RETAILPRICE > 100);因为重复进入 LINEITEM和PART ,不知道要怎么减少进入次数
我用 self 写了个
SELECT AVG(l1.L_QUANTITY),AVG(l2.L_QUANTITY),AVG(l3.L_QUANTITY)
FROM LINEITEM l1, LINEITEM l2 , LINEITEM l3, PART p1, PART p2, PART p3
WHERE l1.L_PARTKEY =l2.L_PARTKEY AND l2.L_PARTKEY =l3.L_PARTKEY
AND l1.L_PARTKEY =p1.P_PARTKEY AND l2.L_PARTKEY =p2.P_PARTKEY AND
l3.L_PARTKEY =p3.P_PARTKEY AND p2.P_PARTKEY>100 AND p3.P_PARTKEY <=100
发现select 出来的row不对,而且没有减少进入次数
求解答方法
SELECT (SELECT AVG(L_QUANTITY)
FROM LINEITEM JOIN PART
ON L_PARTKEY = P_PARTKEY),
(SELECT AVG(L_QUANTITY)
FROM LINEITEM JOIN PART
ON L_PARTKEY = P_PARTKEY
WHERE P_RETAILPRICE > 100),
(SELECT AVG(L_QUANTITY)
FROM LINEITEM JOIN PART
ON L_PARTKEY = P_PARTKEY
WHERE P_RETAILPRICE <= 100)
FROM (SELECT *
FROM LINEITEM JOIN PART
ON L_PARTKEY = P_PARTKEY
WHERE P_RETAILPRICE > 100);因为重复进入 LINEITEM和PART ,不知道要怎么减少进入次数
我用 self 写了个
SELECT AVG(l1.L_QUANTITY),AVG(l2.L_QUANTITY),AVG(l3.L_QUANTITY)
FROM LINEITEM l1, LINEITEM l2 , LINEITEM l3, PART p1, PART p2, PART p3
WHERE l1.L_PARTKEY =l2.L_PARTKEY AND l2.L_PARTKEY =l3.L_PARTKEY
AND l1.L_PARTKEY =p1.P_PARTKEY AND l2.L_PARTKEY =p2.P_PARTKEY AND
l3.L_PARTKEY =p3.P_PARTKEY AND p2.P_PARTKEY>100 AND p3.P_PARTKEY <=100
发现select 出来的row不对,而且没有减少进入次数
求解答方法
--這個意思?
select avg(L_QUANTITY),
avg(case when P_RETAILPRICE>100 then L_QUANTITY else null end),
avg(case when P_RETAILPRICE<=100 then L_QUANTITY else null end)
FROM LINEITEM JOIN PART
ON L_PARTKEY = P_PARTKEY;