mysql - 在 SQL 中动态选取一定数量的数据组

Question

我有两张 table 。右边是一个查找表，左边是我遇到问题的表。 num 是主键，WordID 是引用 num 的外键。 Pri 是代表单词优先级的数字(编号最低的单词排在最前面)，ID 代表单词组。对于我的示例，我有以下内容......

第一组:学校奶油球第三组:奶油球店第22组:门屋第5组:球第7组:汽车

问题来了。

我希望我的应用程序能够让我有机会添加一个新单词集，其中可以包含新的和/或现有的单词，但一次只能包含一个单词。

例如，如果我首先选择单词“cream”，则现有的两个组中都会包含该单词，但我无法确定这些单词是否有额外的单词以及有多少额外的单词。

如果单词组相同且顺序相同，我正在尝试找出如何解决这个问题，而不必每次都插入新记录。

我正在尝试以编程方式使此设计尽可能高效，而不浪费磁盘空间。

所以基本上我想做的是创建一组单词并在添加或删除部分单词时更新数据库，这样如果服务器崩溃，至少可以保存部分数据。

有什么想法吗？

<小时/>

更新:我添加了一个新示例和代码来更好地说明我的问题。

drop table if exists words;drop table if exists word;drop table if exists name;
create table word(ID Int primary key not null auto_increment, Word varchar(255));
create table name(ID int, Obj int);
create table words(Seq  Int primary key not null auto_increment,NameID Int,WordID Int,foreign key (WordID) references word(ID));

insert into word(Word) values("great"),("pink"),("job"),("ribbon"),("nice"),("red"),("tie"),("bow"); # make up words

# add group 1 = great job (words 1 and 3 respectively).
insert into words(NameID,WordID) values(1,1),(1,3);
# add group 2 = nice job (words 5 and 3 respectively).
insert into words(NameID,WordID) values(2,5),(2,3);
# add group 3 = nice tie (words 5 and 7 respectively).
insert into words(NameID,WordID) values(3,5),(3,7);
# add group 4 = bow tie (words 8 and 7 respectively).
insert into words(NameID,WordID) values(4,8),(4,7);
# add group 5 = nice bow tie (words 5, 8 and 7 respectively).
insert into words(NameID,WordID) values(5,5),(5,8),(5,7);
# add group 6 = nice bow (words 5 and 8 respectively).
insert into words(NameID,WordID) values(6,5),(6,8);

# define three wordsets for object #1 and three for object #2 and two for object #3, and a brand new set for object #4.
insert into name(ID,Obj) values(1,1),(2,1),(3,1),(1,2),(4,2),(5,2),(6,3),(5,3),(7,4);

#object 1 has these sets: great job, nice job, nice tie
#object 2 has these sets: great job, bow tie, nice bow tie
#object 3 has these sets: nice bow tie, nice bow
#object 4 has word set 7 which we are about to define.

# At this point, data is ok. Problem starts when new data begins
# So I look for a key (NameID) that hasnt been used yet. in this case, its 7.


# Lets say my application displays each word from the lookup table in its own button on the screen where I can 
# select whatever word I want to add, and on each selection, the button lights up and the database updates.
# if i click the same word again, it is removed and the database is updated.

# so I want to add the word "nice" to set 7 like so...

insert into words(NameID,WordID) values(7,5);

# I can keep that wordset as-is because there is no other instance of that word set. (the set containing only the word "nice"). 
# Lets say I click the "bow" button to add the word "bow" after "nice" to set 7 as well.

insert into words(NameID,WordID) values(7,8);

# now Heres the problem. I have just created the word set nice bow. This also matches word group 6.
# If I keep telling SQL to define objects as the words "nice bow" the way I do it,
# I will have duplicate results, and waste disk space. I will add a few more "nice bow"s to show.

insert into words(NameID,WordID) values(8,5),(8,8);
insert into words(NameID,WordID) values(9,5),(9,8);
insert into words(NameID,WordID) values(10,5),(10,8);

# Running "select * from words" will show the table with the last 5 sets of words being exactly the same. The seq value is only used 
# to determine order. the lower the sequence number, the more closer to the beginning the word is for that group.

我想以某种方式将最后四个条目转换为一个，以消除应用程序中每次单击按钮时的冗余。

有人知道我做错了什么吗？我知道我需要使用 SELECT，并且我想我可能需要使用 COUNT、HAVING、GROUP 等等，但我很困惑

Answer 1

我对你这个冗长的问题有点不清楚。尽管如此，这仍然是您在 MySQL 中实现随机选择的方法。

SELECT A, B, C, D FROM TABLE 
ORDER BY RAND()

OR

SELECT A, B, C, D FROM TABLE,
(SELECT RAND()*A AS A FROM TABLE) AS TMP
WHERE 
TABLE.A = TMP.A

我建议您尝试这两种方法。我还没有尝试过其中任何一个。尝试在您的场景中实现这一点。