MySQL组& COUNT多个表 [英] MySQL GROUP & COUNT Multiple tables

问题描述

我有一个3部分的问题，这给我的麻烦我知道如何让表工作，如果我一次查询1表
，但我似乎不知道如何可以结合两个标签和 more_tags 表获得相同的结果。

我的3个主要问题列出如下。

问题1

表格中包含标签和 more_tags 。

问题2

我还希望能够显示

我还想计算标签在标签和 more_tags 表中出现的总次数。

MYSQL表

  SELECT`tags`.` tag_id`，`tags`.`tag_name`，COUNT（`tags`.`tag_name`）as'num'
 FROM`tags` 
 INNER JOIN`users`ON`tags`.`user_id` =`users`.`user_name` 
 WHERE`users`.`active` is NULL 
 GROUP BY`tags`.`tag_name`
 ORDER BY`tags`.`tag_name` ASC ; 
 
 
 SELECT`more_tags`.`tag_id`，`more_tags`.`tag_name`，COUNT（`more_tags`.`tag_name`）as'num'
 FROM` more_tags` 
 INNER JOIN`users'ON`more_tags`.`user_id` =`users`.`user_name` 
 WHERE`users`.`active` is NULL 
 GROUP BY`more_tags` .`tag_name` 
 ORDER BY`more_tags`.`tag_name` ASC; 
   
 
 
 
 所需输出 
  tag_id tag_name num 
 10 apple 12 
 192 pear 1 
 197 bored 1 
 203 sad 3 
 207广告2 
 217新闻1 
 190 bf 1 
 196 cape 1 
  
 
 
解决方案
问题1：
  SELECT tag_id，tag_name，count（*）
 FROM（
 SELECT tag_id，tag_name FROM tags 
 UNION ALL 
 SELECT tag_id，tag_name FROM more_tags 
）s 
 GROUP BY tag_id，tag_name 
  
问题2：
  SELECT tag_id，tag_name，'not in more tags'as description 
 FROM tags LEFT JOIN more_tags ON tags.tag_id = more_tags.tag_id 
 WHERE more_tags.tag_id IS NULL 
 UNION ALL 
 SELECT tag_id，tag_name，'not in in tags'as description 
 FROM tags RIGHT JOIN more_tags ON tags.tag_id = more_tags.tag_id 
 WHERE tags.tag_id IS NULL 
  
问题3：
  SELECT tag_id，tag_name ，COUNT（*）
 FROM tags INNER JOIN more_tags ON tags.tag_id = more_tags.tag_id 
 GROUP BY tag_id，tag_name 
  
 
I have a 3 part problem thats been giving me trouble I know how to get the tables to work if I query 1 table 
at a time but I can't seem to figure out how I can combine both the tags and more_tags tables to get the same results.

The 3 main problems I have are Listed below. 

PROBLEM 1

I want to be able to group the same tag from both the tags and more_tags tables. 

PROBLEM 2

I also want to be able to display the tags from each table that are not present in the other table.

PROBLEM 3

I also want to count the total amount of times the tag appears in both tags and more_tags tables.

MYSQL Tables
SELECT `tags`.`tag_id`, `tags`.`tag_name`, COUNT(`tags`.`tag_name`) as 'num' 
FROM `tags` 
INNER JOIN `users` ON `tags`.`user_id` = `users`.`user_id` 
WHERE `users`.`active` IS NULL
GROUP BY `tags`.`tag_name` 
ORDER BY `tags`.`tag_name` ASC";


SELECT `more_tags`.`tag_id`, `more_tags`.`tag_name`, COUNT(`more_tags`.`tag_name`) as 'num' 
FROM `more_tags` 
INNER JOIN `users` ON `more_tags`.`user_id` = `users`.`user_id` 
WHERE `users`.`active` IS NULL
GROUP BY `more_tags`.`tag_name` 
ORDER BY `more_tags`.`tag_name` ASC";             
Desired output  
tag_id  tag_name    num
10  apple   12
192     pear    1
197     bored   1
203     sad     3
207     ads     2
217     news    1
190     bf  1
196     cape    1

解决方案
Problem 1:
SELECT tag_id, tag_name, count(*)
FROM (
  SELECT tag_id, tag_name FROM tags
  UNION ALL
  SELECT tag_id, tag_name FROM more_tags
) s
GROUP BY tag_id, tag_name
Problem 2:
SELECT tag_id, tag_name, 'not present in more tags' as description
FROM tags LEFT JOIN more_tags ON tags.tag_id=more_tags.tag_id
WHERE more_tags.tag_id IS NULL
UNION ALL
SELECT tag_id, tag_name, 'not present in tags' as description
FROM tags RIGHT JOIN more_tags ON tags.tag_id=more_tags.tag_id
WHERE tags.tag_id IS NULL
Problem 3:
SELECT tag_id, tag_name, COUNT(*)
FROM tags INNER JOIN more_tags ON tags.tag_id=more_tags.tag_id
GROUP BY tag_id, tag_name


                        
这篇关于MySQL组& COUNT多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！