Nazir Ahmad
یک شنبه 10 فروردین 1393, 14:40 عصر
عرض سلام خدمت تمامی دوستان و اساتید گرام
ما یه کویری داریم که با استفاده از دستور اسکیو ال select نوشته شده و چون تعداد شروط داخل این دستور زیاد هست مدت زمان زیادی طول میکشه تا کویری باز بشه.
حالا من میخوام این کویری رو به کد تبدیل کنم.
ممنون میشم اگر اساتید کمک کنند.
اینم دستور اسکیو الSELECT n1.salN AS salN, n1.shobeN, n1.shftN, n1.clsN, Count(n1.[St-ID1]) AS [CountOfSt-ID1], [کامیاب]+[ناکام] AS [شامل امتحان], (SELECT Count(n3.Natijeh4)
FROM n as n3
WHERE (((n3.Natijeh4)="کامیاب")) and (n3.salN=n1.salN) and (n3.clsN=n1.clsN) and (n1.shftn=n3.shftn) and (n3.shobeN=n1.shobeN)) AS کامیاب, (SELECT Count(n2.Natijeh4)
FROM n as n2
WHERE (((n2.Natijeh4)="ناکام")) and (n2.salN=n1.salN) and (n1.clsN=n2.clsN) and (n1.shftn=n2.shftn) and (n2.shobeN=n1.shobeN)) AS ناکام, (SELECT Count(n4.Majmoo4)
FROM n as n4
WHERE (((n4.[HazerEm])="محروم")) and (n4.salN=n1.salN) and (n4.clsN=n1.clsN) and (n1.shftn=n4.shftn) and (n4.shobeN=n1.shobeN)) AS محروم, (SELECT Count(n6.Natijeh4)
FROM n as n6
WHERE (((n6.Natijeh4)="معذرتی")) and (n6.salN=n1.salN) and (n1.clsN=n6.clsN) and (n1.shftn=n6.shftn) and (n6.shobeN=n1.shobeN)) AS معذرتی, (SELECT Count(n7.Natijeh4)
FROM n as n7
WHERE (((n7.Natijeh4)="نقل مکان")) and (n7.salN=n1.salN) and (n1.clsN=n7.clsN) and (n1.shftn=n7.shftn) and (n7.shobeN=n1.shobeN)) AS [نقل مکان], Count(n1.HazerEm) AS CountOfHazerEm, [کامیاب9]+[ناکام9]+[مشروط9]+[معذرتی9] AS [شامل امتحان9], (SELECT Count(n3.Natijeh9)
FROM n as n3
WHERE (((n3.Natijeh9)="مشروط")) and (n3.salN=n1.salN) and (n3.clsN=n1.clsN) and (n1.shftn=n3.shftn) and (n3.shobeN=n1.shobeN)) AS مشروط9, (SELECT Count(n3.Natijeh9)
FROM n as n3
WHERE (((n3.Natijeh9)="کامیاب")) and (n3.salN=n1.salN) and (n3.clsN=n1.clsN) and (n1.shftn=n3.shftn) and (n3.shobeN=n1.shobeN)) AS کامیاب9, (SELECT Count(n2.Natijeh9)
FROM n as n2
WHERE (((n2.Natijeh9)="ناکام")) and (n2.salN=n1.salN) and (n1.clsN=n2.clsN) and (n1.shftn=n2.shftn) and (n2.shobeN=n1.shobeN)) AS ناکام9, (SELECT Count(n4.Majmoo9)
FROM n as n4
WHERE (((n4.[HazerEm9])="محروم")) and (n4.salN=n1.salN) and (n4.clsN=n1.clsN) and (n1.shftn=n4.shftn) and (n4.shobeN=n1.shobeN)) AS محروم9, (SELECT Count(n6.Natijeh9)
FROM n as n6
WHERE (((n6.Natijeh9)="معذرتی")) and (n6.salN=n1.salN) and (n1.clsN=n6.clsN) and (n1.shftn=n6.shftn) and (n6.shobeN=n1.shobeN)) AS معذرتی9, (SELECT Count(n7.Natijeh9)
FROM n as n7
WHERE (((n7.Natijeh9)="نقل مکان")) and (n7.salN=n1.salN) and (n1.clsN=n7.clsN) and (n1.shftn=n7.shftn) and (n7.shobeN=n1.shobeN)) AS [نقل مکان9], Count(n1.HazerEm9) AS CountOfHazerEm9
FROM n AS n1
GROUP BY n1.salN, n1.shobeN, n1.shftN, n1.clsN;
ممنون
یا حق
ما یه کویری داریم که با استفاده از دستور اسکیو ال select نوشته شده و چون تعداد شروط داخل این دستور زیاد هست مدت زمان زیادی طول میکشه تا کویری باز بشه.
حالا من میخوام این کویری رو به کد تبدیل کنم.
ممنون میشم اگر اساتید کمک کنند.
اینم دستور اسکیو الSELECT n1.salN AS salN, n1.shobeN, n1.shftN, n1.clsN, Count(n1.[St-ID1]) AS [CountOfSt-ID1], [کامیاب]+[ناکام] AS [شامل امتحان], (SELECT Count(n3.Natijeh4)
FROM n as n3
WHERE (((n3.Natijeh4)="کامیاب")) and (n3.salN=n1.salN) and (n3.clsN=n1.clsN) and (n1.shftn=n3.shftn) and (n3.shobeN=n1.shobeN)) AS کامیاب, (SELECT Count(n2.Natijeh4)
FROM n as n2
WHERE (((n2.Natijeh4)="ناکام")) and (n2.salN=n1.salN) and (n1.clsN=n2.clsN) and (n1.shftn=n2.shftn) and (n2.shobeN=n1.shobeN)) AS ناکام, (SELECT Count(n4.Majmoo4)
FROM n as n4
WHERE (((n4.[HazerEm])="محروم")) and (n4.salN=n1.salN) and (n4.clsN=n1.clsN) and (n1.shftn=n4.shftn) and (n4.shobeN=n1.shobeN)) AS محروم, (SELECT Count(n6.Natijeh4)
FROM n as n6
WHERE (((n6.Natijeh4)="معذرتی")) and (n6.salN=n1.salN) and (n1.clsN=n6.clsN) and (n1.shftn=n6.shftn) and (n6.shobeN=n1.shobeN)) AS معذرتی, (SELECT Count(n7.Natijeh4)
FROM n as n7
WHERE (((n7.Natijeh4)="نقل مکان")) and (n7.salN=n1.salN) and (n1.clsN=n7.clsN) and (n1.shftn=n7.shftn) and (n7.shobeN=n1.shobeN)) AS [نقل مکان], Count(n1.HazerEm) AS CountOfHazerEm, [کامیاب9]+[ناکام9]+[مشروط9]+[معذرتی9] AS [شامل امتحان9], (SELECT Count(n3.Natijeh9)
FROM n as n3
WHERE (((n3.Natijeh9)="مشروط")) and (n3.salN=n1.salN) and (n3.clsN=n1.clsN) and (n1.shftn=n3.shftn) and (n3.shobeN=n1.shobeN)) AS مشروط9, (SELECT Count(n3.Natijeh9)
FROM n as n3
WHERE (((n3.Natijeh9)="کامیاب")) and (n3.salN=n1.salN) and (n3.clsN=n1.clsN) and (n1.shftn=n3.shftn) and (n3.shobeN=n1.shobeN)) AS کامیاب9, (SELECT Count(n2.Natijeh9)
FROM n as n2
WHERE (((n2.Natijeh9)="ناکام")) and (n2.salN=n1.salN) and (n1.clsN=n2.clsN) and (n1.shftn=n2.shftn) and (n2.shobeN=n1.shobeN)) AS ناکام9, (SELECT Count(n4.Majmoo9)
FROM n as n4
WHERE (((n4.[HazerEm9])="محروم")) and (n4.salN=n1.salN) and (n4.clsN=n1.clsN) and (n1.shftn=n4.shftn) and (n4.shobeN=n1.shobeN)) AS محروم9, (SELECT Count(n6.Natijeh9)
FROM n as n6
WHERE (((n6.Natijeh9)="معذرتی")) and (n6.salN=n1.salN) and (n1.clsN=n6.clsN) and (n1.shftn=n6.shftn) and (n6.shobeN=n1.shobeN)) AS معذرتی9, (SELECT Count(n7.Natijeh9)
FROM n as n7
WHERE (((n7.Natijeh9)="نقل مکان")) and (n7.salN=n1.salN) and (n1.clsN=n7.clsN) and (n1.shftn=n7.shftn) and (n7.shobeN=n1.shobeN)) AS [نقل مکان9], Count(n1.HazerEm9) AS CountOfHazerEm9
FROM n AS n1
GROUP BY n1.salN, n1.shobeN, n1.shftN, n1.clsN;
ممنون
یا حق