شبیه سازی foreach و محاسبه سطرهای جدول با استفاده از یک شرط

[hercool]

سلام دوستان من دو تا جدول دارم با نام های listcompany که کد شرکت ها رو داره که یکتا هستند و تکراری توش نیست
و جدولی دارم که به نام listshare که کد کمپانی و تعداد سهم کمپانی و پایین ترین و بالاترین قیمت را به صورت فیلد داره که برای هر شرکتی در این جدول می تونه هزاران رکورد باشه یکتا نیست
و من می خوام از این جدول دوم کد کمپانی با مجموع تمام سهم های اون شرکت و کمترین قیمت و بالاترین قیمت را پیدا کنه و در بیاره
برای اینکار باید یک حلقه ایجاد می کردم که با جستجو هام به جاهایی رسیدم اما قسمت اخر که شرط برابری هست رو نمی تونم درست کنم
ممنون میشم راهنماییم کنید این کار رو می تونم تو کد های c# انجام بدم ولی می خوام به صورت یک کوئری ایجاد بشه تا بتونم با jobs اجراش کنم مستقل از کد نرم افزار:قلب:
use daro

DECLARE @sumtable Table (codecompany int,sumshare int,minvalue int,maxvalue int)

DECLARE @table1 table (codecompany int)--,nushare int,minprice int,maxpric int)

insert @table1 select (codecompany) from daro.dbo.listcompany

DECLARE @CurrentRow int
DECLARE @RowsToProcess int
SET @RowsToProcess=@@ROWCOUNT
SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1

insert into @sumtable
select d.codecompany, sum(d.nushare),MIN(d.minprice),MAX(d.maxprice)
from daro.dbo.listshare as d

where d.codecompany =@table1.codecompany


end
خطاش این هست

Msg 137, Level 16, State 1, Line 21
Must declare the scalar variable "@table1".
که مربوط میشه به اینکه نمی تونه فیلد کد شرکت را از @table1 بگیره

[ham3d1988]

سلام
اینطوری ساده تر نیست؟

select codecompany
,(select sum(Ls.nushare) from Listshare Ls where Ls.codecompany=Lc.codecompany) Num
,(select max(Ls.maxprice) from Listshare Ls where Ls.codecompany=Lc.codecompany) maxP
,(select min(Ls.minprice) from Listshare Ls where Ls.codecompany=Lc.codecompany) minP
from Listcompany Lc

[hercool]

واقعا ممنونم داشتم کد برای ساخت اسمبلی میزدم واقعا عالی بود اما یه سوال دارم
در نظر بگیرید که در جدول listshare فیلدی داریم با نام تاریخ از نوع date می خوام بزرگترین تاریخ (نزدیکترین تاریخ به الان که در جدول ذخیره هست برگردونده بشه) اینجا رو هر چی میزنم نمی تونم چیزی بدست بیارم

[hercool]

یکم واضح بگم منظورمو
من می خوام بر اساس جدولی که شماره شرکت ها رو داره بره در جدول لیست سهام اخرین روز اون شرکت جمع تعداد سهام و بیشترین و کمترین قیمت رو بگیره که مربوط به اخرین تاریخ هست تاریخ می تونه هم امروز باشه هم می تونه تاریخ چند روز پیش باشه (مهم اخرین تاریخی هست که برای اون شرکت در جدول ثبت شده هست ) البته تاریخ رو باید برگردونه تا بدونم هر سطری مربوط به چه تاریخ هست

بازم ممنون خیلی کمک کردی دوست من

[hercool]

من هر کاری می کنم نمیشه حتی براش تابع نوشتم اما نشد کد تابع اینه
ALTER FUNCTION [dbo].[getmaxtarikh]
(
-- Add the parameters for the function here
@codecompany int
)
RETURNS date
AS
BEGIN
-- Declare the return variable here
declare @tt date
set @tt=(select top 1 max(tarikh) from listshare where codecompany=@codecompany )

-- Return the result of the function
RETURN @tt

END
use daro


select codecompany

,(select max (ls.tarikh)from listshare ls where ls.codecompany=lc.codecompany and ls.tarikh=[dbo].[getmaxtarikh](codecompany) ) Tarikh
,(select sum(Ls.nushare) from Listshare Ls where Ls.codecompany=Lc.codecompany and tarikh in( max(tarikh))) Num
,(select max(Ls.maxprice) from Listshare Ls where Ls.codecompany=Lc.codecompany) maxP
,(select min(Ls.minprice) from Listshare Ls where Ls.codecompany=Lc.codecompany) minP



from Listcompany Lc


متن خطا هم اینه البته از having هم استفاده کردم اما نشد

Msg 147, Level 15, State 1, Line 7
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

[cherchil_hra]

1. شما می توانید به جای استفاده از سه دستور select از دستور cross apply استفاده کنید و یکبار select بزنید.
2. برای اینکه آخرین تاریخ برای هر کمپانی رو بدست بیاورید کافی هست که براساس کد شرکت select بزنید و براساس تاریخ به صورت نزولی مرتب سازی کنید؛ در نهایت سطر اول رو انتخاب کنید. یا با همان تابع max که نوشته اید ولی در داخل شرط.
SELECT t1.codecompany
t1.CompanyName,
rs.tarikh,
rs.nushare,
rs.maxprice,
rs.minprice
FROM Listcompany t1
CROSS APPLY (
SELECT SUM(t2.nushare) AS nushare,
MAX(t2.maxprice) AS maxprice,
MIN(t2.minprice) AS minprice,
t2.tarikh
FROM Listshare AS t2
WHERE t2.codecompany = t1.codecompany
AND t2.tarikh = (
SELECT TOP(1) tarikh
FROM Listshare AS t3
WHERE t3.codecompany = t1.codecompany
ORDER BY tarikh DESC
)
GROUP BY tarikh
) AS rs

دستورات sql رو مطالعه کنید. مشکل دستور شما این هست که از فیلد tarikh استفاده کردید ولی در group by آن را ذکر نکرده اید و به جای استفاده در having در where استفاده کرده اید. از تابع ایجاد شده فقط در select اول استفاده کرده اید. خطا در select دوم شماست که از تابع max استفاده کردید. در مابقی هم که از تابعتون استفاده نکرده اید.
فیلد companyName (نام شرکت) رو من اضافه کردم. اگر نام شرکت رو نمی خواهی استفاده کنی، select اولت بی معنی خواهد بود. یعنی این مدلی میشه :

SELECT t2.codecompany,
SUM(t2.nushare) AS nushare,
MAX(t2.maxprice) AS maxprice,
MIN(t2.minprice) AS minprice,
t2.tarikh
FROM Listshare AS t2
WHERE t2.codecompany = t1.codecompany
AND t2.tarikh = (
SELECT max(tarikh)
FROM Listshare AS t3
WHERE t3.codecompany = t2.codecompany
)
GROUP BY t2.codecompany,t2.tarikh
که برای بدست آوردن تاریخ از تابع max استفاده شده است.

موفق باشید!

[hercool]

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

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

[حمیدرضاصادقیان]

سلام
دوست عزیز کدی که در پست 6 نوشته شده کاملا میتونه برای شما آموزشی باشه و خیلی تمیز و دقیق نوشته شده و از راندمان بالایی نیز برخوردار هست.البته در کد اول به جای Top می شد از offset fetch استفاده کرد که از لحاظ سرعتی نیز بهتر عمل میکنه .ولی در کل کاملا Set based نوشته شده و سعی کنید از این قالب استفاده کنید.

[hercool]

125591
من کد ها را امتحان کردم
کد اول تنها یک شرکت را بر می گرداند در صورتی که من می خوام برای همه شرکت ها را برگرداند یعنی یک حلقه که نام کل شرکت ها را امتحان کند نه یکی از انها را
در کد دوم که گفتید نیاز نیست نام کمپانی باشه مشکلی هست t1.codecompany هست که اصلا وجود نداره و اگر بخوام تغییری بدم در عکس می بینید که تنها یک رکورد را بر می گرداند


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

[cherchil_hra]

در کد دوم همانطور که گفتید t1 وجود نداره و باید sql به شما خطا برگرداند. خوب این اشتباه من بود یادم رفت حذف کنم. چون وقتی t1 وجود نداشته باشد و codecompany در group by قرار گرفته است نیازی به آن قسمت نخواهد بود. با کمی دقت می توانستید مشکل را بر طرف کنید.
SELECT t2.codecompany,
SUM(t2.nushare) AS nushare,
MAX(t2.maxprice) AS maxprice,
MIN(t2.minprice) AS minprice,
t2.tarikh
FROM Listshare AS t2
WHERE t2.tarikh = (
SELECT MAX(tarikh)
FROM Listshare AS t3
WHERE t3.codecompany = t2.codecompany
)
GROUP BY
t2.codecompany,
t2.tarikh

طبق جدولی که شما قرار دادید و کوئری که در قسمت #6 نوشته ام و استفاده از تابع max :
SELECT t1.codecompany,
t1.CompanyName,
rs.tarikh,
rs.nushare,
rs.maxprice,
rs.minprice
FROM ListCompany t1
CROSS APPLY (
SELECT SUM(t2.nushare) AS nushare,
MAX(t2.maxprice) AS maxprice,
MIN(t2.minprice) AS minprice,
t2.tarikh
FROM Listshare AS t2
WHERE t2.codecompany = t1.codecompany
AND t2.tarikh = (
SELECT max(tarikh)
FROM Listshare AS t3
WHERE t3.codecompany = t2.codecompany
)
GROUP BY t2.codecompany , tarikh
) AS rs


که خروجی (با استفاده از تابع max، یا TOP و یا همانطور که آقای صادقیان فرمودند استفاده از offset برای بدست آوردن آخرین تاریخ)برابر خواهد بود با :

125697

از لحاظ بهینه بودن، کدی که جدول ListCompany در آن دخیل هست و از تابع max استفاده کرده است، هزینه کمتری خواهد داشت.
موفق باشید!