433
社区成员
发帖
与我相关
我的任务
分享mysql – 在2个与IN()子句合并的3-Tables关系中的SELECT和每个关系的COUNT
在3-Tables关系查询中,我为每个关系提供以下两个查询：
http://sqlfiddle.com/#!9/0bcc34/3/0
SELECT d.`id`, COUNT(da.`doc_id`)
FROM `docs` d
LEFT JOIN `docs_scod_a` da ON da.`doc_id` = d.`id`
LEFT JOIN `scod_a` a ON a.id = da.`scod_a_id`
WHERE a.`ver_a` IN ('AA', 'AB')
GROUP BY d.`id`;
| id | COUNT(da.`doc_id`) |
|----|--------------------|
| 1 | 2 |
| 2 | 1 |
| 3 | 2 |
http://sqlfiddle.com/#!9/d89a4e/1/0
SELECT d.`id`, COUNT(db.`doc_id`)
FROM `docs` d
LEFT JOIN `docs_scod_b` db ON db.`doc_id` = d.`id`
LEFT JOIN `scod_b` b ON b.id = db.`scod_b_id`
WHERE b.`ver_b` IN ('BA', 'BB')
GROUP BY d.`id`;
| id | COUNT(db.`doc_id`) |
|----|--------------------|
| 1 | 2 |
| 2 | 1 |
| 3 | 2 |
我想要做的是将两个查询合并为单个查询不仅仅使用(3-Tables)x2,所以我尝试了以下查询：
http://sqlfiddle.com/#!9/1d2954/2/0
SELECT d.`id`, COUNT(da.`doc_id`), COUNT(db.`doc_id`)
FROM `docs` d
LEFT JOIN `docs_scod_a` da ON da.`doc_id` = d.`id`
LEFT JOIN `scod_a` a ON a.id = da.`scod_a_id`
LEFT JOIN `docs_scod_b` db ON db.`doc_id` = d.`id`
LEFT JOIN `scod_b` b ON b.`id` = db.`scod_b_id`
WHERE a.ver_a IN ('AC', 'AB') AND b.ver_b IN ('BA', 'BB')
GROUP BY d.`id`;
我期望从此查询得到的结果如下：
| id | COUNT(da.`doc_id`) | COUNT(db.`doc_id`) |
|----|--------------------|--------------------|
| 1 | 2 | 2 |
| 2 | 1 | 1 |
| 3 | 2 | 2 |
但不是我得到的
| id | COUNT(da.`doc_id`) | COUNT(db.`doc_id`) |
|----|--------------------|--------------------|
| 1 | 4 | 4 |
| 3 | 2 | 2 |
Problm仅在COUNT上,还是我使用错误的方法从3-Tables Relation中选择数据？
那么在Berif：如何从3-Tables Relation中选择数据,并将它们合并在一起,然后COUNT结果为每个关系,
最后我将每行的计数汇总为一,所以最后的结果将在所有这一切之后得到预期.
| id | SUM(COUNT(da.`doc_id`) + COUNT(db.`doc_id`))|
|----|---------------------------------------------|
| 1 | 4 |
| 2 | 2 |
| 3 | 4 |
...全文
请发表友善的回复…
发表回复
weixin_38090265 2019-09-12
- 打赏
- 举报
你正在增加联接,你得到你所要求的.标量选择可能是您想要的：
select X.id, X.sda + X.sdb sumsummerdesum from (
SELECT d.`id` id,
(select COUNT(da.`doc_id`)
from `docs_scod_a` da, `scod_a` a
where da.`doc_id` = d.`id`
and a.id = da.`doc_id`
and a.ver_a in ( 'AA', 'AC')
) sda,
(select COUNT(db.`doc_id`)
from `docs_scod_b` db, `scod_b` b
where db.`doc_id` = d.`id`
and b.`id` = db.`doc_id`
and b.ver_b in ('BA','BB')
) sdb
FROM `docs` d
) X
http://sqlfiddle.com/#!9/fee4c6/39
id sumsummerdesum
1 6
2 1
3 2
4 0
