通行证结果一个查询到另一个查询，然后合并这两个查询的结果

Question

我有以下查询：

SELECT name as String 
    , COUNT(*) Total 
    , SUM(product = 1) Product 
      FROM String 
       WHERE language = 1 
      GROUP 
       BY name 
      ORDER 
       BY total desc 
      LIMIT 10 

此查询的目的是根据其在String发生报告字符串的TOP-10表格，加上报告它们在特定产品中的出现（id = 1）。

我想另一列添加到结果，这将统计每个字符串在另一个字符串中出现的次数。为此，我有一个表格Copy，它被全文索引索引。

我的问题是要弄清楚如何从结果传递String列的值到这个查询：

SELECT COUNT(name) as inAll 
    FROM Copy c 
     WHERE MATCH(c.name) AGAINST (/*String*/) 

是否有可能只是通过一个SQL查询来实现这个结果？

Before:     After: 

String Total Product  String Total Product inAll 
+-----+-----+------+  +-----+-----+-------+-----+ 
|blah | 52 | 12 |  |blah | 52 | 12 | 96 | 
|bleh | 23 | 14 |  |bleh | 23 | 14 | 56 | 
|bloh | 14 | 11 |  |bloh | 14 | 11 | 34 | 
+-----+-----+------+  +-----+-----+-------+-----+ 

谢谢。

编辑

我的架构：

CREATE TABLE Language (
    `id` INT NOT NULL AUTO_INCREMENT, 
    `name` VARCHAR(30) NOT NULL, 
    `code` VARCHAR(10) NOT NULL, 
    PRIMARY KEY (`id`) 
) ENGINE=InnoDB CHARSET=utf8 COLLATE utf8_unicode_ci; 

CREATE TABLE Product (
    `id` INT NOT NULL AUTO_INCREMENT, 
    `name` VARCHAR(100) NOT NULL, 
    PRIMARY KEY (`id`) 
) ENGINE=InnoDB CHARSET=utf8 COLLATE utf8_unicode_ci; 

CREATE TABLE String (
    `id` INT NOT NULL AUTO_INCREMENT, 
    `label` VARCHAR(200), 
    `name` TEXT(500) NOT NULL, 
    `language` INT NOT NULL, 
    `product` INT NOT NULL, 
    PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci; 

CREATE TABLE Copy (
    `id` INT NOT NULL, 
    `name` TEXT(500) NOT NULL, 
    PRIMARY KEY (`id`), 
    FULLTEXT(name) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci; 

另外INSERT，DELETE，UPDATE触发器。

Answer 1

您可以尝试加入String和Copy表：

SELECT COUNT(*) AS inAll, t.String AS String, t.Total AS Total, t.Product AS Product 
FROM Copy c 
INNER JOIN 
(
    SELECT name AS String, COUNT(*) Total, SUM(product = 1) Product 
    FROM String 
    WHERE language = 1 
    GROUP BY name 
    ORDER BY COUNT(*) desc 
    LIMIT 10 
) t 
ON c.name = t.name