Question

我想建立一个新桌,其特性是老板,没有重复。我想做的是:

CREATE TABLE New_Users  LIKE Old_Users, 
AS
(SELECT * FROM Old_Users GROUP BY ID) ;

但是,上述情况并不可行。任何人都能够修改它,使之发挥作用?

Answer 1

你的尝试就是这样坏的。页: 1

http://dev.mysql.com/doc/refman/5.1/en/create-table.html” 它说:

Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table.

您:

CREATE TABLE New_Users  LIKE Old_Users;

之后插入

INSERT INTO New_Users SELECT * FROM Old_Users GROUP BY ID;

但你不能在一次发言中这样做。

Answer 2

http://dev.mysql.com/doc/refman/5.0/en/create-table-select.html

什么是:

Create Table New_Users Select * from Old_Users Where 1=2;

并且,如果这种工作不奏效,则在创造后仅选择一行和一行:

Create table New_Users select * from Old_Users Limit 1;
Truncate Table New_Users;

EDIT:

I noticed your comment below about needing indexes, etc. Try:

show create table old_users;
#copy the output ddl statement into a text editor and change the table name to new_users
#run the new query
insert into new_users(id,name...) select id,name,... form old_users group by id;

这样做。似乎你正在这样做,以清除重复? 在此情况下,你可能希望就补贴问题制定独特的指数。如果它是一个主要的关键,就应当做到。你们也可以:

#make primary key
alter table new_users add primary key (id);
#make unique
create unique index idx_new_users_id_uniq on new_users (id);

Answer 3

For MySQL, you can do it like this:

CREATE TABLE New_Users   SELECT * FROM Old_Users group by ID;

Answer 4

CREATE TABLE new_table LIKE age_table;

或u 可以使用

CREATE TABLE new_table as SELECT * FROM old_table WHERE 1 GROUP BY [column to remove duplicates by];

Answer 5

您可使用以下表格,并在表格中插入不同的价值观:

Select Distinct Column1, Column2, Column3 into New_Users from Old_Users

友情链接