پیدا کردن شماره های ناموجود

[S_O_S1982]

سلام
اگه سوالم تکراری هست عذر خواهی می کنم

من یه دیتابیس دارم که تو یکی از فیلدهای اون شماره ثبت ها رو پشت سر هم به کالاها دادم ، حالا وقتی کالایی تو یکی از مراحل مشکلی داشته ، شماره ثبت به اون اختصاص داده شده اما تو دیتابیس ذخیره نشده ، مثلا الان فیلد شماره ثبت من به این صورت هستش


Regnumber
-------------
1
2
3
6
7
9
10


مثلا الان من شماره ثبت های 4 - 5 - 8 رو تو فلیدهام ندارم
حالا من می خوام یک کوئری بنویسیم که بهم شماره ثبت هایی اختصاص داده نشده یعنی تو مثال بالا همون 4-5-8 و برام در بیاره ،اما نمی دونم باید چی کار کنم

ممنون از توجه شما

[mehrpars]

مشکلت چیه ؟؟ اگر رنج اعدادو داری دیگه مشکلی نیست ، با Not In میتونی بنویسیش
رنج کاملتو نمی تونی در بیاری ؟؟؟

[ASKaffash]

سلام
چرا از کرسر استفاده نمی کنید؟ یک کرسر را پیمایش و با یک شمارنده مقایسه کنید

[Iran58]

سلام
چرا از کرسر استفاده نمی کنید؟ یک کرسر را پیمایش و با یک شمارنده مقایسه کنید
سلام
مهندس جان ميشه بايك مثال (سوال همين تايپيك)كاركرسرراتوضيح دهيد

[S_O_S1982]

از توجه دوستان خوبم ممنونم


فقط لطفا برام مثال بزنید چون زیاد وارد نیستم

در مورد جواب دوستی که گفته بودن رنج اعداد و رو اگه داری ، از not in استفاده کن ، خدمتشون عرض کنم که رنج اعدا بین 1 تا 1000,000 هستش

در مورد جواب دوست دیگه ای هم که فرموده بودند از کرسر استفاده کننم ، خواهش می کنم ازشون با یک مثال برم توضیح بدن

با تشکر مجدد

[ASKaffash]

سلام
در این مثال اولین مقدار برگشت می شود :


Declare @i Tinyint,@L Tinyint
Set @i = 0
Declare TmPCursor Cursor For
Select FID From T1 Order By FID
Open TmPCursor
While 1=1 Begin
Fetch Next From TmPCursor Into @L
If @@Fetch_Status<>0 Break
Set @i=@i+1
if @i<>@L Begin
Select @i
Break
End
End
Close TmPCursor
Deallocate TmPCursor

[S_O_S1982]

دوستان دیگه نمی تونن به من کمک کنن؟

[S_O_S1982]

سلام
در این مثال اولین مقدار برگشت می شود :


Declare @i Tinyint,@L Tinyint
Set @i = 0
Declare TmPCursor Cursor For
Select FID From T1 Order By FID
Open TmPCursor
While 1=1 Begin
Fetch Next From TmPCursor Into @L
If @@Fetch_Status<>0 Break
Set @i=@i+1
if @i<>@L Begin
Select @i
Break
End
End
Close TmPCursor
Deallocate TmPCursor



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

[ashkan209]

دیتابیست چیه ؟ access یا sql ؟
تا کدشو برات بنویسم

[ASKaffash]

سلام
این عدد اولین عددی است که در بانک خالی است مثلا 1و2و3 و5 داریم عدد 4 را پیدا می کند شما این کد را دستکاری کنید تا همه اعداد را برای شما ارائه دهد (اصل روش است)

[S_O_S1982]

سلام

نه ، این عدد اولین عددی نا موجود نیست ، در واقع به همین خاطر پرسیدم برام مفهوم نیست عدد
منطقی که توضیح دادین درست هست ، اما این کد این کار و فکر نکنم انجام بده

[S_O_S1982]

سلام
این عدد اولین عددی است که در بانک خالی است مثلا 1و2و3 و5 داریم عدد 4 را پیدا می کند شما این کد را دستکاری کنید تا همه اعداد را برای شما ارائه دهد (اصل روش است)


نه دوست گرامی ، این عدد اولین عدد ناموجود نیست ، منطق الگوریتمی که توضیح دادین درست هست ، اما این کد به نظر من این کار رو نمی کنه

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

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

حالا من می خوام یک کوئری بنویسیم که بهم شماره ثبت هایی اختصاص داده نشده یعنی تو مثال بالا همون 4-5-8 و برام در بیاره ،اما نمی دونم باید چی کار کنم
با یکی از روشهای ارائه شده در این مقاله (http://www.30sharp.com/ShowArticle.aspx?nid=13&did=216&AuthorID=11)یک جدول اعداد ایجاد کنید. سپس این کوئری را اجرا کنید:

SELECT nbr FROM Nums WHERE nbr NOT IN(SELECT ID FROM t)--d

ویرایش:
کوئری کامل شده:

SELECT nbr FROM nums WHERE nbr BETWEEN (SELECT MIN(ID) FROM T) AND (SELECT MAX(ID) FROM T)--d
AND nbr NOT IN (SELECT ID FROM T)--d

[goolestan]

یه راه راحتتر هم داره



create table ##Temp(TempeFiled int)
Declare @min int
Declare @max int
Declare @Counter int
set @Counter
select @min=min(fildname) from Tablename
select @max=max(fildname) from Tablename
set @Counter =@min
Whiel @Counter >=@max
begin
Insert into ##Temp(TempeFiled) values(@Counter)
set @Counter=@Counter+1
end
select * from ##Temp
where TempFiled Not in (select FiledName from Tambl1) --ok


با این میتونی این مشکلتو حل کنی

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

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

[ASKaffash]

سلام
چرا فکر می کنید کرسر قدیمی است؟ provider های Native Client و OLE-DB ها از کرسر ها نیز استفاده می کنند در ضمن مستندات ارائه دهید

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

سلام
چرا فکر می کنید کرسر قدیمی است؟ provider های Native Client و OLE-DB ها از کرسر ها نیز استفاده می کنند در ضمن مستندات ارائه دهید

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

راجب مستندات، کافیه کتاب های ماکروسافت را مطالعه کنید.
اگر هم فکر می کنید روش های cursor-based در مقابل روشهای set-based حرفی برای گفتن دارند بروید و در سایت های بین المللی با همین روش، راه حل ارائه دهین تا عکس العمل Expert ها را در مقابل Reply هایتان ببینید.

[ASKaffash]

سلام
فرق ID و nbr در مثال ذیل چیست ؟
SELECT nbr FROM Nums WHERE nbr NOT IN(SELECT ID FROM t)

[ASKaffash]

سلام
جواب Provider ها ؟

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

فرق ID و nbr در مثال ذیل چیست ؟
SELECT nbr FROM Nums WHERE nbr NOT IN(SELECT ID FROM t)
ID همان Regnumber ای است که کاربر در پست اول به آن اشاره کرده است.
nbr نام ستون جدول اعداد است.

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

جواب Provider ها ؟
چیزی راجب این Provider نمی دانم و تجربه ای ندارم.

[ASKaffash]

سلام
من از کوئری شما جواب نگرفتم لطفا شما برای این اعداد کوئری را تست کنید :
F
---
1
2
3
5
7
8
9
13
14
20
باید نتیجه بشود:
4
6
10
11
12
15
16
17
18
19

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

declare @t table(i int)
insert @t values
(1),
(2),
(3),
(5),
(7),
(8),
(9),
(13),
(14),
(20)

SELECT nbr
FROM (select row_number() over(order by (select null)) from sys.columns )nums (nbr)
WHERE nbr BETWEEN (SELECT MIN(i) FROM @t) AND (SELECT MAX(i) FROM @t)--d
AND nbr NOT IN (SELECT i FROM @t)--d

/*
nbr
--------------------
4
6
10
11
12
15
16
17
18
19
*/

[ASKaffash]

چیزی راجب این Provider نمی دانم و تجربه ای ندارم.
سلام
سلام
دات نت در Native Clinet 10 که روی دات نت 2008 سوار است (ADO.net) از کرسر ها نیز برای fetch استفاده می کند و خروجی را بعنوان یک DataTable در یک DataSet ارائه میدهد من تعجب می کنم که کی کرسر ها قریمی شده است؟ درست است که کرسرها در شرایط مساوی کندتر هستند ولی قدرت برنامه نویسی شبیه یک زبان برنامه نویسی و مدیریت تک تک رکوردها را فراهم آورده اند که واقعا نظیر ندارد در اصل اگر کرسرها نباشند در خیلی از موارد در پروژه ها باید دست به دامن زبان برنامه نویسی شویم و یک DataTable را پیمایش و نتیجه را استخراج کنیم.

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

منظورم از قدیمی این نیست که دیگه منسوخ شده منظورم این هست که در هر مساله ای نباید نیازمان را با این مقوله برطرف کنیم. قطعا کرسر یک امکان فوق العاده است که موارد استعمال خاص خودش را دارد. بیشتر منظورم من این است که در اینگونه مسائل/موارد بهتر است از روشهای ساده تر و اصولی تر مساله حل شود.

[ASKaffash]

منظورم از قدیمی این نیست که دیگه منسوخ شده منظورم این هست که در هر مساله ای نباید نیازمان را با این مقوله برطرف کنیم. قطعا کرسر یک امکان فوق العاده است که موارد استعمال خاص خودش را دارد. بیشتر منظورم من این است که در اینگونه مسائل/موارد بهتر است از روشهای ساده تر و اصولی تر مساله حل شود.

سلام
انصافا روش را تست کردم خوب بود:تشویق: و بدنیست بحث را ادامه دهیم ولی قبلش اینکه عموما بچه ها از کرسر ها وحشت دارند و نمی دانند که در زبان برنامه نویسی روی یک DataTable یا ADO قدیمی در VS6 دارند استفاده میکنند (MoveNext و یا ایندکسر روی یک Datarow شبیه است) ولی ادامه بحث :
می خواستم بدونم هزینه IO و تعداد دفعات Scan جدول در کوئری شما چقدر است ؟
چون Exection Plan زیاد table Sacn میدهد

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

روزهای اولی که با SQL Server و کلا زبان SQL آشنا شدم یک فرد از من خواست که تعدادی سطر را با یکدیگر الحاق کنم من هم چون با کرسر آشنا شده بودم فورا رفتم سراغ کرسر و یک راه حل برایش ارائه دادم. ولی بعد از اینکه بیشتر با قدرت و قابلیت T-SQL آشنا شدم یادم نمی یاد دیگه از کرسر استفاده کرده باشم. و الان همانطوری که شما اشاره کردین وحشت عجیبی نسبت به کرسر پیدا کردم:ناراحت: ولی چون دیگه طرفش نمی رم برایم تفاوتی نمی کنه که syntax اش یادم هست یا نه :لبخندساده:
راجب بدست آوردن هزینه ی I/O و Estimated Subtree Cost و Elapsed CPU Time و ... روی داده های تصادفی زیاد با شاخص مطلوب، اجازه بدین سر فرصت اینکار را انجام بدهم. الان کمی نیاز به Have a cup of coffee دارم!

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

می خواستم بدونم هزینه IO و تعداد دفعات Scan جدول در کوئری شما چقدر است ؟
چون Exection Plan زیاد table Sacn میدهد
مقدمه:
این راه حل بر اساس "جدول اعداد" پایه ریزی شده است.
(تعریف جدول اعداد: جدولی است با یک ستون از نوع عدد صحیح که دارای مقادیری از 1 تا N است و این مقادیر به ترتیب هستند یعنی 1 و 2 و 3 و ... . نام کامل این جدول، جدول کمکی اعداد متوالی (auxiliary sequence numbers table) است )

به هر Database Developer توصیه می شود که یکبار برای همیشه این جدول را ایجاد کند و در پروژه هایش از آن استفاده کند. (مساله های متعددی با این جدول اعداد حل میشود که یک نمونه اش مساله ی مورد نظر به نام Missing Numbers است)

برای تولید جدول اعداد راه حل های بسیار بسیار متنوع و متعددی وجود داره که می تونید به این مقاله رجوع کنید:
http://www.30sharp.com/ShowArticle.aspx?nid=13&did=216&AuthorID=11

پس از اینکه این جدول ساخته شد یک Clustered Index روی ستون Nbr ایجاد می کنیم.

مساله
پس از تولید و انتشار جدول اعداد یک جدولی با نام missing_numbers ایجاد می کنیم که دارای یک ستون بوده و مقادیر تصادفی بین 1 تا 1 میلیون را در خود نگهداری می کند. تعداد سطرهای این جدول را یک صد هزار تا در نظر گرفته ام.
بعد از این مرحله کوئری را اجرا می کنم و Plan و موارد دیگر را مورد بررسی قرار می دهم.

من از روش زیر جدول اعداد را ایجاد کردم که در حدود 5 ثانیه 1 میلیون سطر را تولید و در جدول مقصد درج کرد:


;WITH C(i) AS
( SELECT 1
UNION ALL
SELECT i + 1 FROM C WHERE i < 100 )
SELECT ROW_NUMBER() OVER(ORDER BY C1.i) AS Nbr
INTO Nums
FROM C AS C1
CROSS APPLY
C AS C2
CROSS APPLY
C AS C3;

ساخت ایندکس که در حدود 2 ثانیه زمان برد:


CREATE CLUSTERED INDEX IX_1 ON Nums (Nbr ASC);


حالا جدول مورد نظر را ایجاد و یک صد هزار داده ی تصادفی در رنج 1 تا 1 میلیون در آن درج می کنیم:


CREATE TABLE missing_numbers
(
recID INT NOT NULL PRIMARY KEY CLUSTERED
);
INSERT INTO missing_numbers (recID)
SELECT DISTINCT TOP 100000 ABS(CHECKSUM(NEWID())) % 1000000 + 1
FROM Nums;


کوئری:


SELECT Nbr
FROM Nums
WHERE Nbr BETWEEN (SELECT MIN(recID) FROM missing_numbers)
AND (SELECT MAX(recID) FROM missing_numbers)
AND Nbr NOT IN (SELECT recID FROM missing_numbers);


Plan:
چون در کوئری فوق نیاز به خواندن چهار منبع وجود دارد (سه subquery که از داده ها را از جدول missing_number می خوانند و کوئری بیرونی که داده ها را از جدول Nums می خواند)
پس چهار گره ی مربوط به Table Scan یا Index Scan یا Index Seek و ... داریم.
در سه سابکوئری که تمام داده ها به طور کامل از جدول missing_numbers خوانده می شوند و چون در آن جدول یک Custered Index وجود دارد پس بدیهی است که Clustered Index Scan اتفاق افتد.
و چیزی که خیلی همیت دارد این است که Optimizer چگونه داده ها را از جدول Nums می خواند. پس از اجرای کوئری من Clustered Index Seek را مشاهده کردم پس هیچ Scan ای اتفاق نیوفتاده است!
http://www.barnamenevis.org/forum/attachment.php?attachmentid=50190&stc=1&d=1275402760

(برای مشاهده ی Graphical Plan کافیه Script های فوق را اجرا کنید. چون Win Rar نداشتم نتونستم Plan را ضمیمه کنم)

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


SELECT Nbr
FROM Nums
WHERE Nbr BETWEEN (SELECT MIN(recID) FROM missing_numbers)
AND (SELECT MAX(recID) FROM missing_numbers)
AND Nbr NOT IN (SELECT recID FROM missing_numbers);

SELECT Nbr
FROM Nums N
LEFT JOIN missing_numbers m
ON n.Nbr = m.recID
WHERE Nbr BETWEEN (SELECT MIN(recID) FROM missing_numbers)
AND (SELECT MAX(recID) FROM missing_numbers)
AND m.recID IS NULL;

SELECT Nbr
FROM Nums N
WHERE Nbr BETWEEN (SELECT MIN(recID) FROM missing_numbers)
AND (SELECT MAX(recID) FROM missing_numbers)
AND NOT EXISTS (SELECT recID FROM missing_numbers
WHERE recID = N.Nbr);

[ASKaffash]

سلام
در واقع به نظر شما اگر جدول اعداد را داشته باشیم که روی فیلد مورد نظر Index وجود داشته باشد دیگر موضوع Scan اتفاق نمی افتد ؟اگر Ok است قبول ولی یک سئوال :
در پروژه های واقعی که کاربر یک جدول دارد و جدول اعداد وجود ندارد (عملا هم همینطور است) زمان ایجاد جدول در یک پروژه یک گلوگاه نیست ؟

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

Index Scan زمانی اتفاق می افتد که در شرط WHERE عبارت ها (ستونها) به عنوان SARG محسوب نشوند (مثلا بجای Name = 'Ali'--d بنویسیم STUFF(Name,1,1,'') = 'Ali'--d) یا تمام ستون های Target را پوشش (Cover) ندهند.

جدول اعداد باید شبیه به یک جعبه ابزار در کنار هر Developer ای وجود داشته باشد. ما تماما داریم در مورد سناریوهای واقعی صحبت می کنیم. زمانی که شمای جداول بانک طراحی و ایجاد می شوند، View ها و SP ها را ایجاد می کنیم این جدول هم در کنار تمام این اشیاء یکبار برای همیشه ایجاد می کنیم.
چون این جدول یکبار قرار هست ایجاد شود برای همیشه فکر نمی کنم 5 ثانیه زمان مدت طولانی باشد.