英文:
How to count if there does not exist TRUE in the same category?
问题
|roadNum|length|
|-|-|
|3|140|
|9|209|
请注意，你提供的SQL查询代码中，表的别名不一致。你在查询中使用了 "c" 和 "r" 作为表的别名，但在 WHERE 子句中使用了 "h.highwayNum"，这会导致查询出现错误。我已经根据你的需求进行了修正。
英文:
Assume I have two tables:
| cameraNum | roadNum | isWorking |
|---|---|---|
| 100 | 1 | TRUE |
| 101 | 1 | FALSE |
| 102 | 1 | TRUE |
| 103 | 3 | FALSE |
| 104 | 3 | FALSE |
| 105 | 7 | TRUE |
| 106 | 7 | TRUE |
| 107 | 7 | TRUE |
| 108 | 9 | FALSE |
| 109 | 9 | FALSE |
| 110 | 9 | FALSE |
| roadNum | length |
|---|---|
| 1 | 90 |
| 3 | 140 |
| 7 | 110 |
| 9 | 209 |
I want to select a table like this:
If there is no camera working, I put it in the table.
| roadNum | length |
|---|---|
| 3 | 140 |
| 9 | 209 |
I tried this below:
SELECT r.roadNum, r.length
FROM Cameras c, Road r
WHERE c.isWorking = FALSE
AND h.highwayNum = c.highwayNum
But these code only fliter there exists FALSE in isWorking.
| roadNum | length |
|---|---|
| 1 | 90 |
| 3 | 140 |
| 9 | 209 |
答案1
得分: 1
以下是代码的中文翻译：
您想要的是所有摄像头都不工作的道路。以下是一种使用聚合和`having`的方法：
select r.*
from road r
inner join camera c on c.roadNum = r.roadNum
group by r.roadNum
having not bool_or(isWorking)
| roadnum | length |
|---|---|
| 3 | 140 |
| 9 | 209 |
英文:
You want roads whose all cameras are not working. Here is one way to do it with aggregation and having:
select r.*
from road r
inner join camera c on c.roadNum = r.roadNum
group by r.roadNum
having not bool_or(isWorking)
| roadnum | length |
|---|---|
| 3 | 140 |
| 9 | 209 |
答案2
得分: 0
关于使用 not exists，是的，你可以使用它。以下代码使用公共表达式（CTE）仅获取满足摄像头要求的roadnum，然后将其与road表连接起来（请参见演示）。
with no_working_camera (roadnum) as
( select distinct on (c1.roadNum)
c1.roadnum
from cameras c1
where not c1.isworking
and not exists (select null
from cameras c2
where c2.roadNum = c1.roadNum
and c2.isworking
)
order by c1.roadnum
)
select r.*
from no_working_camera nwc
join road r
on nwc.roadnum = r.roadnum;
英文:
Regarding using not exists, yes, you can use it. The following uses a CTE to get only the roadnum of those satisfying the camera requirement then joins that to road: (see demo)
with no_working_caMera (roadnum) as
( select distinct on (c1.roadNum)
c1.roadnum
from cameras c1
where not c1.isworking
and not exists (select null
from cameras c2
where c2.roadNum = c1.roadNum
and c2.isworking
)
order by c1.roadnum
)
select r.*
from no_working_camera nwc
join road r
on nwc.roadnum = r.roadnum;
答案3
得分: 0
只使用NOT IN或NOT EXISTS来查找没有工作摄像头的道路：
select *
from road
where roadnum not in (select roadnum from cameras where isworking);
英文:
Don't join, don't aggregate, just use NOT IN or NOT EXISTS in order to find roads that don't have a working camera:
select *
from road
where roadnum not in (select roadnum from cameras where isworking);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论