dongyi5817
dongyi5817
2016-12-02 14:29

将不同表中的不同字段存储到临时表中

已采纳

I am trying to create a temporary table where it has two fields (user,pass) which was a combination from three tables

TABLES

agents
username | password

clients
username | password

admin
username | password

i want their data from their fields to be stored to a temporary table

TEMPORARY TABLE

temp_tbl
username | password

This is my MySQL query (i know this is a wrong syntax)

CREATE TEMPORARY TABLE temp_tbl ENGINE=MEMORY 
AS (SELECT username,password FROM agents UNION SELECT username,password FROM clients UNION SELECT username,password FROM admin) AS col1, col2;
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答

  • dsds33222 dsds33222 5年前

    Pretty close

    CREATE TEMPORARY TABLE temp_tbl ENGINE=MEMORY 
    AS SELECT * FROM 
     (SELECT username,password FROM agents 
     UNION SELECT username,password FROM clients 
     UNION SELECT username,password FROM admin) AS t1;
    

    You have to wrap those three unions inside a another SELECT and you need to give that derived table an alias. Just one name please not two (t1 instead of col1, col2)

    Unless you wrap the whole thing aaround another query as I have done, you will get a syntax error.

    点赞 评论 复制链接分享
  • dpw43061 dpw43061 5年前
    CREATE VIEW all_user AS
       SELECT * FROM agents
       UNION ALL
       SELECT * FROM clients
       UNION ALL
       SELECT * FROM admin
    
    点赞 评论 复制链接分享
  • douyin6188 douyin6188 5年前

    Your query should look like below rather. Per MySQL documentation you can use CTAS construct for creating temporary table as well.

    CREATE TEMPORARY TABLE temp_tbl
    AS (SELECT username, password FROM agents 
    UNION 
    SELECT username, password FROM clients 
    UNION 
    SELECT username, password FROM admin);
    
    点赞 评论 复制链接分享