سلام واحترام خدمت دوستان عزیز
در نمونه ای که گذاشتم ، 2 تیبل (ورودی / خروجی) موجود هست که میخایم با استفاده از ساخت یک کویری ، مقدار کل ورودی و خروجی بصورت تجمعی محاسبه بشه %
مثلا : کالای cpu i3 9100f ، طی دو مرحل (50 + 8) وارد انبار شده و طی سه مرحله (3 + 1 + 2) از انبار خارج شده.
چطور میتونیم با استفاده از کویری، مقادیر کل ورودی (58) و کل خروجی (6) و باقیمانده (52) رو بصورت تجمعی بدست بیاریم ؟
سلام و روز خوش
همیشه جدول ها و خروجی خواسته رو پیوست کنین و روی همون توضیح بدین،
سریعتر پاسخ میگیرین و شاید اصلا نیازی به پیوست دیتا هم نباشه:
ID ItemCode Description InQty 1 1011 cpu i3 9100f 50 2 1012 cpu i3 1200f 20 3 1013 cpu i5 6500 30 4 1014 hdd 1tb sata 10 5 1015 hdd 2tb sata 12 6 1016 vga 4060 8gb 15 7 1017 vga 3050 8gb 18 8 1011 cpu i3 9100f 8 9 1012 cpu i3 1200f 5
ID ItemCode Description OutQty 1 1011 cpu i3 9100f 3 2 1012 cpu i3 1200f 7 3 1013 cpu i5 6500 2 4 1014 hdd 1tb sata 5 5 1015 hdd 2tb sata 1 6 1016 vga 4060 8gb 4 7 1017 vga 3050 8gb 2 8 1011 cpu i3 9100f 1 9 1012 cpu i3 1200f 1 10 1013 cpu i5 6500 2 11 1014 hdd 1tb sata 3 12 1015 hdd 2tb sata 5 13 1016 vga 4060 8gb 1 14 1017 vga 3050 8gb 3 15 1011 cpu i3 9100f 2
به هر حال این یک مسئله ساده total query هست:
گرچه با یک کوئری هم میشه، ولی بهتر و ساده تر هست 2 کوئری جدا بسازین،
یکی برای ورودی که در اون روی itemcode گروپ میگیرین و روی inqty هم sum ،
و مشابه همین رو برای خروجی میسازین.
حالا این دو تا کوئری رو روی itemcode با هم join میکنین.
البته میتونین کد این دو کوئری رو در کوئری سوم جایگزین کنین و یک کوئری داشته باشین،
ولی واقعا مزیتی نمیبینم.
نوشته شده توسط mazoolagh
سلام. ممنون از پاسخ شما
بله من هم ابتدا دوتا کویری ساختم و در کویری سوم نتیجه نهایی رو بدست آوردم....
اتفاقا برای دیتابیس انبار که با تعداد زیادی (گزارش / جدول / کویری / ماژول) سروکار داریم ، مهمه و مزیت اون در اینه که شما بجای 3 کویری فقط 1 کویری خواهید داشت. اینطوری هم از تعداد فایها کم میشه وهم حجم اون کاهش پیدا میکنه...
بهرحال ممنون میشم اگه کسی بتونه اینکار رو در قالب یک کویری انجام بده.. سپاس
سلام دوباره
خب این کاملا اشتباه هست!
هر کوئری نهایتا در حد چند کیلوبایت به اندازه دیتابیس شما اضافه میکنه.
و اگر دیتابیس شما درست طراحی شده باشه (که اینجا دیده میشه اینجور نیست)
این که کوئری روی 2 کوئری میانی ساخته بشه یا مستقیما روی جدولها (کافی هست در تعریف کوئری نهایی همون تعریف اون دو کوئری رو جایگزین کنین - با alias)
واقعا هیچ تاثیری نه تنها در اندازه بلکه در سرعت اجرا هم نداره:
برای این که یک ایده داشته باشین فرضا اگر چند 100 کالا داشته باشین
و جدول های in و out هم هر کدوم چند 100 هزار رکورد داشته باشن
تفاوت سرعت اجرا شاید در اندازه چند 100م ثانیه باشه!
ولی الان طراحی جداول شما نادرست هست چون normalize نیست،
در واقع این هست که اندازه دیتابیس رو بی دلیل زیاد میکنه و البته آثار جانبی دیگه هم داره.
با این وجود در تعداد رکورد کم این نرمالایز نبودن جداول خودش رو نشون نمیده (از نظر سرعت اجرای کوئری)
ولی آثار مخربش در هر صورت هست.
خب اول باید 2 کوئری جدا یکی برای ورودی و یکی خروجی بسازیم:
SELECT
ItemCode,
Description,
Sum(InQty) AS [Quantity In],
Count(ItemCode) AS [Count In]
FROM in_wh
GROUP BY ItemCode, Description
1.png
2.png
SELECT
ItemCode,
Description,
Sum(OutQty) AS [Quantity Out],
Count(ItemCode) AS [Count Out]
FROM out_wh
GROUP BY ItemCode, Description
3.png
4.png
حالا از روی این دو میتونیم کوئری تراز رو تعریف کنیم:
SELECT
StockIN.ItemCode,
StockIN.Description,
StockIN.[Quantity In],
StockOUT.[Quantity Out],
StockIn.[Quantity In] - NZ(StockOut.[Quantity Out], 0) AS [Stock Balance],
StockIN.[Count In], StockOUT.[Count Out]
FROM StockIN
LEFT JOIN StockOUT
ON StockIN.ItemCode = StockOUT.ItemCode
5.png
برای ترکیب دو کوئری StockIn و StockOut در یک کوئری کافی هست تعریف اونها رو با alias بیاریم:
SELECT
input.ItemCode,
input.Description,
input.Q AS [Quantity In],
output.Q AS [Quantity Out],
Input.Q - Nz(output.Q, 0) AS [Stock Balance],
input.N AS [Count In],
output.N AS [Count Out]
FROM (
SELECT
ItemCode,
Description,
Sum(InQty) AS Q,
Count(ID) AS N
FROM in_wh
GROUP BY ItemCode, Description) AS [input]
LEFT JOIN (
SELECT
ItemCode,
Description,
Sum(OutQty) AS Q,
Count(ID) AS N
FROM out_wh
GROUP BY ItemCode, Description) AS [output]
ON input.ItemCode = output.ItemCode
6.png
اما برای نرمال کردن جداول باید یک جدول کدینگ داشته باشیم و فیلد description رو از جدول های in و out حذف کنیم:
SELECT
ItemCode,
Description
INTO Items
FROM in_wh
GROUP BY ItemCode, Description
7.png
حالا برای گرفتن description کوئری پست قبلی رو با این جدول join میکنیم:
SELECT
input.ItemCode,
Items.Description,
input.Q AS [Quantity In],
output.Q AS [Quantity Out],
Input.Q - Nz(output.Q, 0) AS [Stock Balance],
input.N AS [Count In],
output.N AS [Count Out]
FROM
(
(
SELECT
ItemCode,
Sum(InQty) AS Q,
Count(ID) AS N
FROM
in_wh
GROUP BY
ItemCode
) AS [input]
LEFT JOIN (
SELECT
ItemCode,
Sum(OutQty) AS Q,
Count(ID) AS N
FROM
out_wh
GROUP BY
ItemCode
) AS [output] ON input.ItemCode = output.ItemCode
)
LEFT JOIN Items ON input.itemcode = Items.itemcode
حالا هم دیتا تکراری و بدردنخور نداریم (کاهش چشمگیر اندازه فایل دیتابیس)
هم عملیات سریعتر هست
و از همه مهمتر یکپارچگی دیتا رو داریم.
دست مریزاد استاد
قوانین ایجاد تاپیک در تالار
پاسخ با نقل قول