چگونگي نمايش نتايج جستجو در ستونهاي اضافه شونده در كوئري؟

[oghab]

سلام
سوالم ساده است ولي نميدونم چطور مطرحش كنم. پس يك مثال ساده ميزنم
فرضا ما دو تا جدول داريم با عنوان students و course

http://dc93.4shared.com/img/123580214/cd1c52f7/1_online.gif?sizeM=3
حالا اگه ما يه كوئري بنويسيم با اين كد


Select s_name,s_family,course from students,course where students.s_no=course.s_no

نتيجه به اين صورت ميشه:
http://dc93.4shared.com/img/123580231/8f40c4fa/2_online.gif?sizeM=3
يعني براي هر درس اسم و فاميل را تكرار ميكنه
حالا اگه ما بخواهيم نتيجه به اين صورت بشه و به جاي تكرار خودش ستون اضافه كنه
http://dc93.4shared.com/img/123580243/2e0f3311/3_online.gif?sizeM=3

براي گرفتن چنين نتيجه اي براي كوئري چه كدي بايد بنويسيم.
خيلي ممنون ميشم راهنمايي كنيد.
واقعا نميدونم چه چيزي سرچ كنم تا از تاپيكهاي قبلي جواب بگيرم.
كمك خيلي بزرگي ميكنيد اگه راهنمايي ام كنيد.

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

شاید بشوند با چندین LEFT OUTER JOIN یا چند subquery به خواستتون بررسید ولی من پیشهاد می کنم که یک ستون برای نام دانشجو و یک ستون برای لیست دروس انتخاب شده اش در نظر بگیرید.


ستون نام ستون لیست دروس
----------------------------------------------
دانشجوی 1 درس 1و درس 2و درس 3
دانشجوی 2 درس 2و درس 5



ولی اگر اصرار به انجام چنین کاری دارید بفرمایید تا بلکه سعی کنیم یک query تمیزی براش در بیاریم.

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

یک راه حل:

اپتدا جدول اصلی یعنی لیست دروس دانشجویان را ایجاد و با داده های نمونه پر می کنیم.


CREATE TABLE Course
(
S_No int,
course nvarchar(25)
)

INSERT Course
SELECT 1,'math 1' UNION ALL
SELECT 1,'phisisc 1' UNION ALL
SELECT 2,'math 1' UNION ALL
SELECT 3,'culture' UNION ALL
SELECT 3,'history' UNION ALL
SELECT 3,'moadelat'


سپس با کمک Derived Table ، Ranking Functions، Case Function، و SELECT INTO یک query ایجاد می کنیم البته این راه حل فقط تا سه درس دانشجویان را نشان خواهد داد.




if object_id('temp') is not null drop table temp
SELECT S_no,
course,
row=ROW_NUMBER() OVER(PARTITION BY S_no ORDER BY course)
INTO temp
FROM Course

SELECT [Student Number]=(CASE WHEN D1.S_no IS NOT NULL THEN D1.S_no
ELSE
CASE WHEN D2.S_no IS NOT NULL THEN D2.S_no
ELSE D3.S_no
END
END),
ISNULL(D1.course,'') AS Course1,
ISNULL(D2.course,'') AS Course2,
ISNULL(D3.course,'') AS Cource3
FROM
(
SELECT S_no,
course
FROM temp
WHERE row=1
) D1
FULL OUTER JOIN
(
SELECT S_no,
course
FROM temp
where row=2
)D2 ON D1.s_no=D2.S_no
FULL OUTER JOIN
(
SELECT S_no,
course
FROM temp
WHERE row=3
)D3 ON D3.s_no=D2.S_no

Drop Table Temp


پس از اجرا Query داده های زیر به نمایش در خواهند آمد.




Student Number Course1 Course2 Cource3
-------------- ------------------------- ------------------------- -------------------------
1 math 1 phisisc 1
2 math 1
3 culture history moadelat

[m1975b]

از pivot استفاده کن

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

از pivot استفاده کن

لطفا کد! تعریف کافی نیست.

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

به مثال زیر توجه کنید که از عملگر PIVOT استفاده شده است.

نتیجه را با نتیجه راه حل پیشنهادی من مقایسه کنید.



SELECT s_no,
[math 1],
[phisisc 1],
[culture],
[history],
[moadelat]
FROM ( SELECT course,s_no
FROM course
) s
PIVOT
(
max(course)
FOR course IN ( [math 1],
[phisisc 1],
[culture],
[history],
[moadelat])
)as d


نتیجه



s_no math 1 phisisc 1 culture history moadelat
----------- ------------------------- ------------------------- ------------------------- ------------------------- -------------------------
1 math 1 phisisc 1 NULL NULL NULL
2 math 1 NULL NULL NULL NULL
3 NULL NULL culture history moadelat

(3 row(s) affected)

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

راه حل دوم:

همیشه راه حل اول بهترین نخواهد بود.

این Query رو امتحان کن. این راه حل حداقل از راه حل اول ساده تر و بهتره.



if object_id('temp') is not null drop table temp
SELECT S_no,
course,
row=ROW_NUMBER() OVER(PARTITION BY S_no ORDER BY course)
INTO temp
FROM Course

SELECT s_no,
[course 1]=ISNULL((SELECT course FROM temp t
WHERE t.s_no=s.s_no AND t.row=1),''),
[course 2]=ISNULL((SELECT course FROM temp t
WHERE t.s_no=s.s_no AND t.row=2),''),
[course 3]=ISNULL((SELECT course FROM temp t
WHERE t.s_no=s.s_no AND t.row=3),'')
FROM (SELECT DISTINCT s_no
FROM temp) s

Drop Table temp



نتیجه:




s_no course 1 course 2 course 3
----------- ------------------------- ------------------------- -------------------------
1 math 1 phisisc 1
2 math 1
3 culture history moadelat




کافی است یا ادامه بدم؟

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

راه حل سوم:

استفاده از PIVOT همراه با Ranking Function .



SELECT S_No,
ISNULL([1],'') as Course1,
ISNULL([2],'') as Course2,
ISNULL([3],'') as Course3
FROM
(SELECT row_Number() OVER (PARTITION BY S_No ORDER BY course) AS RowID,*
FROM Course) p
PIVOT
(Max(Course)
FOR RowID IN
([1],[2],[3])
) AS pvt


نتیجه دقیقا همان چیزی است که می خواهید.




S_No Course1 Course2 Course3
----------- ------------------------- ------------------------- -------------------------
1 math 1 phisisc 1
2 math 1
3 culture history moadelat

[oghab]

سلام
خيلي ممنون از دوستاني كه نظر دادند و راهنمايي كردند!
راستش كدهاي پيشنهادي شما يك مقدار پيچيده است و بايد امتحان كنم و تغيير بدم تا به جواب برسم. ممنونم
فكر ميكردم راه حل و كد ساده تري داشته باشه. يعني استفاده از يه دستور خاص اين كار را انجام بده و نياز به اين همه كد نوسي نداشته باشه
راستش من سوال را در حد يك مثال ساده مطرح كردم و گرنه كوئري select ي كه من در حالا حاضر ميسازم بر اساس فيلدهاي انتخابي به دلخواه كاربر و با شرطهاي دلخواه كاربر از حدود 20 تا جدول است كه با هم join ميشند. و تعداد نتايج هم نميتونم به سه تا محدود كنم.
و اگه بخواهم كدهاي بالا را به بسط بدم براي 20 تا جدول فكر ميكنم خيلي پيچيده ميشه.
نمونه از كوئري كه توسط برنامه در دلفي براي گرفتن كوئري از Sql ساخته شده.

select distinct patients.patient_no as 'code',sex,name,family,father_name as 'Father Name:',birth_date as 'Birth Date:',age as 'Age:',habit as 'Habit',p_surgery.surgery,p_surgery.date1 as 'Year of Surgery',p_vital_sing.pr as 'Present History- PR',p_vital_sing.rr as 'Present History- RR',p_vital_sing.t as 'Present History- T',lab.ca as 'Laboratory Finding- Ca',lab.ldh as 'Laboratory Finding- LDH',sonography.date1 as 'Sonography- Date of Sonography',sono_kidney.kidney as 'Pre 'Sonography- Kidney' from patients,P_habit,p_surgery,p_vital_sing,lab,sonogr aphy,sono_kidney where patients.patient_no>0 and patients.patient_no>600 and sex like 'Male%' and patients.patient_no=p_habit.patient_no and patients.patient_no=p_surgery.patient_no and p_vital_sing.t>37 and patients.patient_no=p_vital_sing.patient_no and lab.ldh>100 and patients.patient_no=lab.patient_no and patients.patient_no=sonography.patient_no and sono_kidney.kidney in ('Normal') and sonography.sono_id=sono_kidney.sono_id order by patients.patient_no

الان در برنامه دلفي براي ساختن اين كوئري حدود 1000 خطر كد نوشتم. اگه بخوام از روشهاي شما دوستان عزيز استفاده كنم و بسطش بدم. فكر كنم بايد چند هزار خط كد ديگه بنويسم.
آيا راه ساده تري وجود نداره؟ دستور خاصي تو SQL وجود نداره كه به جاي تكرار ركوردها براي هر درس در مثال فوق. درسها به رديف جلوي اون قرار بده؟
باز ممنونم

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

دستور خاصی وجود ندارد، شما شرایط را کمی پیچیده کردید، شما می توانید از PIVOT به عنوان یک دستور خاص یاد کنید.
دوست من، شما برای اینکه بتوانید به تعداد دلخواهی ستون به جدولت اضافه کنید می توانید از Dynamic PIVOT استفاده کنید.

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

فراموش کردم که بگم. خیلی عجیبه که برای ساخت یک query در حدود 20 جدول باید به یکدیگر متصل شوند. چرا که طبق گفته متخصصین پایگاه داده "اگر برای ساخت یک query بیش از 10 (ده) جدول را به یکدیگر متصل کردید باید دقت بیشتری بر نرمال سازی پایگاه داده ها داشته باشید."

بهتر بود که یک نمونه واقعی از محیط عملیاتی در پست اول قرار می دادید!

[oghab]

فراموش کردم که بگم. خیلی عجیبه که برای ساخت یک query در حدود 20 جدول باید به یکدیگر متصل شوند. چرا که طبق گفته متخصصین پایگاه داده "اگر برای ساخت یک query بیش از 10 (ده) جدول را به یکدیگر متصل کردید باید دقت بیشتری بر نرمال سازی پایگاه داده ها داشته باشید."

بهتر بود که یک نمونه واقعی از محیط عملیاتی در پست اول قرار می دادید!

از راهنمايي هاتون ممنونم
سعي ميكنم راجع به Dynamic PIVOT سرچ كنم و نحوه كاركردش را ياد بگيرم.
بعد من تمام سعي ام را براي نرمال سازي كردم. و اگه ممكنه از 20 جدول همزمان جستجو بشه. به خاطر اينه كه كارفرما ميخواد از سرچ برنامه كاملا انعطاف پذير باشه و از هر كدوم از فيلدها از هر جدولي و با هر شرطي كه خواست بتونه گزارش بگيره. و به چند جستجوي از پيش تعيين شده قناعت نميكنه. و علت تعداد زياد جدولها كه بيشتر از 20 تا است. موارد زياد و تفكيك شده اطلاعات پزشيكه. كه در خيلي از موارد تعداد متغيري ركورد براي هر جدول ثبت ميشه و ادغام جدولها با هم باعث افزونگي در پايگاه داده ميشه.
در مورد نمونه واقعي از مشكلي دارم معذورم. چون طبق قرارداد نبايد اطلاعاتي از برنامه و داده ها را جايي خارج از شركت مطرح كنم.
به هر حال از راهنمايي و وقتي كه گذاشتيد ممونم
سعي ميكنم روي Dynamic PIVOT كار كنم شايد بتونم به نتيجه برسم

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

یک لینک برای Dynamic PIVOT

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

من همین الان به یک روش دیگه دست یافتم.

چند لحظه ی دیگر در تاپیک پست میکنم.

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

select s_no,
max(case when rid = 1 then course end) as course1,
max(case when rid = 2 then course end) as course2,
max(case when rid = 3 then course end) as course3
from (Select row_Number() over (partition by S_No order by course) as RID,*
from Course) p
group by s_no






s_no course1 course2 course3
----------- ------------------------- ------------------------- -------------------------
1 math 1 phisisc 1 NULL
2 math 1 NULL NULL
3 culture history moadelat
Warning: Null value is eliminated by an aggregate or other SET operation.

(3 row(s) affected)

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

از چه نسخه SQL Server استفاده می کنید؟ اگر می خواهید از عملگر PIVOT استفاده کنید باید از نسخه 2005 به بالا استفاده کنید.

[oghab]

از چه نسخه SQL Server استفاده می کنید؟ اگر می خواهید از عملگر PIVOT استفاده کنید باید از نسخه 2005 به بالا استفاده کنید.

SQL Server 2000 :اشتباه:

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

نگران نباش. تمام سعی خود را خواهم کرد تا یک روش انعطاف پذیر (flexible) و مناسب (suitable) برای این

این مساله پیدا کنم.

در شرایطی که حد اقل توابع Ranking در SQL Server 2000 وجود داشته باشند!!!

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

متوجه شدید چی شد !؟

کل سناریو ریخت به هم.

چرا که در SQL Server 2000 نه Pivot و نه Rank function وجود ندارد!

حالا دو راه دارید: 1- بانک اطلاعاتی را در 2005/2008 پیاده سازی کنید.
2- کارفرما را راضی کنید که از خر شیطان پایین بیاد.

[oghab]

متوجه شدید چی شد !؟

کل سناریو ریخت به هم.

چرا که در SQL Server 2000 نه Pivot و نه Rank function وجود ندارد!

حالا دو راه دارید: 1- بانک اطلاعاتی را در 2005/2008 پیاده سازی کنید.
2- کارفرما را راضی کنید که از خر شیطان پایین بیاد.

سلام
مرسي از وقتي كه گذاشتيد و زحمتي كه كشيديد.
حالا حداقل ميدونم چه كاري بايد انجام بدم.
تا ببينم چي ميشه. با كارفرما صحبت كنم ببينم چي ميگه. و اگه قبول نكرد مجبورم پايگاه داده را انتقال بدم و در مورد Pivot و نه Rank function تحقيق كنم و يه جوري در كد وحشتناكي كه براي جستجو نوشتم جاش بدم.
ممنون

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

چرا کارفرما همچین نتیجه ای می خواهد!


به پست شماره 2 ی همین تاپیک یک نخواهی بکنید ایده خوبی است و برای پیاده سازی هیچ مشکلی نیست.

[oghab]

شاید بشوند با چندین LEFT OUTER JOIN یا چند subquery به خواستتون بررسید ولی من پیشهاد می کنم که یک ستون برای نام دانشجو و یک ستون برای لیست دروس انتخاب شده اش در نظر بگیرید.


ستون نام ستون لیست دروس
----------------------------------------------
دانشجوی 1 درس 1و درس 2و درس 3
دانشجوی 2 درس 2و درس 5



ولی اگر اصرار به انجام چنین کاری دارید بفرمایید تا بلکه سعی کنیم یک query تمیزی براش در بیاریم.
شرمنده. يعني چطوري بنويسم؟
اگه واسه همين مثال كدش را بنوسيد خودم ديگه يه كاريش ميكنم و سعي مينم بسطش بدم

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

خب، یکمی نیاز به توضیح دارد.

روش های متعددی برای پیاده سازی و حل این گونه موارد وجود دارد که حتی یک مقاله در این مورد نوشته شده به لینک زیر:
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

ولی یک راه ساده استفاده از یک UDF می باشد. که خودم از این روش استفاده می کنم.

یکمی فرصت می خواهم تا روی کامپیوترم پیاده سازی کنم.

چند لحظه دیگر کد تابع و استفاده از آن در SELECT را ارسال خواهم کرد.

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

کد برای تعریف (ایجاد) تابع:


CREATE FUNCTION [dbo].[CourseList]
(
@S_no NVARCHAR(50)
)
RETURNS NVARCHAR(2000)
AS
BEGIN
-- Declare the return variable here
DECLARE @Name NVARCHAR(2000)

-- Add the T-SQL statements to compute the return value here
select @Name=CASE
WHEN course IS NOT NULL
then COALESCE(@Name + ', ', '') + course
else COALESCE(@Name + '', '')
end
from dbo.Course
where s_no=@s_no

-- Return the result of the function
RETURN @Name

END


دستور بازیابی اطلاعات از جدول:



SELECT [Student Number]=S_no,
[Course List]=dbo.CourseList(S_no)
FROM Students


خروجی



Student Number Course List
-------------- ------------------------------
1 math 1, phisisc 1
2 math 1
3 culture, history, moadelat

[mpggcobol]

اگر پایگاه داده حاصل طراحی خودتون هست باید بگم که آنرمالی داره همین هم 1000 خط کد میخواهد
را هش هم اینه که شما یک جدول دروس اضافه کنید که شامل -کد درس-نام درس-واحد-نوع درس(عملی-تئوری)
بعد یک جدول باشه که s_no و Course,ترم -نمره باشه

حالا با دو تا join ساده همه اطلاعات را واکشی کنید