不同的关键字不能在mysql查询中工作

I have ran into a problem that my mysql query is not working as expected. I am joining 3 tables using union and from the result i want to ignore the repeating words. For that I have used DISTINCT keyword. But it is not working as expected.

我遇到了一个问题，我的mysql查询不像预期的那样工作。我使用union连接了3个表，因此我想忽略重复的单词。为此，我使用了不同的关键字。但它并没有像预期的那样发挥作用。

My query is,

我的查询,

SELECT DISTINCT(catnam), sub2id 
FROM tbl_first_page_products f INNER JOIN subcategory_level2 s 
ON f.brand_id = s.sub2id 
WHERE f.title_cat = 'men' 
UNION 
SELECT DISTINCT(catnam), sub2id 
FROM tbl_third_page_products f INNER JOIN subcategory_level2 s 
ON f.brand_id = s.sub2id 
WHERE f.title_cat = 'men' 
UNION 
SELECT DISTINCT(catnam), sub2id 
FROM tbl_fourth_page_products f INNER JOIN subcategory_level2 s 
ON f.brand_id = s.sub2id 
WHERE f.title_cat = 'men'

And my result is,

我的结果是,

catnam    sub2id
------    ------
Levi's      4
United      1
Reebok     130
Jack       18
Proline    77
Levi's     161
Arrow      284

In the above result Levi's is repeating. How to ignore this. I need only one Levi's. Is there any way to avoid this. I am stuck in here.

在上述结果中，李维斯正在重复。如何忽略这一点。我只需要一个Levi's。有没有办法避免这种情况。我被困在这里了。

2 个解决方案

#1

MySQL DISTINCT with multiple columns

MySQL不同于多列

You can use the DISTINCT clause with more than one column. In this case, MySQL uses the combination of all columns to determine the uniqueness of the row in the result set.

您可以使用不止一列的独立子句。在本例中，MySQL使用所有列的组合来确定结果集中行的惟一性。

http://www.mysqltutorial.org/mysql-distinct.aspx

if you want solve your problem, you can use group_concat() function then you group by catnam for all union queries :

如果您想解决问题，可以使用group_concat()函数，然后对所有union查询按catnam分组:

SELECT name ,GROUP_CONCAT(id)  
FROM your_table
GROUP BY name ;

so your query can be:

因此，您的查询可以是:

SELECT distinct(catnam) as catnam, GROUP_CONCAT(sub2id )  
FROM tbl_first_page_products f INNER JOIN subcategory_level2 s 
ON f.brand_id = s.sub2id 
WHERE f.title_cat = 'men'
UNION 
SELECT distinct(catnam) as catnam, GROUP_CONCAT(sub2id )
FROM tbl_third_page_products f INNER JOIN subcategory_level2 s 
ON f.brand_id = s.sub2id 
WHERE f.title_cat = 'men' 
UNION 
SELECT distinct(catnam) as catnam, GROUP_CONCAT(sub2id ) 
FROM tbl_fourth_page_products f INNER JOIN subcategory_level2 s 
ON f.brand_id = s.sub2id 
WHERE f.title_cat = 'men'
group by catnam

#2

I'd try:

我试一试:

SELECT n, GROUP_CONCAT(sub2id )
FROM
(
SELECT catnam n, sub2id 
...
UNION
SELECT catnam, sub2id
...
)
GROUP BY n

#1