使用 UNION 创建 MySQL 视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/514817/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating MySQL View using UNION
提问by
I am trying to create a view for the following query.
我正在尝试为以下查询创建视图。
SELECT DISTINCT
products.pid AS id,
products.pname AS name,
products.p_desc AS description,
products.p_loc AS location,
products.p_uid AS userid,
products.isaproduct AS whatisit
FROM products
UNION
SELECT DISTINCT
services.s_id AS id,
services.s_name AS name,
services.s_desc AS description,
services.s_uid AS userid,
services.s_location AS location,
services.isaservice AS whatisit
FROM services
But not able to do so. I am using MySql query browser. The error I am getting is:
但不能这样做。我正在使用 MySql 查询浏览器。我得到的错误是：
A view can only be created from an active resultset of SELECT command
只能从 SELECT 命令的活动结果集创建视图
Can someone please help me with this?
有人可以帮我解决这个问题吗？
回答by
CREATE VIEW vw_product_services AS
SELECT DISTINCT products.pid AS id,
products.pname AS name,
products.p_desc AS description,
products.p_loc AS location,
products.p_uid AS userid,
products.isaproduct AS whatisit
FROM products
UNION
SELECT DISTINCT services.s_id AS id,
services.s_name AS name,
services.s_desc AS description,
services.s_uid AS userid,
services.s_location AS location,
services.isaservice AS whatisit
FROM services
I tried this and it worked! Thanks everyone :)
我试过了，它奏效了！谢谢大家 ：）
回答by Learning
You might want to swith the order of userid and location in the second select. The column names should match 1 to 1 in all selects of the union.
您可能希望在第二个选择中更改用户 ID 和位置的顺序。在联合的所有选择中，列名应该匹配 1 到 1。
EDIT : For query browser , as thispoints out "To create a view from a query, you must have executed the query successfully. To be more precise, the view is created from the latest successfully executed query, not necessarily from the query currently in the Query Area"
编辑：对于查询浏览器，正如这指出“要从查询创建视图，您必须已成功执行查询。更准确地说，视图是从最近成功执行的查询创建的，不一定是从当前的查询创建的查询区”
so you need to execute the query first before you create the view in query browser.
因此您需要先执行查询，然后再在查询浏览器中创建视图。
The error is from the query browser and not mysql.
错误来自查询浏览器而不是 mysql。
回答by dkretz
You have different types being unioned into the same column. (The names can be different, but the types have to be the same, or at least auto-castable.) But as @Learning points out, it looks like you've twisted the SELECT column enumerations.
您将不同的类型合并到同一列中。（名称可以不同，但类型必须相同，或者至少可以自动转换。）但正如@Learning 指出的那样，您似乎扭曲了 SELECT 列枚举。
Just in case, the proper syntax (which worked for me) is
以防万一，正确的语法（对我有用）是
CREATE VIEW myView
AS
SELECT ...
回答by dkretz
Just a little remark about UNION. UNION only returns the distinct values of your resultset. So there is no need to use SELECT DISTINCT combined with a UNION. Probably better for performance to not use DISTINCT too.
关于 UNION 的一点评论。UNION 只返回结果集的不同值。所以没有必要将 SELECT DISTINCT 与 UNION 结合使用。不使用 DISTINCT 对性能可能更好。
More info on UNION can be found here: SQL UNION Operator
可以在此处找到有关 UNION 的更多信息：SQL UNION 运算符
回答by Tomalak
The error message is in "QueryBrowser.pas", part of mysql-gui-tools.
错误消息位于mysql-gui-tools 的“QueryBrowser.pas”中。
procedure TQueryBrowserForm.SQLCreateViewClick(Sender: TObject);
// ...
begin
if Assigned(ActiveResultset) and (ActiveResultset.ResultSet.query.query_type = MYX_QT_SELECT)then
// ...
else
ShowError('Creation error', _('A view can only be created from a active resultset of SELECT command.'), []);
end;
It is triggered by a) not having an active result set and b) the query having the wrong type.
它由 a) 没有活动结果集和 b) 查询类型错误触发。
Does removing the "DISTINCT" make any difference? In any case, this is a bug in QueryBrowser, rather than one MySQL. Creating the view directly in MySQL should suffice as a work-around.
删除“DISTINCT”有什么区别吗？无论如何，这是 QueryBrowser 中的一个错误，而不是一个 MySQL。作为一种变通方法，直接在 MySQL 中创建视图就足够了。
