我有两个表account (包含活动客户信息)和account_draft (包含草稿信息)
账号
id |description |currency_code| status |
----|------------------|-------------|--------|
1 AccountTransfer SGD Active
2 CTS SGD Active
Account-draft
id |description |currency_code| status |
----|------------------|-------------|--------|
1 AccountTransfer SGD Approved
2 CTS SGD pending
3 payment SGD deleted如何创建视图包含两个表(活动和草稿)的数据,并根据状态识别每条记录。Active tables包含状态为(Active)的记录,而as draft表包含状态为(draft、approved、pending、hold)的记录
视图如下所示,
View_account
id |description |currency_code| status | Type |
----|------------------|-------------|--------|-----------|
1 AccountTransfer SGD Active active
1 AccountTransfer SGD Approved draft
2 CTS SGD Active active
2 CTS SGD pending draft
3 payment SGD deleted draft 请帮帮忙。
发布于 2020-06-22 13:06:17
您可以使用UNION查询,根据从中读取数据的表添加额外的列Type:
CREATE VIEW alldata AS
SELECT id, description, currency_code, status, 'active' AS Type
FROM Account
UNION ALL
SELECT id, description, currency_code, status, 'draft'
FROM `Account-draft`
ORDER BY id, status然后你就可以
SELECT *
FROM alldata输出:
id description currency_code status Type
1 AccountTransfer SGD Active active
1 AccountTransfer SGD Approved draft
2 CTS SGD Active active
2 CTS SGD pending draft
3 payment SGD deleted drafthttps://stackoverflow.com/questions/62507742
复制相似问题