blocks|key|3074674|text|如果您确定不会有重复项，那么您可以通过将UNION替换为不带ALL的UNION+ALL来增强它。查询的结果将被排序，当UNIONing时，重复项将被删除。请参阅http://dev.mysql.com/doc/refman/5.0/en/union.html|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3074675|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/union.html^0|K|5|U|3|Y|9|28|1D|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]|$9|Q|A|R|B|C]|$9|S|A|T|B|C]]|D|@$9|U|A|V|1|W]]|E|$]]|$1|F|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

If you are sure that there will be no duplicates, then you can boost it by replaceng <code>UNION</code> with <code>UNION ALL</code> Without the <code>ALL</code> the results of the queries will be sorted and the duplicates will be removed when UNIONing. see <a href="http://dev.mysql.com/doc/refman/5.0/en/union.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/union.html</a>

blocks|key|3074711|text|您可以使用外部联接进行联接和比较，然后使用COALESCE优先使用T2而不是T1。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3074712|为了保留唯一键，并假设您的id都是正数，您可以将一个表的id设为负数。|3074713|SELECT
++COALESCE(t2.id,+++++-t1.id)++++++AS+id,
++COALESCE(t2.Column1,+t1.Column1)+AS+Column1,
++COALESCE(t2.Column2,+t1.Column2)+AS+Column2
FROM
++Table1++++++++++AS+t1
FULL+OUTER+JOIN
++Table2++++++++++AS+t2
++++ON++t1.Column1+=+t2.Column2
++++AND+t1.Column2+=+t2.Column2
WHERE
++COALESCE(t2.Column3,+t1.Column3)+=+'String'|code-block|syntax|javascript|3074714|编辑：|offset|length|style|BOLD|3074715|对于更复杂的关于选择哪个表具有精度的规则，您可以只使用CASE语句...|3074716|(这与上面的功能相同，但可以针对不同的精度规则进行修改。)|3074717|SELECT
++CASE+WHEN+t2.id+IS+NULL+THEN+-t1.id++++++ELSE+t2.id++++++END++AS+id,
++CASE+WHEN+t2.id+IS+NULL+THEN++t1.Column1+ELSE+t2.Column1+END++AS+Column1,
++CASE+WHEN+t2.id+IS+NULL+THEN++t1.Column2+ELSE+t2.Column2+END++AS+Column2
FROM
++Table1++++++++++AS+t1
FULL+OUTER+JOIN
++Table2++++++++++AS+t2
++++ON++t1.Column1+=+t2.Column2
++++AND+t1.Column2+=+t2.Column2
WHERE
++COALESCE(t2.Column3,+t1.Column3)+=+'String'|3074718|entityMap^0|0|0|0|0|3|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|Z|8|@$K|10|L|11|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

You can join and compare, using an outer join, then use COALESCE to prefer T2 to T1.

To retain a unique key, and assuming your ID's are all positive, you can make one table's id's negative.

<pre><code>SELECT
 COALESCE(t2.id, -t1.id) AS id,
 COALESCE(t2.Column1, t1.Column1) AS Column1,
 COALESCE(t2.Column2, t1.Column2) AS Column2
FROM
 Table1 AS t1
FULL OUTER JOIN
 Table2 AS t2
 ON t1.Column1 = t2.Column2
 AND t1.Column2 = t2.Column2
WHERE
 COALESCE(t2.Column3, t1.Column3) = 'String'
</code></pre>

EDIT:

For more complex rules on selecting which table has precidence, you can just use CASE statements...

(This does the same as above, but can be modified for different precidence rules.)

<pre><code>SELECT
 CASE WHEN t2.id IS NULL THEN -t1.id ELSE t2.id END AS id,
 CASE WHEN t2.id IS NULL THEN t1.Column1 ELSE t2.Column1 END AS Column1,
 CASE WHEN t2.id IS NULL THEN t1.Column2 ELSE t2.Column2 END AS Column2
FROM
 Table1 AS t1
FULL OUTER JOIN
 Table2 AS t2
 ON t1.Column1 = t2.Column2
 AND t1.Column2 = t2.Column2
WHERE
 COALESCE(t2.Column3, t1.Column3) = 'String'
</code></pre>

blocks|key|5145124|text|这应该看起来像这样：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5145125|SELECT+
++++IFNULL(t2.Col1,+t1.Col1)+Col1,
++++IFNULL(t2.Col2,+t1.Col2)+Col2,
++++IFNULL(t2.Col3,+t1.Col3)+Col3,
++++IFNULL(t2.Col4,+t1.Col4)+Col4,
++++'Add+this+string+to+the+Last+Column'+
FROM+
++++Table1+t1
++++LEFT+JOIN+Table2+t2+ON+t1.Column1+=+t2.Column1+AND+t1.Column2+=+t2.Column2
WHERE+
++++t1.Column3+='String'
ORDER+BY+
++++Col4|code-block|syntax|javascript|5145126|如果数据库中的table2列可以有正常的NULL值，那么这个SELECT应该像这样开始：|5145127|SELECT+
++++IF(t2.id+is+NULL,+t1.Col1,+t2.Col1)+Col1,
++++IF(t2.id+is+NULL,+t1.Col2,+t2.Col2)+Col1,
++++IF(t2.id+is+NULL,+t1.Col3,+t2.Col3)+Col1,
++++IF(t2.id+is+NULL,+t1.Col4,+t2.Col4)+Col1,
++++'Add+this+string+to+the+Last+Column'+
++++.
++++.|5145128|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This should look something like this:

<pre><code>SELECT 
 IFNULL(t2.Col1, t1.Col1) Col1,
 IFNULL(t2.Col2, t1.Col2) Col2,
 IFNULL(t2.Col3, t1.Col3) Col3,
 IFNULL(t2.Col4, t1.Col4) Col4,
 'Add this string to the Last Column' 
FROM 
 Table1 t1
 LEFT JOIN Table2 t2 ON t1.Column1 = t2.Column1 AND t1.Column2 = t2.Column2
WHERE 
 t1.Column3 ='String'
ORDER BY 
 Col4
</code></pre>

If table2 columns can have normal NULL values in your database then this SELECT should start like this:

<pre><code>SELECT 
 IF(t2.id is NULL, t1.Col1, t2.Col1) Col1,
 IF(t2.id is NULL, t1.Col2, t2.Col2) Col1,
 IF(t2.id is NULL, t1.Col3, t2.Col3) Col1,
 IF(t2.id is NULL, t1.Col4, t2.Col4) Col1,
 'Add this string to the Last Column' 
 .
 .
</code></pre>

I have a <code>VIEW</code> which is SLOW. I don't like the VIEW Statements as there are lot of <code>JOINS</code> and <code>UNION</code>. 

Here is the view statement.

<pre><code>Create VIEW NewView AS

SELECT t2.* FROM Table1 t1
JOIN Table2 t2
ON t1.Column1 = t2.Column1 AND t1.Column2 = t2.Column2
WHERE t1.Column3 !='String'

UNION

SELECT t1.*, 'Add this string to the Last Column' FROM Table1 t1
LEFT JOIN Table2 t2
ON t1.Column1 = t2.Column1 AND t1.Column2 = t2.Column2
WHERE t2.Column1 is null OR t1.Column3 ='String'
ORDER BY Column 4
</code></pre>

Basically the idea is if a record exists in <code>Table1</code> and <code>Table2</code>, the record from <code>Table2</code> should overlay the record from <code>Table1</code>. How can I optimize this?

I have a primary key <code>id INT NOT NULL AUTO_INCREMENT PRIMARY KEY</code> in both the tables but I am not sure how I can integrate that with the view. I want the view to have a primary key or composite key. I cannot use other columns as all the columns can have null and duplicate values.

How can optimize this VIEW Statement?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一台速度很慢的VIEW。我不喜欢VIEW语句，因为有很多JOINS和UNION。下面是view语句。Create VIEW NewView ASSELECT t2.* FROM Table1 t1JOIN Table2 t2ON t1.Column1 = t2.Column1 AND t1.Column2 = t2...

问如何优化此VIEW语句？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何优化此VIEW语句？EN