MYSQL创建视图，从两个表

问题描述：

deposits 
id |userId | amount| Date 
1 | 2 | 150 | 2013-11-22 02:57:00 
2 | 3 | 230 | 2013-11-25 03:19:00 

withdrawals 
id |userId | amount| Date 
1 | 2 | 150 | 2013-11-23 02:57:00 
2 | 3 | 190 | 2013-11-27 02:27:00

我想创建一个视图，将显示此格式的两个表中的数据最好记录应由日期字段进行排序尽管它并不重要，因为我可以按日期顺序查询视图。

depositsAndWithdrawal 
type  | id | userId| amount | Date 
deposit  | 1 | 2 |  150 | 2013-11-22 02:57:00 
withdrawal | 1 | 2 |  150 | 2013-11-23 02:57:00 
deposit  | 2 | 3 |  230 | 2013-11-25 03:19:00 
withdrawal | 2 | 3 |  190 | 2013-11-27 02:27:00

这甚至有可能吗？或者我是否需要创建一个新表并使用插入事件将相关行添加到该表中？

答

您正在查找union all查询。你可以在MySQL视图做到这一点：

create view v as 
    select 'deposit' as which, id, userId, amount, Date 
    from deposits 
    union all 
    select 'withdrawals' as which, id, userId, amount, Date 
    from withdrawals ;

答

大致如下（借口小错误）的东西线：

create view depositsAndWithdrawal as 
(
    select 'deposits' as type, id, userID, amount, date 
    from deposits 
    UNION 
    select 'withdrawls' as type, id, userID, amount, date 
    from widthdrawls 
)

然后，您可以查询此使用：

select * from depositsAndWithdrawal order by date;

不幸的是，我不认为你可以有这样的观点，因为你需要在视图中使用临时表格，例如：

不起作用：

create view depositsAndWithdrawal as 
    (
     select * from 
     (select 'deposits' as type, id, userID, amount, date 
     from deposits 
     UNION 
     select 'withdrawls' as type, id, userID, amount, date 
     from widthdrawls) as temp order by date 
    )

但是你可以将问题一分为二的观点：

create view depositsAndWithdrawalTemp as 
    (
     select 'deposits' as type, id, userID, amount, date 
     from deposits 
     UNION 
     select 'withdrawls' as type, id, userID, amount, date 
     from widthdrawls 
    ) 

create view depositsAndWithdrawal as 
select * from depositsAndWithdrawalTemp order by date;

MYSQL创建视图，从两个表

相关推荐