نوشتن یک کوئری برای استخراج اطلاعات از 4 جدول
سلام
من یک پایگاه داده mysql دارم که برای یک آموزشگاه علمی است.
در این آموزشگاه دانش آموزان در کلاسهای مد نظر ثبت نام می کنند. شهریه واریز می کنند و ممکن است از یک کلاس انصراف هم بدهند.
این پایگاه داده دارای یک جدول students است که حاوی اطلاعات عادی دانش آموز از جمله id است. (فقط به فیلدهای مهم اشاره می کنم)
یک جدول class که حاوی اطلاعات عمومی کلاسها است از جمله class_id,name,tuition. مورد آخر مقدار شهریه کلاس را نگهداری می کند.
یک جدول واسط به نام kelas وجود دارد که برای ثبت اطلاعات id,class_id,student_id,state است. اگر دانش آموز از کلاس انصراف دهد فیلد state به 0 تغییر میکند. در ثبت نام نیز مقدار آن 1 است.
یک جدول نیز به نام payment پرداخت ها را نگهداری می کند. شامل فیلدهای id,class_id,student_id,price,time
(برای جدول ها هیچ رابطه ای تعریف نشده است)
حال می خواهم یک کوئری داشته باشم که بتواند با داشتن آیدی یک دانش آموز اطلاعات زیر را به من بدهد.
1- مشخصات کلاسهای که دانش آموز با آیدی خاص در آن ثبت نام کرده است.
2- ممجموع پرداخت دانش آموز مورد نظر در هر کدام از کلاسهای ثبت نامی.
3- تعداد کل دانش آموزان ثبت نام شده در هر کدام از کلاسهایی که اشاره شد.
4- تعداد دانش آموزان انصرافی از هر کدام از کلاسهایی که اشاره شد.
اگر به اطلاعات دیگری نیاز است لطفا مطرح کنید تا پاسخ دهم.
من تلاشهای زیادی کردم تا بتوانم این کوئری را بزنم اما هر مرتبه در یک بخش دچار مشکل شدم. برای مثال این کوئری درست کار نمی کند:
select class.* ,sum(DISTINCT payment.price) as sumPay, kelas.state as studentState,kelas.student_id , count(DISTINCT kelas.state) countEnserafiCompliment,sum(kelas.cancelpi) as cancelipayment,COUNT(DISTINCT kelas.student_id) as studentNumber
FROM kelas
JOIN class ON kelas.class_id = class.class_id
left JOIN payment ON kelas.class_id = payment.class_id AND kelas.student_id=payment.student_id
WHERE kelas.student_id = 1
GROUP BY kelas.class_idمتشکرم
گزینش سوالات
گفت و گو های مرتبط
دسته بندی سوالات
تگ‌های محبوب
SELECT
class.name,
kelas.student_id AS 'student_id',
(
SELECT
COUNT(*)
FROM
kelas
WHERE
state=1 AND class_id=class.class_id
) as 'canceli',
(
SELECT
COUNT(*)
FROM
kelas
WHERE
class_id=class.class_id
) as 'all',
(
SELECT
SUM(`receipt`)
FROM
payment
WHERE
class_id=class.class_id AND student_id =1
) as 'total_payment'
FROM
class
INNER JOIN kelas ON kelas.class_id=class.class_id AND kelas.student_id=1
سلام
دلیل خاصی برای اینکه فقط از یک کوئری استفاده کنید وجود داره؟
توی تگ هاتون به php اشاره کردید !
سلام
اطلاعاتی که از این کوئری خواهم گرفت رو توی یک جدول واحد نیاز دارم. واسه همین می خوام یک کوئری باشه. البته اگر بشه چند کوئری زد و نتایج رو به صورت یکپارچه تو یه جدول استفاده کرد استفاده می کنم. اما تا جاییکه بلدم اینطوری نمی شه.
سایت روی زبان php هست
یعنی قصدتون این هست داده هارو در یک رکورد توی یک جدول دیگه ذخیره کنید. درست متوجه شدم ؟
@rafig.xiyavi
نه
من این داده ها رو می خوام بگیرم که تو صفحه خود دانش آموز نمایش بدم. تو هر سطر نام کلاس دانش آموز، تعداد همکلاسی هاش، مبلغی برای اون کلاس پرداخت کرده و ... رو منتشر کنم.
یعنی تو هر سطر اطلاعات مربوط به یک کلاسی که توش ثبت نام کرده نمایش داده می شه
SELECT
class.name,
kelas.student_id AS 'student_id',
(
SELECT
COUNT(*)
FROM
kelas
WHERE
state=1 AND class_id=class.class_id
) as 'canceli',
(
SELECT
COUNT(*)
FROM
kelas
WHERE
class_id=class.class_id
) as 'all',
(
SELECT
SUM(`receipt`)
FROM
payment
WHERE
class_id=class.class_id AND student_id =1
) as 'total_payment'
FROM
class
INNER JOIN kelas ON kelas.class_id=class.class_id AND kelas.student_id=1
آقای @mehranmarandi90 از وقتی که گذاشتید متشکرم. به قدری تمیز و روان کد رو نوشتید که موفق شدم موارد دیگری که مد نظرم بود رو به راحتی اضافه کنم.
من تک تک عباراتی که شما استفاده کردید رو بلد بودم. اما نتونستم این کوئری رو بزنم. اگر مقدور بود لطفا در مورد اینکه چطور برای payment جوین نزدید ولی برای class جوین زدید رو توضیح بدید. امیدوارم برای سایر دوستان نیز جنبه آموزشی هم داشته باشه.
کوئری نهایی این شد:
SELECT
class.class_id,class.name,class.term,class.state,class.tuition,
kelas.state as studentState,kelas.student_id AS 'student_id',kelas.cancelpi as studentCanceliPi,
(
SELECT
COUNT(*)
FROM
kelas
WHERE
state=1 AND class_id=class.class_id
) as 'present',
(
SELECT
COUNT(*)
FROM
kelas
WHERE
class_id=class.class_id
) as 'allStudentNumber',
(
SELECT
SUM(kelas.cancelpi)
FROM
kelas
WHERE
class_id=class.class_id
) AS 'sumCanceliPi',
(
SELECT
SUM(`price`)
FROM
payment
WHERE
class_id=class.class_id AND student_id =".$_GET['id']."
) as 'total_payment'
FROM
class
INNER JOIN kelas ON kelas.class_id=class.class_id AND kelas.student_id=".$_GET['id']خواهش میکنم، لطف دارید
این کوئری در اصل از subqueries استفاده کرده، ممکنه بشه بصورت پیچیده تری هم با جوین های بیشتر به همین نتیجه رسید.
در sub query شما در داخل یک کوئری کوئری های مستقل دیگه ای رو اجرا میکنید و دیتابیس اونها رو مثل یک حلقه اجرا میکنه، به همین دلیل در subquery نیازی به join , union , .. در کوئری لایه بالاتر نداریم.
*در داده های عظیم کوئری های تو در تو ممکنه بهینه نباشن مخصوصا اگر در استفاده از اونها افراط کنیم.