بدست آوردن بیشترین مقدار جا افتاده در یک range

[saeed6162]

سئوالی داشتم، چطوری میشه در فیلد کد (شامل یکسری اعداد بین 0 الی 999) بیشترین مقدار جا افتاده ( وجود نداره ، یا گپ هست) رو بدست آورد؟
نکته: حتی الامکان از جدول temporary استفاده نشود.

مثال : فرض کنید، فیلد کد دارای مقادیر زیره:
1
15
999
750

بیشترین مقدار جا افتاده : 998

[hamid_hr]

select max(id) - min(id) from tbl

[saeed6162]

select max(id) - min(id) from tbl

نه این جواب درستی نیست!

[hamid_hr]

خب سوالت واضح نیست

[saeed6162]

خب سوالت واضح نیست
فکر می کنم که سئوال به اندازه کافی واضح باشه!
اما خب دوباره میگم ، خیلی خلاصه تر و خیلی ساده تر : بیشترین عددی که بین 0 تا 999 در ردیف کد وجود نداره (یعنی جا افتاده ، یا گپ هست) رو برگردونه.

[starting]

راه دیگه ای به ذهنم نمیرسه.
باید یک جدول داشته باشید که شامل اعداد 0 تا 999 باشه. سپس سطرهایی از این جدول که داده ی متناظر آن در جدول دیگه (اصلی) وجود نداره را بدست بیاری (یعنی همون مقادیر جاافتاده) سپس ماکسیمم این مجموعه از داده ها که باقی موندن رو میگیری به این شکل:

SELECT MAX(number) FROM NumberTable
WHERE number NOT IN (SELECT number FROM MainTable)

اما می پرسی چطور داده های اون جدول Number را از 0 تا 999 پر کنم؟ میگم به سادگی:

CREATE TABLE dbo.Numbers (Number integer NOT NULL IDENTITY(0, 1) PRIMARY KEY)
GO
INSERT INTO dbo.Numbers DEFAULT VALUES
Go 1000


کد فوق ممکنه 1 تا 2 ثانیه زمان بگیره. اگر این زمان با این حجم از داده ها برایتان رضایت بخش هست که هیچ در غیر اینصورت راه های بسیار موثرتری برای این کار وجود داره. من فقط خواستم یک راه حلی ساده و دم دستی برات بذارم.

حالا من سوالی از شما دارم. این مقدار را به چه منظور احتیاج دارید؟

[N_D]

Create table #T1( A int )
Insert #T1 Values (3),(23),(53),(998),(999);

SELECT TOP 1 GAP
FROM(
SELECT
Gap=a-1,a,a - LAG(A)Over( order by a) as b
FROM #T1
) K
WHERE b<>1
ORDER BY GAP DESC

Drop table #T1

[starting]

بدون دو مقدار آخر کوئری خودتان را اجرا کنید نتیجه باید 999 باشد اما 52 خواهد بود (این برداشت من از صورت مساله هست شاید منظور و مقصود پرسش گر آن چیزی باشه که شما برداشت کردید).

3
23
53

[N_D]

اگه صورت مسئله اون چیزی باشه که شما میگین اونوقت کوئری زیر جواب درستیه


SELECT TOP 1 GAP
FROM(
SELECT
Gap=A-1,A,A - LAG(A)Over( order by A) as B
FROM(
SELECT A FROM #T1
UNION
SELECT 1000 -- MaxRange
)Tbl

) K
WHERE B<>1
ORDER BY GAP DESC

[saeed6162]

بدون دو مقدار آخر کوئری خودتان را اجرا کنید نتیجه باید 999 باشد اما 52 خواهد بود (این برداشت من از صورت مساله هست شاید منظور و مقصود پرسش گر آن چیزی باشه که شما برداشت کردید).

3
23
53
ممنون، بله دقیقا همین عملکرد رو میخواستم، درسته جواب میده.
درمورد سئوالی که کردید و گفتید، برای چی میخوام، باید عرض کنم که برنامه ای نوشتم که باید برای یک موجودیت کد غیرتکراری اختصاص بدم و این کد از 999 شروع میشه و به پایین (مقادیرکمتر) میاد. در مواردی که موجودیتی که از قبل تعریف شده و کد اون رو نباید به یک موجودیت جدید اختصاص بده، یعنی فقط از مقادیر گپ باید استفاده بشه.
یک سئوال دیگه، روش سریعتری وجود داره؟ ساخت جدول برای هر بار کد اختصاص دادن درست به نظر نمیاد!

[saeed6162]

اگه صورت مسئله اون چیزی باشه که شما میگین اونوقت کوئری زیر جواب درستیه


SELECT TOP 1 GAP
FROM(
SELECT
Gap=A-1,A,A - LAG(A)Over( order by A) as B
FROM(
SELECT A FROM #T1
UNION
SELECT 1000 -- MaxRange
)Tbl

) K
WHERE B<>1
ORDER BY GAP DESC

متاسفانه نتونستم اجراش کنم! اگه ممکنه راجع به اسم گذاری هایی که کردید راهنمایی هم بذارید.
در ضمن سروری که الان باهاش کار می کنم 2000 هست.

[starting]

درمورد سئوالی که کردید و گفتید، برای چی میخوام، باید عرض کنم که برنامه ای نوشتم که باید برای یک موجودیت کد غیرتکراری اختصاص بدم و این کد از 999 شروع میشه و به پایین (مقادیرکمتر) میاد. در مواردی که موجودیتی که از قبل تعریف شده و کد اون رو نباید به یک موجودیت جدید اختصاص بده، یعنی فقط از مقادیر گپ باید استفاده بشه.
درخواستتون یکم برام غیر معمول هست.
اما اگر می خواهید اولین گپ بزرگ را بدست بیارین این روش این کار را براتون انجام میده و دیگه نیاز به جدول Number که در راه حل قبلی قرار داشت نیست:

Use TempDB
create table tablename (value int)
insert tablename values (3),(50),(998),(999)

SELECT Max(value - 1) as TheGap
FROM tableName
WHERE value - 1 NOT IN
(SELECT value
FROM tableName)


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


متاسفانه نتونستم اجراش کنم! اگه ممکنه راجع به اسم گذاری هایی که کردید راهنمایی هم بذارید.
در ضمن سروری که الان باهاش کار می کنم 2000 هست.
قابلیت های بکار رفته در آن کوئری در نسخه 2012 قابل استفاده است