SQL Serverda boshqa jadvalga asoslangan satr sonini hisoblash

Menda ikkita stol bor - biri xodimlar, ikkinchisi esa farzandlari uchun.

Odamlarni bolalar soniga qanday guruhlash mumkin?

xodimlar stoli

pID|name|family|ETC|  
---- |-------- |-------|-------|    
123 |abc|ddd   | 134 | f |   
124 |dfv |eee   | 900 | d |     
126 |rgt |wwww|750|m|    

bolalar jadvali

pID|name|family|ETC|        
---- |-------- |-------|-------|    
123 |mah|ddd |2005/06/09|son |    
123 |dar|ddd |2013/11/01|girl|    
123 |sia|ddd |2003/01/01|girl|    
126 |naz|wwww|2007/25/01|girl|     

Expected table

pID|no|        
----- |- |    
123 |3|    
124 |0|    
126 |1|
0
Siz hali biror narsani sinab ko'rdingizmi?
qo'shib qo'ydi muallif Tim Biegeleisen, manba
Siz hali biror narsani sinab ko'rdingizmi?
qo'shib qo'ydi muallif Tim Biegeleisen, manba

9 javoblar

bolalar ga xodimlar dan qo'shiling va pID bo'yicha guruhlash siz uchun ishni bajaradi:

select
    S.pID,
    count(C.name) as no
from staff as S
    left outer join childern as C on C.pID = S.pID
group by S.pID
2
qo'shib qo'ydi
Nima uchun bunday emas? So'rovni faqat bitta kodni no va pID - hisoblash orqali guruhlashni tanlang va siz xohlagan narsani aniqlaysiz.
qo'shib qo'ydi muallif Andy Korneyev, manba
bolalar hisobiga kadr sonini olish mumkinmi? 0 bola 50 kishi, 1 bola 60 kishiga o'xshashmi?
qo'shib qo'ydi muallif jaleel, manba

bolalar ga xodimlar dan qo'shiling va pID bo'yicha guruhlash siz uchun ishni bajaradi:

select
    S.pID,
    count(C.name) as no
from staff as S
    left outer join childern as C on C.pID = S.pID
group by S.pID
2
qo'shib qo'ydi
Nima uchun bunday emas? So'rovni faqat bitta kodni no va pID - hisoblash orqali guruhlashni tanlang va siz xohlagan narsani aniqlaysiz.
qo'shib qo'ydi muallif Andy Korneyev, manba
bolalar hisobiga kadr sonini olish mumkinmi? 0 bola 50 kishi, 1 bola 60 kishiga o'xshashmi?
qo'shib qo'ydi muallif jaleel, manba

Agar siz barcha PiDlar natijasini istasangiz (ular 0 bolali bo'lsa ham) undan foydalaning:

select
  staff.pID,count(children.name) as count
from staff
  left outer join childern on childern.pID = staff.pID
group by S.pID

(Andy Korneyev bilan bir xil javob)

Biroq, faqat bolalar (0 dan katta) hisoblashni istasangiz, undan foydalaning:

select
  staff.pID,count(children.name) as count
from staff
  inner join childern on childern.pID = staff.pID
group by S.pID

Umid qilamanki bu yordam.

0
qo'shib qo'ydi

Agar siz barcha PiDlar natijasini istasangiz (ular 0 bolali bo'lsa ham) undan foydalaning:

select
  staff.pID,count(children.name) as count
from staff
  left outer join childern on childern.pID = staff.pID
group by S.pID

(Andy Korneyev bilan bir xil javob)

Biroq, faqat bolalar (0 dan katta) hisoblashni istasangiz, undan foydalaning:

select
  staff.pID,count(children.name) as count
from staff
  inner join childern on childern.pID = staff.pID
group by S.pID

Umid qilamanki bu yordam.

0
qo'shib qo'ydi
select s.pId
     , (select count(*) from [children] c where c.pId = s.pId)
  from [staff] s;

[Bolalar] va [xodimlar] ni haqiqiy jadval nomlari bilan almashtiring.

0
qo'shib qo'ydi

Buni ko'ring

select S.pID,
(select isnull(COUNT(*),0) from children C where C.pID=S.pID)
from Staff S
0
qo'shib qo'ydi

Buni ko'ring

select S.pID,
(select isnull(COUNT(*),0) from children C where C.pID=S.pID)
from Staff S
0
qo'shib qo'ydi
SELECT staff.pid,count(*) FROM staff,children WHERE staff.pid=children.pid GROUP BY staff.pid;

Ushbu so'rov natijada chiqadigan chiqishni beradi.

0
qo'shib qo'ydi
Bugungi maslahat: zamonaviy, aniq JOIN sintaksisiga o'tish! Yozish oson (xatosiz), o'qish va saqlashni osonlashtiradi va kerak bo'lganda tashqi qo'shilishni osonlashtiradi.
qo'shib qo'ydi muallif jarlh, manba
SELECT staff.pid,count(*) FROM staff,children WHERE staff.pid=children.pid GROUP BY staff.pid;

Ushbu so'rov natijada chiqadigan chiqishni beradi.

0
qo'shib qo'ydi
Bugungi maslahat: zamonaviy, aniq JOIN sintaksisiga o'tish! Yozish oson (xatosiz), o'qish va saqlashni osonlashtiradi va kerak bo'lganda tashqi qo'shilishni osonlashtiradi.
qo'shib qo'ydi muallif jarlh, manba