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

[oghab]

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


حالا اگه ما يه كوئري بنويسيم با اين كد

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

نتيجه به اين صورت ميشه:

يعني براي هر درس اسم و فاميل را تكرار ميكنه
حالا اگه ما بخواهيم نتيجه به اين صورت بشه و به جاي تكرار خودش ستون اضافه كنه


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

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

شاید بشوند با چندین 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/madhiv...rver-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...-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 ساده همه اطلاعات را واکشی کنید