问题描述
我对大家有一个简单的问题.
我有一张这样的桌子:
ID 1 1 2 3 4 4 5 6
吉姆·约翰·尼克·乔治·保罗·史蒂芬·凯利·杰克的名字
代码14 26 26 14 14 26 14 26
我想从上面的一个创建3个表.一种仅包含代码14的ID与代码26不同的ID.一种仅包含代码26的ID与代码14不同的ID.另一种包含代码14和ID的ID.代码26.更准确地说，我想要这些表:
ID 3 5
名字乔治·凯利
CODE 14 14
ID 2 6
尼克·杰克
代码26 26
ID 1 1 4 4
吉姆·约翰·保罗·斯蒂芬(JIM JOHN PAUL STEPHEN)的名字
CODE 14 26 14 26
HI there, i have a simple question for you all.
I have a table like this one:
ID 1 1 2 3 4 4 5 6
NAME JIM JOHN NICK GEORGE PAUL STEPHEN KELLY JACK
CODE 14 26 26 14 14 26 14 26
I want to create 3 tables from the one above. One that includes only the IDs with code 14 that do not have the same id with code 26. One that includes only the IDs with code 26 that do not have the same id with code 14. And one that includes the IDs with code 14 and code 26. To be more precise i want these tables:
ID 3 5
NAME GEORGE KELLY
CODE 14 14
ID 2 6
NAME NICK JACK
CODE 26 26
ID 1 1 4 4
NAME JIM JOHN PAUL STEPHEN
CODE 14 26 14 26
Thnx in advance for any help guys!
推荐答案
INSERT INTO A (id, name, code) FROM SELECT id, name, code FROM Table1 WHERE Id IN (3, 5)
INSERT INTO B (id, name, code) FROM SELECT id, name, code FROM Table1 WHERE Id IN (2, 6)
etc...
我认为在分离数据之后代码可能会丢失，因为新表将增加其含义.更清楚地说:移至表A的所有记录均隐含代码14，表B 26和表C中的记录具有14 en26.
填充前两个表很容易，但是由于有些记录同时具有代码14和26，因此您需要先将它们弄清楚.要选择您需要的每个代码为14和26的记录，请按id将它们分组，然后仅保留计数为2的记录(因为这意味着它同时具有代码14和26)
选择将变成这样:
I assume the code can be lost after the data is separated because the new table will add the meaning of that. To be more clear: All records that are moved to table A are implied to have code 14, records in table B 26 and table C have 14 en 26.
To fill the first two tables would be easy but because there are records that have both code 14 and 26 you need to get them out of the way first. To select those you need every record with code 14 and 26, group them by id and then only keep records that has a count of two (because that means it has both code 14 and 26)
The select would become something like this:
SELECT id, name FROM Table1
WHERE code in (14, 26)
GROUP BY id
HAVING COUNT(id) = 2;
您需要将这些记录插入表C中，然后将其从Table1中删除(或任何表名).之后，您只需要将代码14或26与记录分开即可.
祝你好运！
You need to insert those records into table C and after that delete them from Table1 (or whatever the name of the table is). After that you only need to separate the records with either code 14 or 26 from it.
Good luck!
这篇关于ms访问中的表分隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！