Axborot texnologiyalari servisi

CREATE VIEW Z_INPUT_JAMI AS

SELECT TOVAR_ID, TOVAR_NAME, EDIN_NAME, SUM(INPUT_SON) AS JAMI_KELGAN,

SUM(INPUT_SON*OLINGAN_BAXO) AS JAMI_OLINGAN_BAXO,

SUM(INPUT_SON*DONALAB_SOTISH_BAXO) AS REJA_DONALAB_SOTISH,

SUM(INPUT_SON*KUTARA_SOTISH_BAXO) AS REJA_KUTARA_SOTISH

FROM Z_INPUT

GROUP BY TOVAR_ID, TOVAR_NAME

Товарларнинг сотилиши холати тўғрисидаги батафсил маълумотларни Z_OUTPUT номли тасаввурда сақлаш

CREATE VIEW Z_OUTPUT AS

SELECT Z.*, T.OTOVAR_SON AS OUTPUT_SON,

T.OTOVAR_BAXO AS SOTILGAN_BAXO,

T.SOTISH_SHAKLI,

T.OTOVAR_DATA AS SOTGAN_SANA,

P.PULTURI_NAME AS PUL_TURI,

X.XARIDOR_NAME AS XARIDOR,

S.SOTUVCH_FIO AS SOTGAN_SOTUVCHI

FROM Z_TOVAR_NAME Z

INNER JOIN TOVAR_OUT T ON Z.TOVAR_ID=T.TOVAR_ID INNER JOIN PULTURI P ON P.PULTURI_ID=T.PULTURI_ID INNER JOIN XARIDOR X ON X.XARIDOR_ID=T.XARIDOR_ID INNER JOIN SOTUVCHI S ON S.SOTUVCHI_ID=T.SOTUVCH_ID

Savdo majmuasidan xaridorlarga sotilgan maxsulotlarni soni, donalab va ko‘tara sotilgan narxlarining jamlanmasi

CREATE VIEW Z_OUTPUT_JAMI AS

SELECT TOVAR_ID, TOVAR_NAME, EDIN_NAME,

IF(SOTISH_SHAKLI>0, SUM(OUTPUT_SON), 0) AS JAMI_DONALAB_SOTILGAN, IF(SOTISH_SHAKLI>0, SUM(OUTPUT_SON*SOTILGAN_BAXO), 0) AS

JAMI_DONALAB_SOTILGAN_BAXO,

IF(SOTISH_SHAKLI<1, SUM(OUTPUT_SON), 0) AS JAMI_KUTARA_SOTILGAN, IF(SOTISH_SHAKLI<1, SUM(OUTPUT_SON*SOTILGAN_BAXO), 0) AS

JAMI_KUTARA_SOTILGAN_BAXO

FROM z_output

GROUP BY TOVAR_ID, TOVAR_NAME, SOTISH_SHAKLI

	TOVAR_					TOVAR_							EDIN_NAME										JAMI_										JAMI_										JAMI_											JAMI_
		ID					NAME													DONALAB_												DONALAB_										KUTARA_										KUTARA_
																						SOTILGAN								SOTILGAN_										SOTILGAN									SOTILGAN_
																																		BAXO																					BAXO
1						Картошка							Кг				0										0										34										53500
2						Памидор							Кг				0										0										22										44800
2						Памидор							Кг				7										11900										0										0
3						Пиёз							Кг				0										0										16										55400
3						Пиёз							Кг				28										78200										0										0
4						Карам							Кг				0										0										65										193600
4						Карам							Кг				26										70200										0										0

Yuqoridagi jamlavchi jadvalda donalab va ko‘tara sotilgan maxsulotlar alohida-alohida qatorlarga tushib turibdi. Chunki, IF sharti SOTISH_SHAKLI bo‘yicha amalga oshirilgandan so‘ng, avtomatik bu SOTISH_SHAKLI maydoni bo‘yicha ham guruhlash lozim.

Shuning uchun keyingi so‘rov Z_OUTPUT_JAMI tasavvuridagi takrorlanuvchi qatorlar bo‘yicha summalash va guruhlash ishlari olib boriladi va uni Z_OUTPUT_JAMI1 nomli tasavvurga yozib qo‘yamiz.