如何更正此SQL查询并对其进行优化？

I am running this SQL query and it is giving me an error, When i re-arrange the columns and make DISTINCT k.Column_1 come first it works but then runs forever and seems to an expensive one. How can i correct this, optimize it etc?

我正在运行这个SQL查询,它给了我一个错误,当我重新安排列并使DISTINCT k.Column_1首先它起作用,但然后运行永远,似乎是一个昂贵的。我怎样才能纠正这个,优化它等?

SELECT 
k.Column_2, DISTINCT k.Column_1, s.Column_3, s.Column_4, s.Column_5, s.Column_6, s.Column_7,
s.Column_8, s.Column_9, s.Column_10, s.Column_11, t.Column_5,
t.Column_6, t.Column_7, t.Column_8, t.Column_9, t.Column_10,
t.Column_11, r.Column_3,
p.Column_2, p.Column_3, p.Column_4, p.5, p.Column_6, p.Column_7, p.Column_8, p.Column_9
FROM table_1 k
LEFT JOIN table_2 s
ON k.Column_1 = s.Column_1
LEFT JOIN table_3 t
ON k.Column_1 = t.Column_1
LEFT table_4 r
ON k.Column_1 = r.Column_1
LEFT JOIN table_5 p
ON k.Column_1 = p.Column_1

I am now running this edited SQL Query and it is running forever, i would like to optimize it if possible.

我现在正在运行这个已编辑的SQL查询,它正在运行,我想尽可能优化它。

Column_1 is the index.

Column_1是索引。

SELECT DISTINCT k.Column_1, k.MONTH, 
SUM(s.Column_3) Column_3, 
SUM(s.Column_4) Column_4, 
SUM(s.Column_5) Column_5, 
SUM(s.Column_6) Column_6, 
SUM(s.Column_7) Column_7,
SUM(s.Column_8) Column_8, 
SUM(s.Column_9) Column_9, 
SUM(s.Column_10) Column_10, 
SUM(s.Column_11) Column_11, 
SUM(t.Column_5) Column_5,
SUM(t.Column_6) Column_6, 
SUM(t.Column_7) t.Column_7, 
SUM(t.Column_8) Column_8, 
SUM(t.Column_9) Column_9, 
SUM(t.Column_10) Column_10,
SUM(t.Column_11) Column_11, 
SUM(r.Column_3) Column_3,
SUM(p.Column_2) Column_2, 
SUM(p.Column_3) Column_3, 
SUM(p.Column_4) Column_4, 
SUM(p.Column_5) Column_5, 
SUM(p.Column_6) Column_6, 
SUM(p.Column_7) Column_7, 
SUM(p.Column_8) Column_8, 
SUM(p.Column_9) Column_9
FROM table_1 k
LEFT JOIN table_2 s
ON k.Column_1 = s.Column_1
LEFT JOIN table_3 t
ON k.Column_1 = t.Column_1
LEFT table_4 r
ON k.Column_1 = r.Column_1
LEFT JOIN table_5 p
ON k.Column_1 = p.Column_1
GROUP BY k.Column_1, k.MONTH

2 个解决方案

#1

DISTINCT k.Column_1, is incorrect and wrong syntax. distinct keyword applies to the entire set of attributes you are fetching and not per column .

DISTINCT k.Column_1,语法错误且错误。 distinct关键字适用于您要提取的整个属性集,而不是每列。

It should be

它应该是

SELECT DISTINCT k.Column_2, k.Column_1, s.Column_3, s.k.Column_4, s.Column_5, ....

#2

One big thing to take into consideration is that joins can return a lot more combinations then you may expect. For instance, take these two tables.

需要考虑的一件大事是,连接可以返回比您预期更多的组合。例如,拿这两个表。

Table_A  Table_B
+-----+  +-----+
|Var_A|  |Var_B|
+-----+  +-----+
|  2  |  |  2  |
|  2  |  |  2  |
|  2  |  |  4  |
|  4  |  +-----+
+-----+

If you run

如果你跑

SELECT 
    * 
FROM table_a
LEFT JOIN table_b
ON table_a.var_a = table_b.var_b

You'll get this mess, because it's matching every possible combination of values between var_a and var_b

你会得到这个混乱,因为它匹配var_a和var_b之间的每个可能的值组合

+-----+-----+
|Var_A|Var_B|
+-----+-----+
|  2  |  2  |
|  2  |  2  |
|  2  |  2  |
|  2  |  2  |
|  2  |  2  |
|  2  |  2  |
|  4  |  4  |
+-----+-----+

To avoid running into this issue, make your joins more restrictive by joining on more fields, even if you ultimately don't end up using them.

为避免遇到此问题,请加入更多字段,使连接更具限制性,即使您最终不使用它们也是如此。

#1