在一列中添加多个值 [英] add multiple values in one column

问题描述

我必须按照下面显示的方式创建一个表.我们可以这样创建吗? (如果是)

I have to create a table in the way shown below. Can we create in this way? (If Yes)

表名称:示例

product_id|   product_name| category   |
    1     |   Sample1     |  1|2|3     |
    2     |   sample2     |  4|5|6     |

其中包含多个值的类别.

where category filed which contains multiple values.

&在表的哪一行中我们如何搜索类别4.

& how we can search for category 4 comes in which row of the table.

推荐答案

您无法创建嵌套表.而且您想到的并不是设计这样的桌子的好主意.您应该有两个表(恰好三个，如果类别是，则包含描述).一个用于product，第二个表包含每个产品的类别.示例设计如下所示，

You cannot create nested table. And the thing on your mind is not a good idea to design table like that. You should have two tables (exactly three which holds the description if the category). One is for the product and the second table holds the category for each product. Example design would look like this,

CREATE TABLE Product
(
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50) UNIQUE
);

CREATE TABLE Category
(
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(50) UNIQUE
);

CREATE TABLE Product_Category
(
    RecordD INT AUTO_INCREMENT PRIMARY KEY,
    CategoryID INT,
    ProductID INT,
    CONSTRAINT tb_uq UNIQUE(CategoryID, ProductID)
);

并填充样本记录

INSERT Category VALUES (1, 'Fruit');
INSERT Category VALUES (2, 'Vegetable');

INSERT Product VALUES (1, 'Apple');
INSERT Product VALUES (2, 'Banana');
INSERT Product VALUES (3, 'Cabbage');
INSERT Product VALUES (4, 'Squash');
INSERT Product VALUES (5, 'Tomato');

INSERT Product_Category (CategoryID, ProductID) VALUES (1,1);
INSERT Product_Category (CategoryID, ProductID) VALUES (1,2);
INSERT Product_Category (CategoryID, ProductID) VALUES (2,3);
INSERT Product_Category (CategoryID, ProductID) VALUES (2,4);
INSERT Product_Category (CategoryID, ProductID) VALUES (1,5);
INSERT Product_Category (CategoryID, ProductID) VALUES (2,5);

• SQLFiddle演示(包含记录)

示例查询

-- NORMAL QUERY
SELECT  a.ProductName, c.CategoryName
FROM    Product a
        INNER JOIN Product_category b
          ON a.ProductID = b.ProductID
        INNER JOIN Category c
          ON b.CategoryID = c.CategoryID
ORDER BY ProductName;

-- If you want catgoryName to be comma separated
SELECT  a.ProductName, GROUP_CONCAT(c.CategoryName) CategoryList
FROM    Product a
        INNER JOIN Product_category b
          ON a.ProductID = b.ProductID
        INNER JOIN Category c
          ON b.CategoryID = c.CategoryID
GROUP BY ProductName
ORDER BY ProductName;

这篇关于在一列中添加多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！