如何将两个不同的表合并到一个表中? [英] How do I merge two different tables to a single table?

问题描述

我正在制作一个基于android的销售点系统，并且正在将两个表合并在一起，我希望将Table Foods和Table Drinks合并在Table Menu中.

I'm making an android-Base Point of Sale System and I'm straggling of combining two tables , I want The Table Foods and The Table Drinks are combined in the Table Menu.

public static final String DATABASE_NAME = "FOODs.db";
//FOODs Tables name
public static final String TABLE_FOODS = "table_Food";
public static final String TABLE_DRINKS = "table_drinks";
public static final String TABLE_MENU = "table_data";


//FOODs Table Columns name
private static final String COL_1 = "Primary ID";
private static final String COL_2 = "NAME";
private static final String COL_3 = "PRICE";

public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
    String CreateTableMains = "CREATE TABLE " +  TABLE_FOODS + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
            " NAME TEXT, PRICE TEXT)";
    db.execSQL(CreateTableMains);

    String CreateTableDrinks = "CREATE TABLE " +  TABLE_DRINKS + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
            " NAME TEXT, PRICE TEXT)";
    db.execSQL(CreateTableDrinks);

    String CreateTable = "CREATE TABLE " +  TABLE_MENU + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
            " NAME TEXT, PRICE TEXT)";
    db.execSQL(CreateTable);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_FOODS);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_DRINKS);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_MENU);

推荐答案

您可以使用 合并表> UNION -请参阅在SELECTS之间的大约3/4下 的复合选择语句

You can merge tables using UNION - see Compound Select Statements about 3/4 down between SELECTS

 SELECT * FROM table_Food UNION SELECT * FROM table_drinks

或

SELECT * FROM table_Food UNION ALL SELECT * FROM table_drinks

除非您碰巧喝了具有相同名称，相同价格和相同ID的饮料和食物，否则两种方法都可以使用相同的方法.没有ALL，重复的行将被省略.因此，UNION ALL更安全.

Both would work the same UNLESS you happened to have a drink and food that had the same name, the same price and the same id. Without ALL the duplicate rows would be omitted. As such UNION ALL is safer.

• 以上假设您需要所有列.
• 请注意，每个选择的列数必须匹配
  • 但您可以轻松解决例如 SELECT *，1作为FROM table_Food的额外列将新列添加到结果中
  • The above assumes that you want all columns.
  • Note the column counts from each select must match
    • but you can easily get around that e.g. SELECT *,1 as extracolumn FROM table_Food adds a new column to the result

    如果，您想要使用的只是一个表 table_data ，然后要将table_food和table_drinks表中的数据加载到table_Data中，可以使用:-

    IF what you want is to just use the one table table_data then to load the data from both the table_food AND the table_drinks table into the table_Data you could use :-

    INSERT INTO table_data (name,price) SELECT name,price FROM table_Food UNION ALL SELECT name,price FROM table_Drinks;
    

    如果

    • id的值相同，则将其省略作为主键，然后由于UNIQUE约束冲突而导致上述操作失败.

      • id's are omitted as being a Primary Key if the values were the same then the above would fail due to a UNIQUE constraint conflict.

        由于id列具有INTEGER PRIMARY KEY，因此很可能会有重复的id.这使该列成为rowid列(隐藏列)的别名，该列具有以下属性:生成的值最初对于表是唯一的，然后是1、2、3(但不能保证).AUTOINCREMENT关键字增加了一个额外的含义，即唯一值必须大于存在或已使用的唯一值.

        There will very likely be duplicate id's as you have INTEGER PRIMARY KEY for the id column. This makes the column an alias of the rowid column (a hidden column) which has the attribute of generating a value unique to the table initially 1, then 2 then 3 (but not guaranteed). The AUTOINCREMENT keyword adds an additional twist in that the unique value must be greater then any that exist or have been used.

        如果要临时合并所有3个表，则可以使用:-

        If you want to temporarily merge all 3 tables then you could use:-

        SELECT * FROM table_Food UNION ALL SELECT * FROM table_drinks UNION ALL SELECT * FROM table_Data;
        

        如果您想合并所有三个表，并知道可以使用哪个表:-

        If you wanted to merge all three and know what came from which table you could use :-

        SELECT *,'Food' FROM table_Food UNION ALL SELECT *,'Drinks' FROM table_drinks UNION ALL SELECT *,'Menu' FROM table_Data;
        

        上述SQL Fiddle演示

        这篇关于如何将两个不同的表合并到一个表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！