代码之家 › 专栏 › 技术社区 › Sasha

MySQL-带连接的计数结果不正确

mysql

Sasha · 技术社区 · 4 年前

我有以下疑问:

SELECT count(a.users_id) as count
  FROM user_jobs_application a
  join job_shifts s
    on s.id = a.job_shift_id
 where s.jobs_id = 29
   and a.status = 1
 group 
    by a.users_id

在这种情况下计数应该是1,但我得到了4。我假设这是因为参加 -当我进行此查询时:

  SELECT *
    FROM user_jobs_application a
    join job_shifts s
      on s.id = a.job_shift_id
   where s.jobs_id = 29
     and a.status = 1

我得到了4行:

<row>
        <field name="users_id">15</field>
        <field name="job_shift_id">246</field>
        <field name="status">1</field>
        <field name="comment"></field>
        <field name="created_at">2020-11-19 06:08:50</field>
        <field name="updated_at">2020-11-19 06:08:50</field>
        <field name="id">58</field>
        <field name="id">246</field>
        <field name="jobs_id">29</field>
        <field name="status">1</field>
        <field name="shift_date">2020-11-23</field>
        <field name="from">08:00:00</field>
        <field name="to">16:00:00</field>
        <field name="duration">1</field>
        <field name="created_at">2020-11-19 05:32:48</field>
        <field name="updated_at">2020-11-19 05:33:44</field>
    </row>

    <row>
        <field name="users_id">15</field>
        <field name="job_shift_id">247</field>
        <field name="status">1</field>
        <field name="comment"></field>
        <field name="created_at">2020-11-19 06:08:50</field>
        <field name="updated_at">2020-11-19 06:08:50</field>
        <field name="id">59</field>
        <field name="id">247</field>
        <field name="jobs_id">29</field>
        <field name="status">1</field>
        <field name="shift_date">2020-11-24</field>
        <field name="from">08:00:00</field>
        <field name="to">16:00:00</field>
        <field name="duration">1</field>
        <field name="created_at">2020-11-19 05:32:48</field>
        <field name="updated_at">2020-11-19 05:33:44</field>
    </row>

    <row>
        <field name="users_id">15</field>
        <field name="job_shift_id">248</field>
        <field name="status">1</field>
        <field name="comment"></field>
        <field name="created_at">2020-11-19 06:08:50</field>
        <field name="updated_at">2020-11-19 06:08:50</field>
        <field name="id">60</field>
        <field name="id">248</field>
        <field name="jobs_id">29</field>
        <field name="status">1</field>
        <field name="shift_date">2020-11-25</field>
        <field name="from">08:00:00</field>
        <field name="to">16:00:00</field>
        <field name="duration">1</field>
        <field name="created_at">2020-11-19 05:32:48</field>
        <field name="updated_at">2020-11-19 05:33:44</field>
    </row>

    <row>
        <field name="users_id">15</field>
        <field name="job_shift_id">249</field>
        <field name="status">1</field>
        <field name="comment"></field>
        <field name="created_at">2020-11-19 06:08:50</field>
        <field name="updated_at">2020-11-19 06:08:50</field>
        <field name="id">61</field>
        <field name="id">249</field>
        <field name="jobs_id">29</field>
        <field name="status">1</field>
        <field name="shift_date">2020-11-26</field>
        <field name="from">08:00:00</field>
        <field name="to">16:00:00</field>
        <field name="duration">1</field>
        <field name="created_at">2020-11-19 05:32:48</field>
        <field name="updated_at">2020-11-19 05:33:44</field>
    </row>

但是当我执行此查询时,我得到一行(正确的行数):

SELECT *
  FROM user_jobs_application a
  join job_shifts s
    on s.id = a.job_shift_id
 where s.jobs_id = 29
   and a.status = 1
 group 
   by a.users_id

结果:

<row>
        <field name="users_id">15</field>
        <field name="job_shift_id">246</field>
        <field name="status">1</field>
        <field name="comment"></field>
        <field name="created_at">2020-11-19 06:08:50</field>
        <field name="updated_at">2020-11-19 06:08:50</field>
        <field name="id">58</field>
        <field name="id">246</field>
        <field name="jobs_id">29</field>
        <field name="status">1</field>
        <field name="shift_date">2020-11-23</field>
        <field name="from">08:00:00</field>
        <field name="to">16:00:00</field>
        <field name="duration">1</field>
        <field name="created_at">2020-11-19 05:32:48</field>
        <field name="updated_at">2020-11-19 05:33:44</field>
    </row>

这里的问题是什么?我做错了什么?

0 回复 | 直到 4 年前

Milda 4 年前

与GROUP BY一起使用的COUNT函数为您提供分组前的行总数。结果与使用SELECT COUNT(s.id)时相同。如果你想知道字段的唯一值的计数,你可以使用DISTINCT修饰符,如下所示: SELECT count(DISTINCT a.users_id) as count