mysql从两个相同的表中获取结果 [英] mysql getting result from two identical table
问题描述
我有两个相同的表。我想比较这两个表并从中获取结果。条件为：
I have two identical tables. I want to compare these two tables and getting the result from them. The condition are:
-
表1中的每个记录按TID分组将与表中的所有记录进行比较TABLE2 按其每个TID分组。
each record in TABLE1 grouped by TID will be compared to all records in TABLE2 grouped by their each TID.
如果要在TABLE1中发现每个分组记录TABLE2（TABLE2中的记录也按每个tid分组），多达N（N是用户输入变量），则该记录将插入到新表中。
if each grouped record in TABLE1 are to be discovered in TABLE2 (records in TABLE2 that grouped by each tid, too), as many as N (N is the user input variable), then that record will be inserted into new table.
例如，与下面的ss一样，按 TID 2 分组的 ITEM CFA 在表2中出现了3次，因此它们将会插入到新表中：
For example, like the ss below, ITEM C-F-A grouped by TID 2 has 3 occurrences in table2, thus they will be inserted into new table:
我已经尝试为此编写代码，并且它可以正常工作（vb.net），但是编译器花了可笑的时间才能完成。主要原因是我正在处理一个巨大的数据库。
I've already tried writing the code for this and it worked (vb.net), but the compiler takes ridiculous time to complete. The main cause is I'm processing a huge database.
我在程序中完成的方法是将两个表填充为2d数组。在将两个元素与 if子句比较时，为数组赋值。
The method I've done in program is populate the two table into 2d array. assigning value to array while comparing the two element with if clause.
下面是我拥有的二维数组创建：
Below is the 2d array that I've created:
但是这种方法确实很昂贵，我在上图上的真实数据库是1st 2d数组有2k条记录，2nd 2d数组有800条记录，而当我尝试计算估计完成的估计时间，它显示了一个奇妙的数字，大约16小时..天哪！
But this method is really expensive, my real database on pic above is 1st 2d array has 2k records and 2nd 2d array has 800 records, and when I try to calculate the estimate time for compiling to completed, it showed a fantastic number, about 16 hours.. gosh!!
用mysql查询，
或比我做的更有效的其他方法解决了吗？
So I was wondering, whether this problem can be solved with mysql query, or other method that is more effective than what I have done?
推荐答案
好，这是
方法如下：
- 您需要表1中的TID列表。因此，您将构建一个独特的列表（最内部的查询）。
- 从table2中进行选择时，您可以在where子句中使用该列表，以便仅获取table1中具有TID的行。您可以对该查询进行分组，然后使用HAVING将行限制为仅包含那些计数> X的行。
- 现在，您将拥有一个与table1中的TID匹配并且超过X的TID列表表2中的条目。您可以选择这些行。
- 那些被用作表1中插入语句的源。
- You need a list of TID's from table1. So you build a distinct list (inner most query).
- You use that list in a where clause when selecting from table2, so that you only get rows that have TIDs in table1. You group that query, and use HAVING to then limit the rows to only those with a count > X.
- Now you have a list of TIDs that match those in table1 and have more than X entries in table2. You select those rows.
- Those are used a the source of an insert statement into table1.
SQL可能类似于：
insert into table1
values (select * from table2 where tid in
(select tid, count(*) as cnt
from table2
where tid in (select distinct tid from table1)
group by tid
having cnt > 10)));
我怀疑语法是否正确（不能记住select中插入的确切语法），以及没有声称它会立即起作用，但是如果我想在一个查询中全部完成操作，那将是我的第一枪。
I doubt the syntax is correct (cant remember the exact syntax for an insert from a select), and make no claim it will work off the bat, but its what my first shot would be if I wanted to do it all in one query.
这篇关于mysql从两个相同的表中获取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
