我有3张桌子的关系
1.表特权(从用户名获取用户特权)
select branch_code,username from privilege where username='joko';
结果:
| branch_code | username |
|--------------|----------|
| 'IDJK003MPV' | 'joko' |
| 'IDJK001MAG' | 'joko' |
| 'IDJK002MCL' | 'joko' |
| 'IDBTNCTR' | 'joko' |
2.表biodata_karyawan(此表关于具有branch_code的员工)
select branch_code,status_karyawan from biodata_karyawan;
结果:
| branch_code | status_karyawan |
|--------------|-----------------|
| 'IDJB001BEC' | 'Aktif' |
| 'IDJB001BEC' | 'Aktif' |
| 'IDPA001PPA' | 'Aktif' |
| 'IDJK001MAG' | 'Aktif' |
| 'IDJB001BEC' | 'Aktif' |
| 'IDJB001BEC' | 'Tidak Aktif' |
| 'IDBTNCTR' | 'Tidak Aktif' |
3.表分支
select branch_code,branch_name from branch;
结果 :
| branch_code | branch_name |
|--------------|--------------------------|
| 'IDJB001BEC' | 'BEC BANDUNG' |
| 'IDJK001MAG' | 'MALL ARTHA GADING' |
| 'IDJK001HO' | 'HEAD OFFICE' |
| 'IDPA001PPA' | 'MALL MATAHARI JAYAPURA' |
| 'IDBE001BGK' | 'BENCOOLEN INDAH MALL' |
| 'IDJK002MPB' | 'METRO PASAR BARU' |
我希望结果会像这样:(记录仅作为示例)
| branch_code | branch_name | count |
|--------------|----------------|-------|
| 'IDJB001BEC' | 'MALL BANDUNG' | '5' |
| 'IDJK001MLP' | 'MALL LIPPO' | '2' |
| 'IDJK002MPI' | 'MALL PURI' | '0' |
| 'IDJB002ZZZ' | 'MALL POSO' | '0' |
谁能帮助我?
谢谢你的时间。
在count_status_karyawan上使用count然后进行分组
select bk.branch_code,b.branch_name,count(bk.status_karyawan) from
biodata_karyawan bk join
branch b on bk.branch_code = b.branch_code
group by bk.branch_code,b.branch_name
这是SQL小提琴
根据您的评论更新查询
select p.branch_code,b.branch_name,count(bk.status_karyawan) from
biodata_karyawan bk join
branch b on bk.branch_code = b.branch_code
join privilege p on b.branch_code = p.branch_code
Where p.username = 'joko'
group by p.branch_code,b.branch_name
更新小提琴
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句