我有三个表,它们的字段(除了add_date之外)彼此不相关(从理论上讲)。我想创建一个视图,在其中可以查看按日期排序的这些表中的更改:
+----------+------------+------------+------------+-----------+------------+
| add_date | table1.f1 | | | | |
| add_date | | table2.f1 | | | |
| add_date | | | table3.f1 | | |
...
etc
...
+----------+------------+------------+------------+-----------+------------+
问题2:如果某些字段是共享的但不是始终共享怎么办?例如:
table 1: payment history (user, value, currency, add_date)
for example payment to virtual wallet but
it can also to be a payment for order or
payment for other things
table 2: purchases (user, product_id, add_date, order_id)
table 3: withdraws (user, value, add_date)
maybe auxiliary table in this case: orders but not in any case
我想为多个表创建一个更改历史记录。
怎么做?
用于UNION
从您的单个表中进行选择,并确保您的SELECT
语句包含相同的列名,以替换NULL
表中不存在名称的位置。
CREATE VIEW myView AS
SELECT add_date,
t1.somecolumn col1,
NULL col2,
NULL col3
FROM t1
UNION ALL
SELECT
add_date,
NULL col1,
t2.anothercolumn col2,
NULL col3
FROM t2
UNION ALL
SELECT
add_date,
NULL col1,
NULL col2,
t3.yetanothercolum col3
FROM t3
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句