根据两个表的几个条件计算出现次数

2022-01-23 00:00:00 sql count subquery mysql inner-join

我有两张桌子.

表 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  | 
+------------+-------------+---------+

我想首先需要根据 var 1 为表 2 预选个人，然后我可以继续查询出现的情况，但到目前为止我还没有找到方法这样做.

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

相关文章