我有两个表。
表1:tA
+----+--------------------+
| id | url |
+----+--------------------+
| 1 | hxxp://www.aaa.com |
| 2 | hxxp://www.bbb.com |
+----+--------------------+
表2:tB
+----+-------+--------+----------+
|id | tA_id | cat_id | cat_name |
+----+-------+--------+----------+
| 1 | 1 | 1 | Mobile |
| 2 | 1 | 2 | Other |
| 3 | 2 | 2 | Other |
+----+-------+--------+----------+
预期输出
+----+-------+--------+----------+
| id | tA_id | cat_id | cat_name |
+----+-------+--------+----------+
| 1 | 1 | 1 | Mobile |
| 3 | 2 | 2 | Other |
+----+-------+--------+----------+
mysql代码:
select *
from tA
inner tB
on tA.id = tB.tA_id
where tB.cat_id = 1
or tB.cat_id = 2
Group By tA_id
我的mysql代码的结果:
+----+-------+--------+----------+
| id | tA_id | cat_id | cat_name |
+----+-------+--------+----------+
| 1 | 1 | 2 | Other |
| 3 | 2 | 2 | Other |
+----+-------+--------+----------+
我该怎么办?
按tA_id删除您的组,因为它正在将cat_name Mobile和Other分组在一起。
如果你需要保留它,请使用ORDER BY tB.cat_id ASC
,我相信它会像你需要的那样显示cat_id 1和2
查询中出现错误。。。缺少JOIN
。。。
查询
SELECT *
FROM tA
INNER JOIN tB
ON tA.id = tB.tA_id
WHERE tB.cat_id = 1
OR tB.cat_id = 2
GROUP BY tA_id
这是提取预期结果
+----+---------------------+-----+--------+--------+----------+
| id | url | id | tA_id | cat_id | cat_name |
+----+---------------------+-----+--------+--------+----------+
| 1 | hxxp://www.aaa.com | 1 | 1 | 1 | mobile |
| 2 | hxxp://www.bbb.com | 3 | 2 | 2 | other |
+----+---------------------+-----+--------+--------+----------+
试试这个:-
select *
from tA
inner join tB
on tA.id = tB.tA_id
where tB.cat_id = 1
or tB.cat_id = 2
group by tb.cat_name;
希望它能帮助你。