SQL虚拟表
我的数据库设置如下：
我拥有的每个产品都有自己的表，其中记录销售数量/交易#（因此第 1 列是“transactionID”，第 2 列是“数量”）
ex）p-backScratcher（其中 p - 表示“产品”）
每年还有一些表格，保存所经历的每笔交易的记录。每个表都包含以下列：“transactionID”、“date”、“time”、“pt_CA”、“pt_DB”、“pt_VC”、“pt_MC”、“pt_CH”、“pt_AM”
ex) sales-2008， sales-2009 等。
我希望能够引用一个包含每年所有记录的表，而不必更改该表的 sql 以包含新的一年。
例如，我想查询“p-backScratcher”的所有交易，我不想输入
SELECT sales-2008.date, sales-2009.date
FROM sales-2008, sales-2009
WHERE sales-2008.transactionID = p-backScratcher.transactionID
OR sales-2009.transactionID = p-backScratcher.transactionID
......而是：
SELECT sales.date
FROM sales
WHERE sales.transactionID = p-backScratcher.transactionID
I have my database set up as such:
Each product I have has it's own table where it records the quantity sold/transaction # (so column 1 is "transactionID", column 2 is "quantity")
ex) p-backScratcher (where p- is indicative of "product")
There are also tables for each year which hold records of each transaction that went through. Each of these tables holds the following columns: "transactionID", "date", "time", "pt_CA", "pt_DB", "pt_VC", "pt_MC", "pt_CH", "pt_AM"
ex) sales-2008, sales-2009, etc. etc.
I'd like to be able to reference a single table that holds all the records for each year without having to change the sql for the table to include a new year.
So for example, I'd want to query all transactions for "p-backScratcher", I don't want to have to type out
SELECT sales-2008.date, sales-2009.date
FROM sales-2008, sales-2009
WHERE sales-2008.transactionID = p-backScratcher.transactionID
OR sales-2009.transactionID = p-backScratcher.transactionID
...but rather:
SELECT sales.date
FROM sales
WHERE sales.transactionID = p-backScratcher.transactionID
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
在 SQL 中，正如凯尔的回答所述，您可以创建一个视图，这是一种虚拟表，但我强烈建议您买一本书，或者谷歌， 关系数据库设计，然后再致力于数据库结构。
In SQL, as Kyle's answer states, you can create a View, which is a kind of Virtual table, but I would strongly recommend that you get a book, or google, Relational database design, before you commit yourself to a database structure.
这就是为什么您不应该为每个产品使用一张表并且每年使用一张表。
您需要的是一张“产品”表和一张“交易”表。
This is why you should not be using one table per product and one table per year.
What you need is one "Product" table and one "Transaction" table.
您正在寻找的内容称为“视图”，它几乎是一个存储的语句，它是格式正确的结果列表。可以像查询表一样直接查询。
What you're looking for is called a "View" which pretty much is a stored statement that is a list of properly formatted results. You can query it directly like it is a table.
不确定这个项目有多大或者您的具体要求是什么（例如，如果您有与之关联的代码），但您可能想考虑将数据迁移到 MS SQL（或 MySQL 等）。 SQL Server 有一个免费的桌面版本，但仅支持有限的功能集（其中包含视图）。
MySQL、PostGre 等支持您正在寻找的功能，包括 PostGre 中的继承表（不完全是您需要的，但类似）。
我有一段时间没有使用 Access，我不确定它是否支持您正在寻找的功能。
Not sure how big this project is or what your specific requirements are (for instance if you have code associated with it) but you might want to look into migrating your data to MS SQL (or MySQL, etc). SQL Server has a desktop edition which is free but supports only a limited feature set (views are included in that).
MySQL, PostGre, etc support the features you are looking for, including inherited tables in PostGre (not exactly what you need but similar).
I have not worked with Access for a while, and I am not sure it will support the features you are looking for.