mysql - 将两个查询输出合并为一个查询

Question

第一个查询

SELECT a.id attribute_id,a.attribute_name,
group_concat(distinct(pad.product_attribute_value) 
order by a.id) as group_name_values
FROM product_attributes pa
INNER JOIN product_attribute_details pad ON pad.product_attribute_id = pa.id
INNER JOIN products p ON p.id = pa.product_id 
  AND p.delete_status != 1 
  AND p.is_completed = 0 
  AND p.is_publish = 0 
  AND p.primary_category_id = 8
INNER JOIN attributes a ON a.id = pad.attribute_id
INNER JOIN attribute_values av ON av.attribute_id = a.id
INNER JOIN stores s ON s.id = p.store_id
  AND s.delete_status != 1 
  AND s.is_approved = 0 
  AND s.status = 0 
  AND s.id = 2
GROUP BY a.id

输出:

+--------------+------------------+---------------------+
| attribute_id | attribute_name   | group_name_values   |
+--------------+------------------+---------------------+
|     27       |   Color          |  Red,Blue           |
|     30       |   Screen Size    |  6 Inch,5 Inch      |
|     31       |   Primary Camera |  13 MP,5 MP,1.2 MP  |
+--------------+------------------+---------------------+

第二个查询

SELECT a.id as attribute_id,a.attribute_name,
group_concat(av.attribute_value order by a.id) as group_name_values
FROM category_attributes ca
LEFT JOIN store_categories sc ON sc.category_id = ca.id 
LEFT JOIN stores s ON s.id = sc.store_id AND s.id = 2 
LEFT JOIN attribute_group_relations agr ON agr.id = ca.attribute_group_relation_id
LEFT JOIN attributes a ON a.id = agr.attribute_id
LEFT JOIN attribute_values av ON av.attribute_id = a.id
WHERE ca.category_id = 8
GROUP BY a.id

输出:

+---------------+--------------------+-----------------------------+
| attribute_id  |   attribute_name   |   group_name_values         |
+---------------+--------------------+-----------------------------+
|     30        |  Screen Size       |    6 Inch,5 Inch,4.3 Inch   |
|     31        |  Primary Camera    |    10 MP,5 MP,13 MP         |
+---------------+--------------------+-----------------------------+

我想合并两个查询结果的输出

输出:

+---------------+--------------------+-----------------------------+
| attribute_id  |    attribute_name  |  group_name_values          | 
+---------------+--------------------+-----------------------------+  
|     30        |   Screen Size 6    |   Inch,5 Inch,4.3 Inch      |
|     31        |   Primary Camera   |   10 MP,5 MP,13 MP,1.2 MP   |
|     27        |   Color            |   Red,Blue                  |
+---------------+--------------------+-----------------------------+

Answer 1

可能有更好的方法来做到这一点，但是在将所有数据放在一起之后，您非常希望推迟分组，在获得所有数据之后，您可以使用 mysql 中的 UNION 语法来执行此操作将数据放在一起，然后您可以将其分组，我不知道这效果如何，因为我无法测试它，但请告诉我:

SELECT t_outer.attribute_id as attribute_id, t_outer.attribute_name, group_concat(t_outer.name_values) as group_name_values

FROM

((SELECT a.id attribute_id,a.attribute_name, distinct pad.product_attribute_value as name_values

FROM product_attributes pa

INNER JOIN product_attribute_details pad ON pad.product_attribute_id = pa.id

INNER JOIN products p ON p.id = pa.product_id AND p.delete_status != 1 AND p.is_completed = 0 AND p.is_publish = 0 AND p.primary_category_id = 8

INNER JOIN attributes a ON a.id = pad.attribute_id

INNER JOIN attribute_values av ON av.attribute_id = a.id

INNER JOIN stores s ON s.id = p.store_id AND s.delete_status != 1 AND s.is_approved = 0 AND s.status = 0 AND s.id = 2) t_1

UNION

(SELECT a.id as attribute_id,a.attribute_name, av.attribute_value as name_values

FROM category_attributes ca

LEFT JOIN store_categories sc ON sc.category_id = ca.id

LEFT JOIN stores s ON s.id = sc.store_id AND s.id = 2

LEFT JOIN attribute_group_relations agr ON agr.id = ca.attribute_group_relation_id

LEFT JOIN attributes a ON a.id = agr.attribute_id

LEFT JOIN attribute_values av ON av.attribute_id = a.id

WHERE ca.category_id = 8) t_2 ) t_outer

GROUP BY t_outer.attribute_id

ORDER BY t_outer.attribute_id