当前位置：文江博客话题详情

一个简单的数据库版本控制

发布于 2024-10-02 10:52:45 字数 101 浏览 1 评论 0原文

如果您有一个存储问题和答案的表，例如使用名为“Parent”之类的额外列。因此，如果一个问题在父级中没有值，那么它就是一个问题，如果有，那么它就是一个答复，那么您将如何对此进行版本控制呢？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浪漫人生路 2024-10-09 10:52:45

有关如何保留文本过去修订版本的完善示例，您可以查看 MediaWiki 数据库布局。这是 MediaWiki 软件使用的数据库布局，许多 wiki（尤其是维基百科）都在该软件上运行。具体来说，page、revision 和 text 表是涉及跟踪历史更改的主要表。

回复收藏 0 原文

阳光的暖冬 2024-10-09 10:52:45

有一个表用于问题，另一个表用于答案：

这是 SQLite 中的一个示例

问题表应包含问题的文本和要连接的 id 字段：

sqlite3
sqlite> CREATE TABLE questions(id, question);
sqlite> INSERT INTO questions VALUES(1,"What is your name");
sqlite> INSERT INTO questions VALUES(2,"Phone Number");
sqlite> INSERT INTO questions VALUES(3,"name of dog")
sqlite> SELECT * FROM questions;
1|What is your name
2|Phone Number

答案应包含修订号或日期、问题的文本答案，一个字段，用于指示向哪个用户询问该问题（如果需要），以及作为答案的问题的 ID。

sqlite> CREATE TABLE answers(question,userid,revision,answer);
sqlite> INSERT INTO answers VALUES(1,100,1,"Alex");
sqlite> INSERT INTO answers VALUES(2,100,1,"1234");
sqlite> INSERT INTO answers VALUES(2,100,2,"5678");
sqlite> SELECT * from answers;
1|100|1|Alex
2|100|1|1234
2|100|2|5678

现在查询两个数据库，确保：仅选择最新版本（GROUP 和 MAX），并且包含未回答的问题（LEFT JOIN）

sqlite> SELECT questions.question, answers.answer, userid, max(revision) 
                FROM questions LEFT JOIN answers ON questions.id = answers.question
                GROUP BY questions.id, userid;
What is your name|Alex|100|1
Phone Number|5678|100|2
name of dog|||

您可以创建一些其他查询来列出给定问题/用户的过去答案，并列出用户未回答的问题。

Have one table for questions, and a separate one for answers:

Here's an example in SQLite

The questions table should contain the text of the question and an id field to join on:

sqlite3
sqlite> CREATE TABLE questions(id, question);
sqlite> INSERT INTO questions VALUES(1,"What is your name");
sqlite> INSERT INTO questions VALUES(2,"Phone Number");
sqlite> INSERT INTO questions VALUES(3,"name of dog")
sqlite> SELECT * FROM questions;
1|What is your name
2|Phone Number

The answers should contain a revision number or date, the text of the answer, a field to indicate which user is asked the question (if necessary), and the id of the question to which it is an answer.

sqlite> CREATE TABLE answers(question,userid,revision,answer);
sqlite> INSERT INTO answers VALUES(1,100,1,"Alex");
sqlite> INSERT INTO answers VALUES(2,100,1,"1234");
sqlite> INSERT INTO answers VALUES(2,100,2,"5678");
sqlite> SELECT * from answers;
1|100|1|Alex
2|100|1|1234
2|100|2|5678

Now query on the two databases, making sure that: only the most recent revision is selected (GROUP and MAX), and unanswered questions are included (LEFT JOIN)

sqlite> SELECT questions.question, answers.answer, userid, max(revision) 
                FROM questions LEFT JOIN answers ON questions.id = answers.question
                GROUP BY questions.id, userid;
What is your name|Alex|100|1
Phone Number|5678|100|2
name of dog|||

You can create some other queries to list past answers for a given question/user, and to list unanswered questions by user.

回复收藏 0 原文

~没有更多了~