teradata数据库
当我做左连接时,返回的行数更多。我的volatile表中的基本填充是559157行。我要做的是只返回基本人口中的559157行。但运行此查询后:
SELECT distinct a.*, b.column1, b.column2, b.column3, b.column4, b.column5, b.column6, c.column3, SUM(c.column3) AS Total_Column3_Profit, AVG(c.column3) AS Column3_Profit_Average
,CASE
WHEN b.column5 < a.column6 1
ELSE 0
END as column_open_flag
,CASE
WHEN a.column4 < 580 THEN 1
WHEN a.column4 between 580 and 619 THEN 2
WHEN a.column4 between 620 and 639 THEN 3
WHEN a.column4 between 640 and 659 THEN 4
WHEN a.column4 between 660 and 679 THEN 5
WHEN a.column4 between 680 and 699 THEN 6
WHEN a.column4 between 700 and 739 THEN 7
WHEN a.column4 >= 740 THEN 8
ELSE 0
END as column4_band
FROM volatile_table1 a
LEFT JOIN database_table1 b
ON a.column1 = b.column0
LEFT JOIN volatile_table2 c
ON a.column1 = c.column2
GROUP BY a.column1
,a.column2
,a.column3
,a.column4
,a.column5
,a.column6
,b.column1
,b.column2
,b.column3
,b.column4
,b.column5
,b.column6
,c.column3
现在返回的行数超过2000000行。所以我想试试工会条款。以下是我的最新查询:
SELECT a.column1, a.column2, a.column3, a.column4, a.column5, a.column6
,CASE
WHEN b.column5 < a.column6 1
ELSE 0
END as column_open_flag,
CASE
WHEN a.column4 < 580 THEN 1
WHEN a.column4 between 580 and 619 THEN 2
WHEN a.column4 between 620 and 639 THEN 3
WHEN a.column4 between 640 and 659 THEN 4
WHEN a.column4 between 660 and 679 THEN 5
WHEN a.column4 between 680 and 699 THEN 6
WHEN a.column4 between 700 and 739 THEN 7
WHEN a.column4 >= 740 THEN 8
ELSE 0
END as column4_band
FROM volatile_table1 a
UNION
SELECT b.column1, b.column2, b.column3, b.column4, b.column5, b.column6
FROM database_table1 b
UNION
SELECT c.column3, SUM(c.column3) AS Total_Column3_Profit, AVG(c.column3) AS Column3_Profit_Average
FROM volatile_table2 c
GROUP BY a.column1
,a.column2
,a.column3
,a.column4
,a.column5
,a.column6
,b.column1
,b.column2
,b.column3
,b.column4
,b.column5
,b.column6
,c.column3
现在我不断得到3807错误:对象“a”不存在。我跑了一辆
HELP volatile table
查询,它表示这两个易失性表确实存在。有人能告诉我错误的方向吗(我还尝试删除第一个case语句,该语句引用b.column5<a.column6。相同的错误)。
编辑:
我从注解更新到这个,现在出现错误3653。'所有选择列表不包含相同数量的表达式:
SELECT distinct a.column1, a.column2, a.column3, a.column4, a.column5, a.column6, b.column1, b.column2, b.column3, b.column4, b.column5, b.column6, c.column3, SUM(column3) AS Total_Profit, AVG(column3) AS Profit_Averag
,CASE
WHEN b.column5 < a.column6 1
ELSE 0
END as column_open_flag
,CASE
WHEN a.column4 < 580 THEN 1
WHEN a.column4 between 580 and 619 THEN 2
WHEN a.column4 between 620 and 639 THEN 3
WHEN a.column4 between 640 and 659 THEN 4
WHEN a.column4 between 660 and 679 THEN 5
WHEN a.column4 between 680 and 699 THEN 6
WHEN a.column4 between 700 and 739 THEN 7
WHEN a.column4 >= 740 THEN 8
ELSE 0
END as column4_band
FROM
(
SELECT a.column1, a.column2, a.column3, a.column4, a.column5, a.column6
FROM volatile_table1 a
UNION ALL
SELECT b.column1, b.column2, b.column3, b.column4, b.column5, b.column6
FROM database_table1 b
UNION ALL
SELECT c.column3, SUM(c.column3) AS Total_Column3_Profit, AVG(c.column3) AS Column3_Profit_Average
FROM volatile_table2 c
GROUP BY 1
) d
GROUP BY a.column1
,a.column2
,a.column3
,a.column4
,a.column5
,a.column6
,b.column1
,b.column2
,b.column3
,b.column4
,b.column5
,b.column6
,c.column3
1条答案
按热度按时间bvk5enib1#
如果要在执行union all之后进行聚合,请使用子查询或cte。我想查询应该是这样的: