بدست آوردن رکوردهای مشترک و غیر تکراری با شرط خاص

[فؤاد]

سلام

فرض کنیم در یک جدول فیلدهای زیر رو داریم

Name
Class_ID
Score

میخواهیم

لیست غیر تکراری Name هایی که در همه Class_ID ها مشترک هستند را بدست آوریم و جمع Score آنها را در تمامی Class_ID ها داشته باشیم

فیلدهای خروجی

Name
Total_Score

با تشکر

[Salah Sanjabian]

سلام یه نمونه از داده ها و نتیجه ای که از داده هات میخوای بذار تا دوستان بهتر بتونن راهنمایی کنن.
Class_ID رفرنس از یه جدول دیگست؟

[فؤاد]

سلام

عذر میخوام اگه سوالم گویا نبود


توی برنامه فقط یک جدول دارم Tbl_Data

فیلدهای این جدول ایناست

ID PK
Name Nvarchar
Class_ID int
Score int

فیلد Name نام دانشجو رو ذخیره میکنه
فیلد Class_ID کد کلاس رو ذخیره میکنه (فرض کنیم رفرنس نیست و کاری به حواشیش نداریم)
فیلد Score نمره کسب شده توسط دانشجو توی اون کلاس رو ذخیره میکنه
نکته: هر دانشجو میتونه همزمان توی چند تا کلاس شرکت کرده باشه

فرض کنید این اطلاعات رو دارم


Score-----Class_ID--------Name-----ID
------------------------------------------------------
1-----مرتضی محدثی-----10----------10
2-----علی رضایی--------11----------20
3-----علی رضایی--------12----------30
4-----محمد جوادی-------11----------40
5-----محمد جوادی-------12----------50

در جدول بالا از سه نفر موجود فقط دو نفر هستند که توی کلاسهای با کد 11 و 12 شرکت کرده اند و نفر دیگه (مرتضی محدثی) فقط توی کلاس کد 10 شرکت کرده
حالا من میخواهم لیست دانشجویانی که در تمامی کلاسها (در مثال بالا کد 11 و 12 ) شرکت کرده اند رو بدست بیارم
یعنی نقطه مشترک خروجی لیست دانشجویان من اینه که تمامی اونا توی همه کلاس هایی که توی لیست رکوردام ازشون نام برده شده شرکت کرده اند و نمره دارند

جمع نمرات هر دانشجو رو هم توی کل کلاسها میخوام

Sum(point)---------------Name
---------------------------------
علی رضایی ---------- 50
محمد جوادی ---------- 90


با تشکر

[Mahmoud.Afrad]

براساس نام شخص گروهبندی کنید تا به نتیجه برسید:

SELECT Name , SUM(Score)
FROM [Table_Name]
WHERE Class_ID=11 OR Class_ID=12
GROUP BY Name

[محمد سلیم آبادی]

select name, sum(score) as score
from tbl_data
group by name
having count (distinct case when class_id in (11, 12) then class_id end) = 2;

[فؤاد]

سلام و تشکر

یه نکته !!!! شما توی کوئری که ارسال کردین Class_ID رو میدونستین که 11 و 12 هست..

فرض کنید اصلا نمیدونم چه کلاس های وجود داره ....فقط میخوام لیست دانشجوهایی که توی تمامی کلاسها شرکت کرده اند رو بدست بیارم....
با این حال کوئری شما چه تغییری باید کنه

بازم ممنونم

[محمد سلیم آبادی]

یه نکته !!!! شما توی کوئری که ارسال کردین Class_ID رو میدونستین که 11 و 12 هست..
با این احتساب شما باید مقدار 10 را نیز به لیست class_id هایتان در مثالی که زدین اضافه کنید!
امتحان کنید:
select name, sum(score) as score
from tbl_data t1
inner join (select distinct class_id from tbl_data) d
on t1.class_id = d.class_id
group by name
having count(distinct class_id) = (select count(distinct class_id) from tbl_data);

[فؤاد]

سلام

تست کردم

هیچ رکوردی رو برنمیگردونه !!!!

[محمد سلیم آبادی]

جمله زیر را خواندین؟

با این احتساب شما باید مقدار 10 را نیز به لیست class_id هایتان در مثالی که زدین اضافه کنید!شما مگر قرار نیست افرادی را بدست بیارین که تمام دروس را انتخاب کرده باشند؟ تمام دروس هم درس های 10و11و12 است. خب هیچ فردی وجود ندارد که تمام دروس را انتخاب کرده باشد. به گفته خودتان:

فرض کنید اصلا نمیدونم چه کلاس های وجود داره ....فقط میخوام لیست دانشجوهایی که توی تمامی کلاسها شرکت کرده اند رو بدست بیارم....

[tooraj_azizi_1035]

فرض کنید اصلا نمیدونم چه کلاس های وجود داره ....فقط میخوام لیست دانشجوهایی که توی تمامی کلاسها شرکت کرده اند رو بدست بیارم....

چون نمی دونیم کدام کلاس ها هستند پس وجود Where بی معنی است و باید حذف شود.


SELECT Name , SUM(Score)
FROM [Table_Name]
GROUP BY Name

[فؤاد]

سلام و هزار تشکر

به پست سه دقت کنید

به جدول اطلاعات موجود و خروجی که میخواهم داشته باشم دقت مجدد کنید

ما 3 دانشجو داریم و 3 کلاس

از بین این 5 رکورد تنها دو رکورد قراره توی خروجی باشه / نقطه اشتراکشون چیه / این دو دانشجو هر دو شون توی دو کلاس اشتراک حضور دارند / نفر سوم (مرتضی محدثی) هم حتی اگه توی یکی از کلاسهای 11 یا 12 شرکت میکرد باز هم نمیبایست در لیست خروجی می آمد

در واقع این دو رکورد خروجی اسامی دانشجویانی هست که با هم از نظر کلاسها نقطه اشتراک دارند. (علی رضایی و محمد جوادی چون هر دوشون توی کلاسهای 11 و 12 شرکت داشته اند باید در خروجی مورد نظرمون بیاد / البته به همراه جمع نمرات کل کلاسهاشون


بازم از وقتی که گذاشتین ممنونم

[محمد سلیم آبادی]

بر اساس داده های زیر خروجی باید چگونه باشد؟

Score-----Class_ID--------Name-----ID
------------------------------------------------------
1-----مرتضی محدثی-----10----------10
2-----علی رضایی--------11----------20
3-----علی رضایی--------12----------30
4-----محمد جوادی-------11----------40
5-----محمد جوادی-------12----------50
6-----حسن حسنی------10----------25
7-----حسین حسینی---11-----------100
8-----حسین حسینی---12-----------100
9---- محمد محمدی -----10-----------50
10---محمد محمدی ------11-----------50
11---محمد محمدی ------12------------50

[فؤاد]

بر اساس داده های زیر خروجی باید چگونه باشد؟

Score-----Class_ID--------Name-----ID
------------------------------------------------------
1-----مرتضی محدثی-----10----------10
2-----علی رضایی--------11----------20
3-----علی رضایی--------12----------30
4-----محمد جوادی-------11----------40
5-----محمد جوادی-------12----------50
6-----حسن حسنی------10----------25
7-----حسین حسینی---11-----------100
8-----حسین حسینی---12-----------100
9---- محمد محمدی -----10-----------50
10---محمد محمدی ------11-----------50
11---محمد محمدی ------12------------50



بر اساس داده های فوق چون فقط محمد محمدی هست که در تمامی کلاسها شرکت دارد خروجی ما محمد محمدی با جمع نمرات 150 خواهد بود


score -------------------- name
محمد محمدی ------------150

[Salah Sanjabian]

در واقع این دو رکورد خروجی اسامی دانشجویانی هست که با هم از نظر کلاسها نقطه اشتراک دارند. (علی رضایی و محمد جوادی چون هر دوشون توی کلاسهای 11 و 12 شرکت داشته اند باید در خروجی مورد نظرمون بیاد / البته به همراه جمع نمرات کل کلاسهاشون



براساس این حرف شما نباید محمد محمدی در خروجی بیاد چون هیچ نقطه اشتراکی با کسی ازنظر کلاس نداره




Score-----Class_ID--------Name-----ID
------------------------------------------------------
1-----مرتضی محدثی-----10----------10
2-----علی رضایی--------11----------20
3-----علی رضایی--------12----------30
4-----محمد جوادی-------11----------40
5-----محمد جوادی-------12----------50

در جدول بالا از سه نفر موجود فقط دو نفر هستند که توی کلاسهای با کد 11 و 12 شرکت کرده اند و نفر دیگه (مرتضی محدثی) فقط توی کلاس کد 10 شرکت کرده
حالا من میخواهم لیست دانشجویانی که در تمامی کلاسها (در مثال بالا کد 11 و 12 ) شرکت کرده اند رو بدست بیارم
یعنی نقطه مشترک خروجی لیست دانشجویان من اینه که تمامی اونا توی همه کلاس هایی که توی لیست رکوردام ازشون نام برده شده شرکت کرده اند و نمره دارند

جمع نمرات هر دانشجو رو هم توی کل کلاسها میخوام

Sum(point)---------------Name
---------------------------------
علی رضایی ---------- 50
محمد جوادی ---------- 90


با تشکر
دوست عزیز پست هاتون اصلا به هم ربط نداره این دو نفر رو که من در بالا نقل قول کردم هیچ کدام در همه کلاس ها شرکت نکردن !!!
در حالی که تو پست قبلیتون میگین فقط محمد محمدی چون در همه کلاس ها شرکت کرده
اگه براساس پست قبلیتون فرض رو بذاریم و بگیم کسی که تو همه کلاسا شرکت کرده کوئریتون این باید باشه و گرنه اطلاعاتتون ناقص هست

SELECT name,SUM(Score)
FROM dbo.Class
GROUP BY NAME
HAVING COUNT (DISTINCT Class_ID)=(SELECT COUNT ( DISTINCT Class_ID) FROM dbo.Class)