右连接不生成所有行

发布于 2025-01-09 02:09:17 字数 454 浏览 0 评论 0原文

我有两个表Factory和Site，factory表有10条记录

Code      Site
Skirt     115
Skirt     116
Skirt     117
Skirt     118
Skirt     119
Skirt     120
Skirt     121
Skirt     122
Skirt     123
Skirt     124

，tbl_site有以下站点地点 115 116 117 118 119 120 121 122 123 124 125 但是当我运行以下查询时，我只得到 10 个计数，而不是 11 个

select count(*) from tbl_factory fr
right join tbl_site sit on fr.siteid = sit.siteid
where fr.code='Skirt'

原文

I have two tables Factory and Site, the factory table has 10 records

Code      Site
Skirt     115
Skirt     116
Skirt     117
Skirt     118
Skirt     119
Skirt     120
Skirt     121
Skirt     122
Skirt     123
Skirt     124

and tbl_site has the following sites
Site
115
116
117
118
119
120
121
122
123
124
125
But when I run the following query I only get 10 counts instead of 11

select count(*) from tbl_factory fr
right join tbl_site sit on fr.siteid = sit.siteid
where fr.code='Skirt'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

北座城市 2025-01-16 02:09:17

我会切换到 LEFT JOIN，从您想要所有行的表开始...本质上是相同的，但有助于从您想要的所有行开始，并可选择获取另一个。

这将显示适当的上下文，以确保所有站点均已统计，以及哪些站点拥有生产裙子的工厂。

select 
      s.siteid,
      coalesce( count(f.siteid), 0 ) as HasFactory
   from 
      tbl_site s 
         LEFT JOIN tbl_factory f
            on s.siteid = f.siteid
           AND f.code = 'Skirt'
  group by
      s.siteid

一旦确认，您只需进行一次计数，自左连接以来始终会给出 11 个站点（不需要在工厂表中具有匹配项）。但你也可以得到一些确实生产裙子的工厂的数量

select 
      count(*) TotalSites,
      count( distinct f.siteid ) CountOfFactoriesMakingSkirts
   from 
      tbl_site s 
         LEFT JOIN tbl_factory f
            on s.siteid = f.siteid
           AND f.code = 'Skirt'

I would switch to a LEFT JOIN starting with the table you want all rows... essentially the same, but helps to start with ALL you want and OPTIONALLY getting the other.

This will show proper context to ensure all sites accounted for and which sites have a factory that makes skirts.

select 
      s.siteid,
      coalesce( count(f.siteid), 0 ) as HasFactory
   from 
      tbl_site s 
         LEFT JOIN tbl_factory f
            on s.siteid = f.siteid
           AND f.code = 'Skirt'
  group by
      s.siteid

Once that is confirmed, you can just do a count which will always give 11 sites since a left-join (not required having a match in the factory table). But you can also get a count of factories that DID make skirts

select 
      count(*) TotalSites,
      count( distinct f.siteid ) CountOfFactoriesMakingSkirts
   from 
      tbl_site s 
         LEFT JOIN tbl_factory f
            on s.siteid = f.siteid
           AND f.code = 'Skirt'

回复收藏 0 原文