Question

Closed. This question needs details or clarity. It is not currently accepting answers.

想要改进这个问题吗? 添加细节,并通过

这是餐桌货和猫咪

Item	Key
Electronics	0
Smartphones	1
Laptops	1
Cameras	1
Headphones	1
Clothing	0
T-shirts	1
Jeans	1
Dresses	1
Jackets	1

Column Item has names of categories and goods with their respective 0 and 1 in column Key. It is required to fetch all categories and goods into two diferent columns like Goods and Categories. So, every goods-item must have its respective category-item in each row.

我试过做这个SQL

SELECT 
    CASE WHEN Key = 0 THEN Item ELSE NULL END AS Category,
    CASE WHEN Key = 1 THEN Item ELSE NULL END AS Goods
FROM goodsandcat;

查询的回音是 :

Category	Goods
Electronics	NULL
NULL	Smartphones
NULL	Laptops
NULL	Cameras
NULL	Headphones
Clothing	NULL
NULL	T-shirts
NULL	Jeans
NULL	Dresses
NULL	Jackets

但预计它会以相应的类别项目填写列类中的所有NULLs。因此,它必须像这样:

Category	Goods
Electronics	NULL
Electronics	Smartphones
Electronics	Laptops
Electronics	Cameras
Electronics	Headphones
Clothing	NULL
Clothing	T-shirts
Clothing	Jeans
Clothing	Dresses
Clothing	Jackets

如何写上上述结果的 sql- query fo?

Answer 1

电子产品与货物、上衣和服装物品之间没有任何关系,您可以将电子产品改为0,电子产品改为1类类似服装改为2类,服装产品改为3类,然后按编码修改您的标准查询。

Answer 2

同意上一个注释。这不是像一个电子表格, 您可以在此创建基于工作表中位置的关系。

相反,如果您为每行有一个密钥,您也许能找到一个可以将其分组的查询,然后构建您的查询。

我所做的就是将它放入电子表格然后创建查询/视图将东西插入 DB 中,比如:

Column A Contains the Items list, Column B contains the Keys then Column C contains this formula: =IF(B2=0,A2,C1) Column D contains this formula: =IF(B2=1,A2,"NULL") Then Column E contains this formulas: ="INSERT INTO Item_Table (Category, Goods) VALUES ( " & C2 & " , " & D2 & " );"

E列中的结果将是您可以粘贴到 DB 以创建您想要的表格的公式。

例如:

INSERT INTO goodsandcat (Category, Goods) VALUES ( Electronics ,  NULL );
INSERT INTO goodsandcat (Category, Goods) VALUES ( Electronics ,  Smartphones );

友情链接