根据两个表的几种条件对出现次数进行计数 [英] Count ocurrences based on several conditions for two tables

问题描述

我有两个桌子.

表1:

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| ID         | varchar(255)| NO   | PRI | NULL    |       |
| Sex        | int(20)     | YES  |     | NULL    |       |
| Age        | varchar(255)| YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

表2:

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ID        | varchar(255) | NO   | PRI | NULL    |       |
| var1      | varchar(255) | YES  |     | NULL    |       |
| var2      | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

我想要做的是基于三个变量的条件，例如:

What I want to do, is, based on a condition of three of the variables, as for example:

• 性别= 1
• 年龄= 3(组)
• var1 =威斯康星州"

计算发生次数.也就是说，有此条件的人数.

count the number of ocurrences. That is, the number of persons with that conditions.

我发现的主要问题是第二张桌子的每个人都有不同数量的样本.因此，table2中的行数远大于number1中的行数.

The main problem that I´m finding is that the second table has a different amount of samples for each individual. So the number of rows in table2 is far bigger than in number1.

为清楚起见:

Table1
+------------+-------------+------+
| ID         | Sex         | Age  |
+------------+-------------+------+
| 1          | 1           | 2    |
| 2          | 0           | 4    | 
| 3          | 0           | 3    | 
+------------+-------------+------+

Table 2
+------------+-------------+---------+
| ID         | Var1        | Var2    |
+------------+-------------+---------+
| 1.1        | "Wisconsin" | var2_1  |
| 1.2        | "Wisconsin" | var2_2  | 
| 1.3        | "Wisconsin" | var2_3  | 
+------------+-------------+---------+

我猜想，首先需要根据表2的变量1预选个体，然后，我可以继续进行查询，但到目前为止，我还没有找到解决方法这样做.

I guess that firstly it is needed a preselection of the individuals based on var 1 for table 2, and then, I can carry on with the query for the ocurrences, but so far I didn´t manage to find a way of doing that.

任何帮助将不胜感激.

推荐答案

如果我正确地跟随了您，则可以使用 exists 对 table2 进行过滤:

If I follow you correctly, you can use exists to filter on table2:

select count(*) as cnt
from table1 t1
where t1.sex = 1 and t1.age = 3 and exists (
    select 1
    from table2 t2 
    where t2.id = t1.id and t2.var1 = 'Wisconsin'
)

此操作计算第一个表中的行，第二个表中的至少一个行具有威斯康星州.另一方面，如果您要确保第二张表中的所有行满足条件，则选项为:

This counts rows in the first table for which at least one row in the second table has Wisconsin. If, on the other hand, you want to ensure that all rows in the second table satisfy the condition, then an option is:

select count(*) as cnt
from table1 t1
inner join (
    select id
    from table2
    group by id
    having min(var1 <=> 'Wisconsin') = 1
) t2 on t2.id = t1.id
where t1.sex = 1 and t1.age = 3

这篇关于根据两个表的几种条件对出现次数进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！