mysql - 如何选择(计数+分组依据)和加入

Question

我正在尝试选择我拥有的数据库，但是当我尝试选择显示 count 时我卡住了> 在其中一列中。

这是我试过的查询:

SELECT * , SELECT COUNT((DISTINCT daftar.id_daftar) group by daftar.id_kelas) AS Count
                            FROM kelas
                            INNER JOIN matkul
                            ON kelas.id_matkul = matkul.id_matkul
                            INNER JOIN proker
                            ON kelas.id_proker = proker.id_proker order by nama_matkul

数据表

+-----------+--------+----------+---------------------+
| id_daftar | npm    | id_kelas | tgldaftar           |
+-----------+--------+----------+---------------------+
|        26 | admin3 |        0 | 2019-06-09 13:04:58 |
|        27 | admin3 |        0 | 2019-06-09 13:07:02 |
|        28 | admin3 |        0 | 2019-06-09 13:07:41 |
|        29 | admin3 |        0 | 2019-06-09 13:08:17 |
|        30 | admin3 |        7 | 2019-06-09 14:00:54 |
|        31 | admin3 |        6 | 2019-06-09 15:34:00 |
+-----------+--------+----------+---------------------+

餐 table 可乐

+----------+-----------+-----------+------------+------------+-------+---------+--------+------------+----------+------------+-----------+
| id_kelas | id_proker | id_matkul | nama_kelas | tgl        | jam   | ruangan | status | buka_tgl   | buka_jam | keterangan | kapasitas |
+----------+-----------+-----------+------------+------------+-------+---------+--------+------------+----------+------------+-----------+
|        0 |         2 |         1 | nini       | 2019-06-23 | 17.00 | -       | 3      | 2019-06-23 | 20.00    | Baus       | 3         |
|        6 |         2 |         3 | B          | 2019-06-22 | 20.00 | bb      | 2      | 2019-06-27 | 12.00    | kk         | 30        |
|        7 |         1 |         1 | Aduh       | 2019-06-14 | 23.00 | nddm    | 2      | 2019-06-29 | 20.00    | oke        | 1         |
+----------+-----------+-----------+------------+------------+-------+---------+--------+------------+----------+------------+-----------+

表 matkul

+-----------+------------------+
| id_matkul | nama_matkul      |
+-----------+------------------+
|         1 | akuntansi biayaz |
|         3 | ewean            |
|         5 | ewe              |
|         6 | tes              |
|         7 | Tesd             |
+-----------+------------------+

餐 table 服务人员

+-----------+---------------------------+
| id_proker | nama_proker               |
+-----------+---------------------------+
|         1 | Bebelac                   |
|         2 | Mentoring Accounting Club |
+-----------+---------------------------+

这是预期结果示例:

有人可以帮助我吗？感谢您的关注

Answer 1

如果您需要每个 daftar.id_kelas 的不同计数

SELECT  daftar.id_kelas COUNT(DISTINCT daftar.id_daftar) AS Count
FROM kelas
INNER JOIN matkul  ON kelas.id_matkul = matkul.id_matkul
INNER JOIN proker  ON kelas.id_proker = proker.id_proker 
group by daftar.id_kelas

如果你想要行和计数，那么你可以使用内部连接子查询来获取计数

select *,  t.count
from  kelas 
inner join  (
   SELECT  daftar.id_kelas, COUNT(DISTINCT daftar.id_daftar) AS Count
   FROM kelas
   INNER JOIN matkul  ON kelas.id_matkul = matkul.id_matkul
   INNER JOIN proker  ON kelas.id_proker = proker.id_proker
   group by daftar.id_kelas
) t on t.id_kelas = kelas.id_kelas 

您似乎需要以任何方式查看您的架构

  SELECT  daftar.id_kelas COUNT(*) AS Count
  FROM daftar 
  INNER JOIN kelas on daftar.id_kelas = kelas.id_kelas 
  INNER JOIN matkul  ON kelas.id_matkul = matkul.id_matkul
  INNER JOIN proker  ON kelas.id_proker = proker.id_proker 
  group by daftar.id_kelas

如果你想要行和计数，那么你可以使用内部连接子查询来获取计数

  select *,  t.count
  from  daftar  
  inner join  (
     SELECT  daftar.id_kelas COUNT(*) AS Count
      FROM daftar 
      INNER JOIN kelas on daftar.id_kelas = kelas.id_kelas 
      INNER JOIN matkul  ON kelas.id_matkul = matkul.id_matkul
      INNER JOIN proker  ON kelas.id_proker = proker.id_proker 
  group by daftar.id_kelas
  ) t on t.id_kelas = daftar.id_kelas