Nima uchun men "GROUP BY ifodasi emas" xatosini olamanmi?

Men sqlda loyihada ishchilarning miqdorini olishga intilaman, ammo so'rovning bir qismi tomonidan guruhda xatolik bor. Men ishlaydigan ishchilar bilan loyiha identifikatorlariga mos keladigan alohida jadvalga egamiz (workersonprojects). So'rovda har bir narsani tanlash kerak: 1 loyihada ishchilar soni.

SELECT projectid, name, developerleader, consultantleader, projectleader, budget, count(workerid) AS workers
FROM projects
JOIN workersonprojects on (projectid=project)
JOIN workers on (worker=workerid)
GROUP BY projectid;
1
@jarlh: "umumiy" qoida (SQL standartidagi kabi) quyidagilarni kengaytiradi: yoki guruhga tegishli bo'lmagan barcha ustunlar guruhlash ustunlariga (funktsiyalariga) bog'liq. Afsuski, Oracle buni qo'llab-quvvatlamaydi
qo'shib qo'ydi muallif a_horse_with_no_name, manba
Siz faqatgina guruhda "Statement" yoki "Aggregatsiya" funktsiyasida tanlangan ustunlarni ishlatishingiz mumkin
qo'shib qo'ydi muallif Jens, manba
Umumiy GROUP BY qoidasi shunday deyilgan: Agar GROUP BY yon tümcesi belgilansa, Tanlash ro'yxatidagi har bir ustun mos yozuvlar, bir guruh sütununu belgilashi yoki belgilangan funktsiya argümanı bo'lishi kerak!
qo'shib qo'ydi muallif jarlh, manba
@a_horse_with_no_name, ko'p dbms uni qo'llab-quvvatlamaydi. Agar "umumiy qoidamga" amal qilsangiz, hech qachon noto'g'ri yo'l tutasiz!
qo'shib qo'ydi muallif jarlh, manba
nima? GROUP BY ni aytish kerak. loyihalar
qo'shib qo'ydi muallif hurricane, manba

6 javoblar

Buning sababi siz GROUP BY projectid so'rovi orqali ko'rinadigan guruhiga qismini tanlash ro'yxatidagi barcha ustunlarni o'z ichiga olmaysiz. So'rovingizni o'zgartiring

SELECT p.projectid, 
name, 
developerleader, 
consultantleader, 
projectleader, 
budget,
xxx.workers
FROM projects p
JOIN workersonprojects wp on p.projectid= wp.project
JOIN workers w on p.worker= w.workerid
JOIN (select projectid, count(workerid) AS workers
FROM projects
GROUP BY projectid ) xxx ON p.projectid = xxx.projectid;
2
qo'shib qo'ydi

Asosan Group BY yon tümcesinde, barcha ustunlar, Guruh vazifalari ichida biron-bir yig'ish Funktsiyasida ishlatilmaydigan moddalar bilan qo'shilishi kerak. Bu erda SELECTED projectid, nom, developerleader, consultantleader, projectleader, byudjet ustunlari bor, ammo COUNT() funktsiyasidagi faqat bitta ustun bor. Shunday qilib, ushbu ustunlarni SELECT-ga qo'shishingiz va sizning ishingiz bo'yicha so'rovlaringiz bo'lgan boomlashingiz mumkin.

SELECT projectid,
  name,
  developerleader,
  consultantleader,
  projectleader,
  budget,
  COUNT(workerid) AS workers
FROM projects
JOIN workersonprojects
ON (projectid=project)
JOIN workers
ON (worker=workerid)
GROUP BY PROJECTID,
  name,
  developerleader,
  consultantleader,
  projectleader,
  budget;
0
qo'shib qo'ydi

Siz umumiy funksiyadan foydalanmoqdasiz: COUNT() . COUNT() dan boshqa barcha ustunlar bo'yicha guruhlashingiz kerak.

0
qo'shib qo'ydi

So'rovlar bo'yicha guruhda kerakli jadvalning "projectid" ni aniqlash kerak.

SELECT workersonprojects.projectid, projects.name, 
projects.developerleader, projects.consultantleader, projects.projectleader,
projects.budget, 
count(workersonprojects.workerid) AS workers
FROM projects
JOIN workersonprojects on 
(workersonprojects.projectid=projects.projectid)
JOIN workers on (workers.worker=workersonprojects.workerid)
GROUP BY workersonprojects.projectid;
0
qo'shib qo'ydi

Boshqa javoblarga ko'ra:

SELECT projectid, nom, ishlab chiquvchi, maslahatchi, proyektor, byudjet, hisob (ishchi) AS ishchilari Loyihalardan Ishchi loyihalarni ishga tushirish (project = loyiha) Xodimlarni (ishchi = ishchi) bajaring GROUP BY BY projectid, ism, ishlab chiquvchi, maslahatchi, proyektor, byudjet

0
qo'shib qo'ydi

Berilgan barcha tanlangan so'zlar atributlari bilan guruhlashingiz kerak, shuning uchun siz tanlagan bayonotda tanlagan barcha atributlarni bering.

0
qo'shib qo'ydi