Question

在我的客户电子邮件给我一个有50个数据栏的外壳体中,我处于这种状态。然后,我将其出口到特别安全局,上载到MySQL——单桌。该栏为不同成分(每个成分10列数据——标题、类别等)和每个成分的40个不同栏目。因此,表格中每个成分都有这50列,尽管每个栏目都对这一成分适用。

我的问题是,我能否创建一种结构,只选择一个选定成分的特性,并排除其他所有栏目?

(我知道,另一个选择是建立我自己的特别志愿人员组织,设立多个表格,然后为它们撰写文件,但我大麻调查首先解决了这一问题。) 如果无法做到这一点,我就不得不面对这一点,并建造一个教区。

就我而言,这并不完全排除(或包含“nei”的)未填满的栏目。

SELECT 
IF(`Heving-vanlig-gjaerbakst` <>    AND `Heving-vanlig-gjaerbakst` <>  nei , `Heving-vanlig-gjaerbakst`,  random ) AS `test1`,
IF(`Frys-kort` <>    AND `Frys-kort` <>  nei , `Frys-kort`,  random ) AS `test2`
... and for the 38 other rows ...
FROM x
WHERE id = 123

而在PHP-code中,我没有通过跳出空头来解决这一问题。

例行(首栏):

g1      gruppe              ug1         undergruppe             artnr   artikkel                beskrivelse                                             status  enhet   ansvar      prisliste   Heving-vanlig-gjaerbakst    Heving-soete-deiger Deig-stabilitet Smaksgiver  Saftighet   Krumme-poring   Skorpe  Volum   Konservering    Skjaerbarhet    Frys-lang   Frys-kort   Kjoel   Holdbarhet  E-fri   Azo-fri Mandler Aprikoskjerner  Helmiks Halvmiks    Base    Konsentrat  Utstrykning Bakefasthet Frukt-Baerinnhold   Slippegenskaper Hindre-koksing  Palmefri    Fritering   Smidighet   Baking  Kreming Roere   Fylning Dekor   Prefert Viskositet  Cacaoinnhold    Fet-innhold
100150  Bakehjelpemidler    100150200   Fiber/potetprodukter    10085   Potetflakes sekk 15 kg  Egnet til lomper, lefser, brød og annet bakverk. B...   Handel  Sekk    Trond Olsen JA          xxx                         xxx                 xxx

各位可以看到,大多数栏目是空洞的。第十、二十和三十是职等制度的一种形式,但在某些栏中,内容是“是”或“否”。

正如我说过的那样,头10列是关于这一产品的信息,其他40个是不同的特征(它使那些为某种产品工作的大麻)。

Answer 1

缩略语与你希望将表格转换成两个表格一样:

CREATE TABLE Ingredients
(
    g1           ...,
    gruppe       ...,
    ug1          ...,
    undergruppe  ...,
    artnr        ... PRIMARY KEY,
    artikkel     ...,
    beskrivelse  ...,
    status       ...,
    enhet        ...,
    ansvar       ...,
    prisliste    ...
);

I ve opted to guess that the artnr is the primary key, but adapt what follows to the actual primary key. This table contains the eleven (though your question said ten) columns that are common to all ingredients. You then have another table which contains:

CREATE TABLE IngredientProperties
(
    artnr       ... NOT NULL REFERENCES Ingredients,
    property    VARCHAR(32) NOT NULL,
    value       VARCHAR(3)  NOT NULL,
    PRIMARY KEY(artnr, property)
);

You can then load the populated columns from your original table into these two. At worst, there d be 40 entries in IngredientProperties for one entry in Ingredient. You might make property into a foreign key reference to a defining list of possible ingredient properties (a third table that defines the possible values for the properties - basically, a record of the column names from your original table). If you add the third table, it might logically be called IngredientProperties (too), in which case the table I called IngredientProperties needs to be renamed.

届时,你们可以加入兴旺剂和兴奋剂,获得你们想要的信息。

I m not sure that I recommend this solution; it is basically a use of the Entity Attribute Value approach to database design. However, for extremely sparse information like you seem to have, and when used with the constraint of the third table.

你可以明智地做的是,处理所有可能合并的40个栏目,因为这一编号与栏数成正数的增长(与N=40相比,其数量相对较大)。

友情链接