我在MySQL中有三个表结构,如下所示:
+-----------------------+|Tables_in_resort_wear|+-----------------------+|类别||Categorysub||项目|+-----------------------+
- 表格类别:
+-------------+------------------+---------------+|ID_Category|Name_Category=Rank_Category|+-------------+------------------+---------------+|101|度假服应答器|1||102|巴厘岛服装|1||103 |大型度假村|3||104|bali批发|4||105|巴厘岛连衣裙|5||106 |巴厘岛咖啡馆|6|+-------------+------------------+---------------+
- 表类别子类
+----------------+-------------+------------------------+------------------+|ID_Categorysub|ID_CateCategory|Name_Categorysub|Rank_Categoriessub|+----------------+-------------+------------------------+------------------+|630|103|大号间苯二酚1|1||625|101|度假服应答器1|1||626|101|度假服应答器2|2||627|102|巴厘岛服装1|1||628|102|巴厘岛服装2|2||629|103|大号间苯二酚2|2||631|104|巴厘岛批发1|1||632|104|巴厘岛批发2|2||633|105|巴厘岛连衣裙1|1||634|105|巴厘岛连衣裙2|2||635|106|巴厘岛咖啡馆1|1||636|106|balicaftan 2|2|+----------------+-------------+------------------------+------------------+
- 表格项目
+----------+-------------+----------------+----------------------+----------------------------------------+|ID_Items|ID_Category|ID_CCategorySub|Code_Items| Name_Items|+----------+-------------+----------------+----------------------+----------------------------------------+|2519|101|625|jm4828长袍|长袍度假服||2520|101|625|jm4828长kaftan|juwita月亮长kaftan||2521|101|625|jm4828长款连衣裙|jm482热带度假服长款连衣裙||2522|101|625|jm4828度假服|jm4828juwita moon巴厘岛热带连衣裙||2523|101|626|jm4828长款kaftan|jm4828kaftan长款连衣裙巴厘岛||2524|101|626|jm4828kaftanbali|jm4828女性kaftanBali设计||2525|101|626|jm4828巴厘岛设计||2526|101|626|jm4828巴厘岛度假村穿着长裙||2527|102|627|jm44dm巴厘岛女性|jm44dm巴厘岛度假酒店穿长裙||2528|102|627|jm44dm巴厘岛女性|jm44dm度假服巴厘岛||2529|102|627|jm44dm巴厘岛连衣裙|jm44dm巴厘岛长裙设计||2530|102|627|jm44dm巴利连衣裙|jm44dm-女式巴利连衣裙||2531|102|628|jm44dm巴厘岛连衣裙|jm44dm女式长裙巴厘岛设计||2532|102|628|jm44dm巴厘岛长裙|jm44dm巴厘岛长裙设计||2533|102|628|jm44dm度假服|jm44dm-度假服长裙||2534|102|628|jm44dm巴厘岛||2535|103|630|jm18加大码巴利|juwitamoon女士加大码||2536|103|630|jm18加大码巴厘岛|juwita月亮女人加大码巴厘||2537|103|630|jm7002巴厘岛加大号|女式加大号度假服巴厘岛||2538 | 103 | 630 | jm7002大号bal |最佳大号caftan bal||2539|103|629|jm1333巴利加大码|juwita moon巴利加大码衬衫连衣裙||2540|103|629|jm44dm加大码|juwita moon加大码长裙||2541|103|629|jm18加大码巴厘岛||2542|103|629|jm7002加大码bal|juwita moon加大码女式咖啡|+----------+-------------+----------------+----------------------+----------------------------------------+
我想显示这3个表中的一些数据,为此我使用了JOIN。
这是我的问题:
SELECT items.id_items,
items.name_items,
category.name_category,
categorysub.id_categorysub,
categorysub.name_categorysub
FROM category
JOIN items
ON category.id_category = items.id_category
JOIN categorysub
ON category.id_category = categorysub.id_category
LIMIT 8
但是,在ID_Items中显示双重数据的结果如下:
+----------+----------------------------------------+------------------+----------------+--------------------+|ID_Items|Name_Items| Name_Category|ID_Categorysub | Name_Ccategorysub|+----------+----------------------------------------+------------------+----------------+--------------------+|2519|kaftan度假服长裙|度假服巴厘岛|625|度假服巴利岛1||2519|度假服长袍|度假服巴厘岛|626|度假服巴利2||2520|juwita moon long kaftan|resort wear bali|625|resort wear bali1||2520|juwita moon long kaftan|resort wear bali|626|resort wear bali2||2521|jm4828热带度假服长裙|度假服巴厘岛|625|度假服巴利岛1||2521|jm4828热带度假服长裙|度假服巴厘岛|626|度假服巴利2||2522|jm4828juwita月亮巴厘岛热带连衣裙|度假服巴厘岛|625|度假服巴利1||2522|jm4828juwita月亮巴厘岛热带连衣裙|度假服巴厘岛|626|度假服巴利2|+----------+----------------------------------------+------------------+----------------+--------------------+
我想显示一些类似的数据,但在ID_Items中没有双重数据。我尝试过DISTINCT,得到了与上面相同的数据。如何在我的情况下最好的方法?
首先加入category
和categorysub
,然后可以同时加入items
。
SELECT items.id_items,
items.name_items,
category.name_category,
categorysub.id_categorysub,
categorysub.name_categorysub
FROM category
JOIN categorysub
ON category.id_category = categorysub.id_category
JOIN items
ON category.id_category = items.id_category
AND categorysub.ID_Categorysub = items.ID_Categorysub
LIMIT 8
但这适用于有复合主键的情况。
对于您的数据,我假设ID_Categorysub
是唯一的,所以实际上您只需要:
JOIN items
ON categorysub.ID_Categorysub = items.ID_Categorysub
尝试通过使用组
SELECT items.id_items,
items.name_items,
category.name_category,
categorysub.id_categorysub,
categorysub.name_categorysub
FROM category
JOIN items
ON category.id_category = items.id_category
JOIN categorysub
ON category.id_category = categorysub.id_category
GROUP BY id_items