PDA

View Full Version : سوال: تلفیق اطلاعات دو جدول مشابه



احمد سامعی
جمعه 26 شهریور 1389, 22:20 عصر
سلام

من دو تا جدول دارم که ساختار یکسانی دارن، در یکی اطلاعات به صورت موقت نگاه داری می کنم و بعد از تائید مدیر به جدول اصلی انتقال پیدا می کنند

از دو جدول استفاده کردم زیرا بعد از هر تغییر در هر رکورد دوباره باید اطلاعات توسط مدیر تائید بشه برای همین به جدول موقت انتقال پیدا می کن

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

با sp هم یک جدول موقت ایجاد کردم و اطلاعات اینزرت کردم من می خوام علاوه بر فیلدهای اصلی یک کلید هم برای این جدول ایجاد کنم هچنین یک فیلد که نام جدول توش قرار بدم چی کار کنم
و آیا راه ساده تری هست یا چطوری می تونم view برای این کار ایجاد کنم

این هم کدم

ALTER PROCEDURE dbo.mySp
@user nvarchar(256)
AS
create table #foo (
ID int NOT NULL,
userName nvarchar(256) NOT NULL,
title nvarchar(50) NOT NULL,
Description nvarchar(500) NULL,
address nvarchar(250) NOT NULL,
tell varchar(30) NULL,
fee int NULL
) ON [PRIMARY]

insert into #info SELECT * FROM tb WHERE userName = @user
insert into #info SELECT * FROM tbTemporary WHERE userName = @user

SELECT * from #info

drop table #info
RETURN

بهزادصادقی
جمعه 26 شهریور 1389, 22:51 عصر
اینجا چند تا مسئله وجود دارد.

اول اینکه، شما می خواهید نتایج دو تا دستور select را با هم تلفیق کنی. برای این کار، دارید از یک temporary table استفاده می کنید. نتایج هر دو select را می ریزید اون تو و بعد کل سطرهای آن جدول موقت را نشان می دهید. ولی راه خیلی آسان تری وجود دارد برای این کار، و آن استفاده از دستور union all می باشد که دقیقا کارش این است که نتایج دو select را با هم تلفیق کند. روش استفاده ازش هم این طوری است:



select * from Table1
union all
select * from Table2


در مرحله بعدی، شما می خواهید نتایج این عملیات فابل استفاده باشد در کدی که تقاضای دریافت این اطلاعات را کرده. برای اینکار، شما می توانید از یک user defined function استفاده نمائید که خروجی اش یک جدول است. بعد می توانید مثل این از آن استفاده نمایید:



select * from dbo.tbTalfighi( @uesr );


در اینجا، dbo.tbTalfighi اسم یک function است که یک پارامتر دریافت می کند به اسم user. نتیجه این function یک جدول است.

حال، کد dbo.tblTalfighi.



if object_id( 'dbo.tbTalfighi' ) is not null
drop function dbo.tbTalfighi;
go

create function dbo.TbTalfighi
(
@user nvarchar(256)
)
returns table
as
return
(
select 0 IsTemp, t.* from tb t where t.userName = @user
union all
select 1 IsTemp, t.* from tbTemporary t where t.userName = @user
);

go


من اینجا یک کاری کردم که شاید شما احتیاج نداشته باشی. من اومدم و یک ستون به نتیجه شما اضافه کردم که اسمش IsTemp می باشد. اگر مقدار این ستون 1 باشد یعنی این سطر از جدول tbTemporary آمده. اگر 0 باشد، یعنی از چدول tb آمده. اگر این ستون را لازم نداری، می توانی کد dbo.tbTalfighi را عوض کنی و آن را برداری.

احمد سامعی
جمعه 26 شهریور 1389, 23:28 عصر
ممنون جناب صادقی

استفاده از union نکته جالبی بود که بهش دقت نکرده بودم اما:

1. چرا در سلکت اول من where نکنم بعد از فانکشن استفاده کنم
2. من این فیلد اضافی می خوام که بدونم از این رکورد مربوط به کدام جدول هست اون رو هم چرا در سلکت اصلی نزارم

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

و نکته آخر اینکه من کل سطها رو نمی خوام به کاربر نشون بدم و صفحه بندی می کنم برای اینکار نیاز هست تعداد کل سطرهایی که در شرط صدق می کنه بازگشت بشه تا در درخواست صفحه بعدی استفاده بشه
در روشی که در پست اول گفتم به راحتی می شه تعداد سطرهای جدول موقت در sp به برای اینکار استفاده کرد اما در این روش چی کار باید کرد


ALTER PROCEDURE dbo.mySp
@PageIndex int OUTPUT,
@user nvarchar(256)
AS
DECLARE @StartIndex int
set @StartIndex = (@PageIndex * 20) + 1

DECLARE @EndIndex int
set @EndIndex = (@PageIndex * 20) + 20

SELECT * FROM
(SELECT ID, 'tb1' as tb, title, Description, address, tell, fee, row_id = ROW_NUMBER() OVER(ORDER BY ID DESC) FROM tb WHERE userName = @user
union all
SELECT ID, 'tbTemporary' as tb, title, Description, address, tell, fee, row_id = ROW_NUMBER() OVER(ORDER BY ID DESC) FROM tbTemporary WHERE userName = @user)
AS D WHERE D.row_id BETWEEN @StartIndex AND @EndIndex



SELECT @PageIndex = CAST(COUNT(*) AS INT) FROM #info

بهزادصادقی
شنبه 27 شهریور 1389, 00:16 صبح
نگاه کن، هیچ باید و شایدی اینجا وجود نداره. کاری را که شما می خواهید انجام دهید را از حدود 15000 راه مختلف می شود انجام داد. این هم یکی از راهایش است. اگر دوست داشتی که می تونی ازش استفاده کنی. دوست نداشتی، که خوب، استفاده نکن.

برای صفحه بند هم توی این متد شما یا می توانی شماره صفحه ای را که می خواهی به function به عنوان یک پارامتر پاس کنی و بعد درون function همان کاری را که الان داری با row_number انجام می دهی روی نتایج union شده دو select انجام دهی و یا اینکه می تونی خود function را به صورت یک جدول مجازی ازش استفاده کنی و یک ستون row_number روی نتایچی که بهت باز میگرداند محاسبه کنی تا کار صفحه بندی ات را اون طوری انجام دهی.

در کدی که شما نوشتی، شما از row_number داری روی هر دو select استفاده می کنی. اگر اشتباه نکنم این باعث می شود که ستون row_id متعلق به select اولیه ات شماره هاش از 1 شروع شوند تا تعداد نتایج select و همین هم دقیقا در مورد select دوم صدق خواهد کرد. در نتیجه، در سطرهایی که union all باز می گرداند، شما می توانید دو تا سطر با row_id برابر با 1 داشته باشید، دو تا سطر با 2 و غیره. اگر اشتباه نکنم، آن چه شما دنبالش هستی این است که شماره سطرها را بر اساس نتایج تلفیق شده هر دو select محاسبه کنید. اگر این درست است، باید row_number را به نتایج union اضافه کنی نه نتیجه هر select.

این هم نمونه کد با صفحه بندی توی خود function:


if object_id( 'dbo.tbTalfighi' ) is not null
drop function dbo.tbTalfighi;
go

create function dbo.TbTalfighi
(
@user nvarchar(256),
@pageIndex int,
@numRowsPerPage int
)
returns table
as
return
(
with CombinedResults as
(
select t.ID, 0 as IsTemp, t.title, t.Description, t.address, t.tell, t.fee from tb t where t.userName = @user
union all
select t.ID, 1 as IsTemp, t.title, t.Description, t.address, t.tell, t.fee from tbTemporary t where t.userName = @user
), NumberedCombinedResults as
(
select row_number() over( order by c.IsTemp, c.ID desc ) rn, c.* from CombinedResults c
)

select
n.ID,
case n.IsTemp when 0 then 'tb1' else 'tbTemporary' end tb,
n.title,
n.Description,
n.address,
n.tell,
n.fee,
n.rn row_id
from
NumberedCombinedResults n
where
n.rn between ( (@pageIndex-1) * @numRowsPerPage ) + 1 and ( @pageIndex * @numRowsPerPage )
);
go

احمد سامعی
شنبه 27 شهریور 1389, 08:55 صبح
ممنون جناب صادقی از وقتی که گذاشتید

یک سوال دیگه
من باید روی نتایج where که از جداول اصلی انجام دادم کارهای دیگه ای هم انجام بدم مثلاً جمع یک ستون به نام مبلغ
چطوری می تونم فقط یک بار جدول اصلی پیمایش کنم و بعد با نتیجه حاصل کارهای مختلف انجام بدم تا بار کمتری روی سرور ایجاد بشه

آیا اگر از view استفاده کنم این حالت انجام می شه منظورم اینکه هربار که ما view فراخوانی می کنی دوباره جدول های اصلی پیمایش می شه و نتایج در view قرار می گیره یا باید کار دیگه ای انجام داد مثلاً چیزی به عنوان "کش" در sql هم هست کاری که در asp انجام می دیدم

احمد سامعی
شنبه 27 شهریور 1389, 09:25 صبح
من کد شما رو تست کردم

مشکل ایجاست که من باید تعداد کل سطرهای صدق کننده در بخش where به متد فراخوان کننده بازگردانم تا بر اساس اون که کاربر نشان بدم چند صفحه اطلاعات هست اگر در پست سوم در خط دوم کدها دقت کنید من نوع PageIndex را output گرفتم

اما از فانکشن نتونستم مقدارش بازگشت بدم چون فقط یک return داره من می خوام علاوه بر سطرهای لازم، تعداد سطرهای CombinedResults بازگشت بدم

بهزادصادقی
شنبه 27 شهریور 1389, 17:28 عصر
من کد شما رو تست کردم

مشکل ایجاست که من باید تعداد کل سطرهای صدق کننده در بخش where به متد فراخوان کننده بازگردانم تا بر اساس اون که کاربر نشان بدم چند صفحه اطلاعات هست اگر در پست سوم در خط دوم کدها دقت کنید من نوع PageIndex را output گرفتم

اما از فانکشن نتونستم مقدارش بازگشت بدم چون فقط یک return داره من می خوام علاوه بر سطرهای لازم، تعداد سطرهای CombinedResults بازگشت بدم

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

یک اصلی وجود دارد توی مهندسی نرم افزار که به این نام شناخته می شود: The Single Responsibility Principle

این اصل می گوید که هر چیزی فقط باید یک کار بکند.

در کد شما، متغیر PageIndex دارد دو کار می کند. یکی کاری است که اسمش به ما می گوید این متغیر انجام خواهد داد و دیگری یک کاری است که اصلا ربطی به اسمش ندارد.

واژه های page index معنی شان یعنی «شماره یک صفحه در بین صفحات دیگر». PageIndex یکی از پارامترهای sp شماست و به نظر می رسد که کارش این است که سطرهای مربوط به یک صفحه درخواستی خاص را به کاربر برگرداند. درسته؟ تا اینحا همه چیز خوب است. ولی بعد از این، یکدفعه شما شروع می کنید به خلاف کاری. شما آمده ای و نوع این پارامتر را output تعیین کرده ای تا از آن برای برگرداندن یک داده خاص، که تعداد کل صفحات می باشد، نیز استفاده کنید. این یعنی برای دو کاری که هیج ربطی به هم ندارند دارید از یک متغیر استفاده می کنید. و تازه، اسم این متغیر طوری انتخاب شده که به هیچ وجه به من خواننده کد نمی گوید که کار دیگر این متغیر چیست. من کلمه output را در کد شما دیدم، ولی فکر کردم که این یک اشتباه لپی است. جرا، وقتی به اسم و کاربرد خود متغیر در کد نگاه کردم، دیدم که نقشش تعیین صفحه مورد نظر کاربر است. من از کجا باید بفهمم که شما می خواهید تعداد صفحات را با این متغیر برگردانید؟

ولی اگر اسم متعیرتان را این می گذاشتید:

PageIndexAsInputAndTotalNumberOfPagesAsOutput

آن وقت من دقیقا می توانستم ببینم که شما چه می خواهید.

یک راه که با استفاده از آن می توانید Single Responsibility Principle را رعایت کنید این است که یک پارمتر به لیست پارامتر های sp خود اضافه کنید:



ALTER PROCEDURE dbo.mySp
@PageIndex int,
@user nvarchar(256),
@TotalNumberOfPages int output
AS


همانطور که خود اشاره کردید، چون شما باید دو نوع مختلف از داده ها را به کاربر برگردانید، دیگر استفاده از function میسر نمی باشد و شما باید از یک stored procedure استفاده نمائید.

مگر اینکه دو تا function تعریف کنید. یکی که تعداد سطرها را باز می گرداند و دیگری که سطرهای صفحه مورد نظرتان را. استفاده از تابع این مزیت را دارد که شما می توانید در کد خود روی نتایجی که آن function دارد باز می گرداند پردازش انجام دهید، درست مثل اینکه آن تابع خودش یک جدول یا view است.

یک راه دیگری هم که دارید، اگر دوست دارید از یک تابع استفاده کنید ولی هنوز تعداد کل صفحات را از آن تابع می خواهید این است که آن تعداد را در بدنه تابع حساب کنید و بعد به عنوان یک ستون به نتایج تابع اضافه کنید. تنها مشکل این است که آن وقت یک ستون اضافه خواهید داشت که فقط یک مقدار در آن وجود دارد و برای هر سطر دارید یک بار محاسبه اش می کنید.