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

Tovarlarning sotilishi xolati to‘g‘risidagi batafsil ma’lumotlarni Z_OUTPUT nomli tasavvurda saqlash

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.