ستون تجمعي

[behroz1387]

دوستان عزيز با سلام
سال گذشته يك تايپيك در اين قسمت ديدم كه در آن سوالي درباره ستون تجمعي به اين صورت شده بود كه اگر يك ستون به اينصورت داشته باشيم كه شامل اعداد
100 1
200 2
250 1
50 2
باشد
ستون ديگري ايجاد كرد كه شامل اعداد
100 1
200 2
350 1
250 2
شود
اما هرچه مي گردم پيدا نمي كنم
اگه كسي جواب را مي داند و يا تايپيك قبلي را مي داند ممنون مي شم كه كمك كند

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

سلام، امکان داره بگید که دقیقا به چه چیزی احتیاج دارید؟ سوال نا مفهومه!

[behroz1387]

دوست عزيز
من يك ستون كه متشكل از جمع سطرهاي ستون ديگري باشد
مثلا
ستون 1
100
200
50
150
50
ستون تجمعي
100
300
350
500
550
ممنون از توجهتان

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

یعنی جمع سطر فعلی با مقادیر سطرهای قبلی.


Set NoCount On
Declare @Sample Table
(
Col1 int
)

Insert Into @Sample Values (100)
Insert Into @Sample Values (200)
Insert Into @Sample Values (50)
Insert Into @Sample Values (150)
Insert Into @Sample Values (50)


Select Col1, Identity(int,1,1) as id
Into #New_table
From @Sample

Select Col1
, Col2 = (Select Sum(Col1)
From #New_Table
Where id <= N.id)
From #New_Table N

/*
Col1 Col2
----------- -----------
100 100
200 300
50 350
150 500
50 550
*/
Drop Table #New_Table

[behroz1387]

دوست عزيز من كد شما را تست كردم جواب داد
اما در جدولي كه من داراي مقدار بودم هر كاري كردم اروور مي داد
نام جدولم nm و داراي ستون idو col1 است اگه زحمت نيست كد را طوري تغيير بديد كه با اطلاعات جدول كار كنه و يا راهنمايي بفرماييد
ممنون

[FSarab]

create table #Test
(
FID int identity(1,1),
FValue numeric
)

insert into #Test(FValue) values(100)
insert into #Test(FValue) values(200)
insert into #Test(FValue) values(50)
insert into #Test(FValue) values(150)
insert into #Test(FValue) values(50)

select FID, FValue, (select sum(FValue) from #Test A where (A.FID <= B.FID)) as Total from #Test B
order by B.FID

drop table #Test

[behroz1387]

دوست عزيز اين كه همان قبلي است اگه مي شه كمي توضيح بده

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

اما در جدولي كه من داراي مقدار بودم هر كاري كردم اروور مي داد
نام جدولم nm و داراي ستون idو col1 است اگه زحمت نيست كد را طوري تغيير بديد كه با اطلاعات جدول كار كنه و يا راهنمايي بفرماييد


از آنجایی که من با تصور اینکه جدول شما دارای یک ستون از نوع increment نبود از تابع Identity استفاده کردم، نکته اینجاست که همزمان نمی توانیم هم ستون identity داشته و هم از این تابع استفاده کنیم.

امکان داره پیغام مربوط به خطا و سکریپت کد جدول را ارسال کنید؟

[behroz1387]

دوست عزيز نام جدولم nm و اين جدول داراي يك ستون به نام col1 است اين ستون از قبل داراي اطلاعات است حال من مي خواهم يك ويو داشته باشم كه اين ستون را همراه با ستون تجمعي نشان دهد از كد شما به اين صورت استفاده كردم
DECLARE @nm tabel(Col1 int) SELECT Col1, IDENTITY (int, 1, 1) AS id
INTO #New_table
FROM @nm
SELECT Col1, Col2 =
(SELECT Sum(Col1)
FROM #New_Table
WHERE id <= N .id)
FROM #New_Table N DROP TABLE #New_Table

اگه ممكنه كمك كنيد ممنون

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

بهتر بود نسخه ی SQL Server ای که داری استفاده می کنی هم ذکر می کردی.
این راه حل برای نسخه های 2005 و بالاتر است.


Declare @useful table (col1 int, rowid int)

Insert Into @useful
Select Col1,
RowID = ROW_NUMBER() Over(Order By 1)
From nm


Select Col1
, Col2 = Col1 + (Select Sum(Col1)
From @useful AS U1
where U1.rowid < U2.rowid)
From @useful AS U2


ویرایش:
بجای Over(order by 1) از یکی از دو کد زیر استفاده کنید:


over (order by rand())
over (order by getdate())

[behroz1387]

sql 2005 استفاده مي كنم
دوست عزيز با تشكر از شما ولي ممكنه بگيد چرا سطر اول را بصورت null نشان مي دهد
باز هم از توجهتان ممنونم

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

به پست ویرایش شده شماره 10 مراجعه کنید.
نام جدول : nm
نام ستون col1

[behroz1387]

دوست عزيز با تشكر از شما ولي ممكنه بگيد چرا سطر اول را بصورت null نشان مي دهد
باز هم از توجهتان ممنونم

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

مطمئن هستید که محتوای ستون col1 سطر اول جدولتان برابر با null نیست؟

در ضمن ممکنه بگید که هدفتان از گرفتن همچین نتیجه ای چیه؟

[behroz1387]

استاد عزيز من كد شما را به اين صورت تغيير دادم مشكلم حل شد
DECLARE @nm TABLE (col1 int, id int) INSERT INTO @nm
SELECT Col1, id = ROW_NUMBER() OVER (ORDER BY getdate())
FROM nm
SELECT id, Col1, Col2 =
(SELECT Sum(Col1)
FROM @nm AS U1
WHERE U1.id <= U2.id)
FROM @nm AS U2


ببخشيد كه فقط يكبار تشكر كردم چون ارزش كارتان ارزش هزاران بار تشكر را داشت

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

ولي ممكنه بگيد چرا سطر اول را بصورت null نشان مي دهد


حالا یادم افتاد مشکل از کجاست! وقتی که در سطر اول در قسمت subquery هیچ مقداری بر نمی گردد به جاش null بر می گردد و هر عددی با null جمع بشود حاصلش هم null خواهد شد. یعنی Col1 + Subquery

یعنی نتیجه دستور زیر برابر با null است.
select 1 + null

[behroz1387]

ببخشيد آخرين سوال
وقتي كه مي خواهم view را ذخيره كنم اين پيغام را مي ده

Incorrect syntax near the keyword 'DECLARE'

به نظر شما مشكل كجاست چون اگه ذخيره نشه كل زحمتمان هدر مي ره ممنون

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

UDF دقیقا کار View را می تواند انجام بدهد حتی بسیار قدرت مند تر.
برایتان یک UDF ایجاد کردم که در دستور Select به شکل زیر از آن استفاده کنید.




create function fun_view () returns @dd table
(
id int primary key,
col1 int,
col2 int
)
as
begin
DECLARE @nm TABLE (col1 int, id int)
--Declare @dd Table (col1 int , col2 int)

INSERT INTO @nm
SELECT Col1, id = ROW_NUMBER() OVER (ORDER BY getdate())
FROM nm

Insert Into @dd
SELECT id
, Col1
, Col2 = (SELECT Sum(Col1)
FROM @nm AS U1
WHERE U1.id <= U2.id)
FROM @nm AS U2

Return;
end
Go

Select *
From dbo.fun_view()

[behroz1387]

دوست عزيز من اين كد را كجا قرار دهم
و مشكل قبلي قابل حل نيست
ممنون

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

دوست عزيز من اين كد را كجا قرار دهم
شما همیشه برای ساخت view از ابزار query builder استفاده می کنین؟

این تابع را در یک پنجره جدید query کپی-پیست کرده سپس اجرایش کنید (با کمکه دکمه execute). توجه داشته باشید که قبل از اجرای کد، بانک مورد نظر خود را انتخاب کنید.

بعد از ایجاد تابع (که خروجی جدول دارد) می توانید آن را در ماده From یک عبارت Select استفاده کنید.


و مشكل قبلي قابل حل نيست
با این تابعی که برایتان قرار دادم دیگر نیازی به ساخت ویو ندارین.
در view ما محدودیت هایی داریم.

[behroz1387]

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

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

چرا نتوانستید استفاده کنید؟ توجه داشته باشید که من هر کدی را که پست می کنم اول روی سیستم خودم اجرا می کنم.

تابع با خروجی جدول دقیقا کار view را انجام می دهد.

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

[behroz1387]

دوست گرامي من يكسري اطلاعات دارم كه شامل ماه و مقدار است مي خواهم با وارد كردن ماه خاصي دو سطر مقدار آن ماه خاص و تجمعي تا آن ماه را به من بدهد
اگه راه حل بهتري به نظرتان مي رسد به ديده منت
در ضمن به علت اينكه داراي چندين محصول هستم متناسب آن نيز چندين جدول به اينصورت بايد بسازم



ممنون

[مهدی نان شکری]

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



With MyCTE
as
(
Select (Row_Number() Over (Order By GetDate())) as RowNum,* From TABLENAME
)

select
M,
(Select Sum(V) From (Select M,V,(Row_Number() Over (Order By GetDate())) as RN From TABLENAME) T1
Where T1.M=MyCTE.M And T1.RN<=MyCTE.RowNum) as S
From MyCTE



منظور از TableName نام جدول شماست و M منظور ماه و V منظور مقداری که باید جمع بشه

امیدوارم مشکلت حل بشه

[behroz1387]

Where T1.M<=MyCTE.M And T1.RN<=MyCTE.RowNum) as S


دوست عزيز ممنون مشكلم حل شد فقط در كد > را فراموش كرده بوديد

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

اولش خواستم از دو Derived Table استفاده کنم، که راحت تر بود که از یک متغیر جدولی استفاده کنم. به کل CTE را فراموش کرده بودم! چون بیشتر از متغیر جدولی استفاده می کنم کمتر می شه از CTE استفاده کنم.

به دلیل این که عبارت with در T- SQL برای اهداف مختلفی استفاده می شود برای متمایز کردن with به عنوان یک Common table expression = CTE باید از semicolon قبل از with استفاده کرد. ولی از آنجایی که شما می خواهین در View استفاده کنید بدون semicolon می نویسید.

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

[behroz1387]

دوست عزيز مشكلم حل شد ممنون
ولي اگه برايتان مقدور است ممنون ميشم

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

یک Trigger از نوع After تعریف کنید که بعد از Insert و Update و Delete اجرا شده و جدول شما را Update کند. برای Update کردن جدول از کد که در ادامه قرار داده ام استفاده کنید.



--===================================
Set NoCOunt On
--===================================
Declare @table table (col1 int, col2 int)
insert into @table (col1) values(50)
insert into @table (col1) values(50)
insert into @table (col1) values(100)
--===================================
Select * from @table
/*
col1 col2
----------- -----------
50 NULL
50 NULL
100 NULL
*/
--===================================
Declare @seq int
Set @seq = 0
--===================================
Update @table
Set @seq = col2 = @seq + col1
--===================================
Select * From @table
/*
col1 col2
----------- -----------
50 50
50 100
100 200
*/
--===================================
/*
Create Trigger I_U_D
On Table_Name
After Insert, Update, Delete
as
Begin
Declare @seq int
Set @seq = 0

Update table_name
Set @seq = col2 = @seq + col1
End
*/