ТООЦОО
Дүгнэлт функцууд
SQL асуулгын илэрхийлэл нь ихэвчлэн өгөгдлийг урьдчилан боловсруулахыг шаарддаг. Энэ зорилгоор тэдгээрийг ашигладаг тусгай функцуудболон илэрхийлэл.
Тодорхой асуулгад хэдэн бичлэг таарч байгааг олж мэдэх шаардлагатай болдог.тодорхой тоон баганын утгуудын нийлбэр, түүний хамгийн их, хамгийн бага, дундаж утгууд хэд вэ. Энэ зорилгоор эцсийн (статистик, нэгтгэсэн) гэж нэрлэгддэг функцуудыг ашигладаг. Хураангуй функцууд нь жишээлбэл, WHERE заалтаар заасан багц бичлэгүүдийг боловсруулдаг. Хэрэв та тэдгээрийг SELECT мэдэгдлийн дараа баганын жагсаалтад оруулбал үр дүнгийн хүснэгт нь зөвхөн мэдээллийн баазын хүснэгтийн багана төдийгүй эдгээр функцээр тооцоолсон утгуудыг агуулна. Дараахь ньхураангуй функцуудын жагсаалт.
Үйлчлүүлэгчдээс COUNT(*) СОНГОХ;
Хэрэглэгчдээс COUNT(Захиалгын_Дэм) СОНГОХ;
Үйлчлүүлэгчдээс ТООЛОН (захиалгын_хэмжээ) СОНГОХ;
Дараах асуулгыг ажиллуулахыг оролдоход алдааны мэдэгдэл гарах болно:
Бүс нутгийг SELECT , COUNT(*) FROM Clients ;
СОНГОХ НИЙТЛЭГ (Захиалгын_хэмжээ) Хэрэглэгчээс;
Энэхүү SQL мэдэгдэл нь Хэрэглэгчдийн хүснэгтээс Order_Amount баганын бүх тодорхойлсон утгуудын нийлбэрийг агуулсан нэг багана, нэг бичлэгтэй хүснэгтийг буцаана.
Эх хүснэгтэд Order_Amount баганын утгыг рубльээр илэрхийлсэн бөгөөд нийт дүнг доллараар тооцох шаардлагатай гэж үзье. Хэрэв одоогийн ханш жишээлбэл 27.8 байвал та дараах илэрхийлэлийг ашиглан шаардлагатай үр дүнг авч болно.
СОНГОХ НИЙТЛЭГ (Захиалгын_хэмжээ*27.8) Үйлчлүүлэгчдээс;
Хэрэглэгчдээс AVG (Захиалгын_Дэм) СОНГОХ;
Үйлчлүүлэгчдээс AVG (Захиалгын_Дэмжээ*27.8) СОНГОХ
WHERE бүс<>"Баруун_хойд_3";
Үйлчлүүлэгчдээс хамгийн ихдээ (Захиалгын_дүгээр) СОНГОХ;
Үйлчлүүлэгчдээс дээд тал нь(Захиалгын_дүгээр*27.8) СОНГОХ
ХААНА Бүс нутаг<>"Баруун_хойд_3";
Хэрэглэгчдээс MIN(Захиалгын_Дэм) СОНГОХ;
Үйлчлүүлэгчдээс MIN (Захиалгын дүн*27.8) СОНГОХ
ХААНА Бүс нутаг<>"Баруун_хойд_3";
Практикт ихэвчлэн тоон баганын нийт, дундаж, хамгийн их, хамгийн бага утгыг агуулсан эцсийн хүснэгтийг авах шаардлагатай байдаг. Үүнийг хийхийн тулд та бүлэглэх (GROUP BY) болон хураангуй функцийг ашиглах хэрэгтэй.
СОНГОХ бүс, СУМ (Захиалгын_хэмжээ) Хэрэглэгчээс
Бүс нутгаар нь бүлэглэх;
Энэ асуулгын үр дүнгийн хүснэгтэд бүс нутгийн нэрс, холбогдох бүс нутгуудаас ирсэн бүх үйлчлүүлэгчдээс ирсэн захиалгын нийт (нийт) хэмжээг агуулна (Зураг 5).
Одоо бүх хураангуй мэдээллийг бүс нутгаар авах хүсэлтийг авч үзье:
Бүс нутгийг СОНГОХ, НИЙТЛЭЛ (Захиалгын_Дэм), AVG (Захиалгын_хэмжээ), MAX(Захиалгын_хэмжээ), MIN (Захиалгын_дүгээр)
Үйлчлүүлэгчдээс
Бүс нутгаар нь бүлэглэх;
Анхны болон үр дүнгийн хүснэгтийг Зураг дээр үзүүлэв. 8. Жишээн дээр зөвхөн баруун хойд бүсийг эх хүснэгтэд нэгээс олон бичлэгээр төлөөлүүлсэн. Тиймээс үр дүнгийн хүснэгтэд өөр өөр хураангуй функцууд өөр өөр утгыг өгдөг.
Цагаан будаа. 8. Бүс нутгаар захиалгын дүнгийн эцсийн хүснэгт
Та SELECT мэдэгдлийн баганын жагсаалтад хураангуй функцуудыг ашиглах үед үр дүнгийн хүснэгтийн харгалзах баганын толгойнууд нь Expr1001, Expr1002 гэх мэт байна. (эсвэл үүнтэй төстэй зүйл, SQL хэрэгжилтээс хамаарч). Гэсэн хэдий ч, та өөрийн үзэмжээр хураангуй функц болон бусад баганын утгуудын толгойг тохируулж болно. Үүнийг хийхийн тулд SELECT мэдэгдлийн баганын дараа маягтын илэрхийлэлийг зааж өгнө үү:
AS баганын_толгой
AS (as) гэсэн түлхүүр үг нь үр дүнгийн хүснэгтэд харгалзах баганад AS-ийн дараа заасан гарчиг байх ёстой гэсэн үг юм. Томилогдсон гарчгийг бас нэр гэж нэрлэдэг. Дараах жишээнд (Зураг 9) бүх тооцоолсон баганын нэрүүдийг тохируулсан:
Бүс нутгийг сонгох,
СУМ (Захиалгын_дүгээр) AS [Захиалгын нийт дүн],
AVG (Захиалгын_Дунд) AS [Захиалгын дундаж хэмжээ],
MAX(Захиалгын_Дэм) AS Хамгийн их,
MIN (Захиалгын_дүгээр) AS хамгийн бага,
Үйлчлүүлэгчдээс
Бүс нутгаар нь бүлэглэх;
Цагаан будаа. 9. Баганын нэр ашиглан захиалгын дүнгийн эцсийн хүснэгт
Зайгаар тусгаарлагдсан хэд хэдэн үгнээс бүрдсэн хочуудыг дөрвөлжин хаалтанд бичнэ.
Хураангуй функцийг SELECT болон HAVING заалтуудад ашиглаж болох боловч WHERE заалтад ашиглах боломжгүй. HAVING оператор нь WHERE оператортой төстэй боловч WHERE-ээс ялгаатай нь бичлэгүүдийг бүлгээр сонгодог.
Та аль бүс нутагт нэгээс олон үйлчлүүлэгчтэй болохыг тодорхойлохыг хүсч байна гэж бодъё. Энэ зорилгоор та дараах асуултыг ашиглаж болно.
Бүс нутгийг SELECT , Count(*)
Үйлчлүүлэгчдээс
COUNT(*) > 1 БҮСГЭЭР БҮЛЭГЛЭХ;
Үнэ цэнийг боловсруулах функцууд
Өгөгдөлтэй ажиллахдаа та үүнийг ихэвчлэн боловсруулах хэрэгтэй болдог (үүнийг зөв төрөл): мөрөнд дэд мөр сонгох, эхний болон арын зайг арилгах, тоог дугуйлах, квадрат язгуурыг тооцоолох, тодорхойлох Одоогийн цаггэх мэт. SQL-д гурван төрлийн функц байдаг:
Мөрийн функцууд
Мөрийн функцууд нь мөрийг параметр болгон авч боловсруулсны дараа мөр эсвэл NULL утгыг буцаана.
Жишээлбэл:
SUBSTRING ("Эрхэм Маша!" FROM 9 FOR 4) "Маша"-г буцаана;
SUBSTRING ("Хүндэт Маша!" FROM 9) "Маша!" гэж буцаана;
SUBSTRING("Эрхэм Маша!" FROM 15) нь NULL-г буцаана.
Та энэ функцийг SQL илэрхийлэлд ашиглаж болно, жишээ нь:
Үйлчлүүлэгчдээс * СОНГОХ
WHERE SUBSTRING(Region FROM 1 FOR 5) = "Хойд";
Жишээлбэл:
TRIM (ТЭРГҮҮЛЭХ " " FROM "Санкт-Петербург хот") "Санкт-Петербург хот" эргэлддэг;
TRIM(TRALING " " FROM "city of Petersburg") "City of Petersburg"-ыг буцаана;
TRIM (хоёул " " FROM " хот Санкт-Петербург ") "хот Санкт-Петербург" буцаана;
TRIM(BOTH FROM " city of. Petersburg ") буцаана "хот Санкт-Петербург";
TRIM(BOTH "g" FROM "city of. Petersburg") "хот Санкт-Петербург"-ыг буцаана.
Эдгээр функцүүдийн дотроос хамгийн түгээмэл хэрэглэгддэг функцүүд нь SUBSTRING() ба TRIM() юм.
Тоон функцууд
Тоон функцууд нь зөвхөн тоон төрлийн өгөгдлийг параметр болгон хүлээн авахаас гадна үргэлж тоо эсвэл NULL (тодорхойгүй утга) буцаана.
Жишээлбэл:
POSITION("e" ДАХЬ "Сайн байна уу") 5-ыг буцаана;
POSITION ("бүгд сайн уу" ДАХЬ) 8-ыг буцаана;
POSITION(" " Бүгдээрээ сайн уу") 1-ийг буцаана;
POSITION("Сайн уу!" ДАХЬ "Сайн байна уу") 0-г буцаана.
Үйлчлүүлэгчдийн хүснэгтийн (1-р зургийг үз) Хаяг багана нь хотын нэр, шуудангийн код, гудамжны нэр болон бусад өгөгдлийг агуулна. Та тодорхой хотод амьдардаг үйлчлүүлэгчдэд зориулсан бүртгэлийг сонгох шаардлагатай байж магадгүй юм. Тиймээс, хэрэв та Санкт-Петербургт амьдардаг үйлчлүүлэгчидтэй холбоотой бүртгэлийг сонгохыг хүсвэл SQL хайлтын дараах илэрхийллийг ашиглаж болно.
Үйлчлүүлэгчдээс * СОНГОХ
WHERE POSITION (" Санкт-Петербург " ДАХЬ хаяг ) > 0;
Энэхүү энгийн өгөгдөл хайх хүсэлтийг өөр өөрөөр томъёолж болохыг анхаарна уу:
Үйлчлүүлэгчдээс * СОНГОХ
ХААНА "%Петербург%" ШИГ хаяг;
ХАНДЛАГА ("2005-10-25"-аас хойшхи САР) 10 буцаана.
Жишээлбэл:
CHARACTER_LENGTH("Сайн байна уу") 11-ийг буцаана.
ABS (-123) нь 123-ыг буцаана;
ABS (2 - 5) нь 3-ыг буцаана.
MOD(5, h) 2-ыг буцаана;
MOD(2, h) 0-г буцаана.
FLOOR (5.123) 5.0-ийг буцаана.
CEIL(5.123) нь 6.0-ыг буцаана.
WIDTH_BUCKET(3.14, 0, 9, 5) нь 2-г буцаана.
Огноо цагийн функцууд
IN SQL хэлОдоогийн огноо, цагийг буцаадаг гурван функц байдаг.
Жишээ нь: 2005-06-18.
12:39:45.27.
Эдгээр функцээр буцаасан огноо, цаг нь тэмдэгтийн төрөл биш гэдгийг анхаарна уу. Хэрэв та тэдгээрийг тэмдэгтийн мөр болгон төлөөлөхийг хүсвэл үүнийг хийхийн тулд CAST() төрлийн хөрвүүлэх функцийг ашиглах хэрэгтэй.
Огноо цагийн функцийг ихэвчлэн өгөгдөл оруулах, шинэчлэх, устгах асуулгад ашигладаг. Жишээлбэл, борлуулалтын мэдээллийг бүртгэхдээ та оруулна одоогийн огнооба цаг хугацаа. Нэг сар эсвэл улирлын үр дүнг нэгтгэсний дараа тайлант хугацааны борлуулалтын мэдээллийг устгаж болно.
Тооцоолсон илэрхийллүүд
Тооцоолсон илэрхийлэл нь тогтмол (тоон, мөр, логик), функц, талбарын нэр болон бусад төрлийн өгөгдлүүдээс арифметик, мөр, логик болон бусад операторуудтай холбож бүтээдэг. Хариуд нь илэрхийлэлийг оператор ашиглан илүү төвөгтэй (нийлмэл) илэрхийлэл болгон нэгтгэж болно. Хаалт нь илэрхийллийг үнэлэх дарааллыг хянахад ашиглагддаг.
Логик операторуудБА, ЭСВЭЛ, БИШ, функцууд өмнө нь хэлэлцсэн.
Арифметик операторууд:
Мөр операторзөвхөн нэг залгах эсвэл мөр холбох оператор (| |). Зарим SQL хэрэгжилтэд (жишээлбэл, Microsoft Access) (| |)-ийн оронд (+) тэмдэг хэрэглэнэ. Холбогч оператор нь эхний жишээний төгсгөлд хоёр дахь мөрийг хавсаргана, илэрхийлэл:
"Саша" | | "хайртай" | | "Далалах"
Үүний үр дүнд "Саша Машад хайртай" мөрийг буцааж өгөх болно.
Илэрхийлэл зохиохдоо та операторуудын операндууд хүчинтэй байгаа эсэхийг шалгах ёстой. Жишээ нь: 123 + "Саша" гэсэн илэрхийлэл нь мөрийн операнд дээр арифметик нэмэх оператор хэрэгждэг тул хүчин төгөлдөр бус байна.
Тооцоолсон илэрхийллүүд дараа гарч ирж болно SELECT мэдэгдэл, түүнчлэн WHERE болон HAVI хэллэгүүдийн нөхцөл байдлын илэрхийлэлдН.Г.
Хэд хэдэн жишээг харцгаая.
Борлуулалтын хүснэгтэд Бүтээгдэхүүний төрөл, Тоо хэмжээ, Үнэ гэсэн багануудыг агуулж, бид бүтээгдэхүүний төрөл бүрийн орлогыг мэдэхийг хүсч байна. Үүнийг хийхийн тулд SELECT мэдэгдлийн дараа баганын жагсаалтад Quantity*Price илэрхийллийг оруулахад л хангалттай.
Бүтээгдэхүүний_төрөл, Тоо хэмжээ, Үнэ, Тоо хэмжээ*Үнэ СОНГОХ AS
Нийт борлуулалтаас;
Энэ нь AS (as) түлхүүр үгийг ашиглан тооцоолсон өгөгдлийн баганад өөр нэрийг зааж өгнө.
Зураг дээр. Зураг 10-д Борлуулалтын анхны хүснэгт болон асуулгын үр дүнгийн хүснэгтийг харуулав.
Цагаан будаа. 10. Бүтээгдэхүүний төрөл тус бүрийн орлогын тооцоо бүхий асуулгын үр дүн
Хэрэв та бүх барааны борлуулалтаас олсон нийт орлогыг мэдэхийг хүсвэл дараах асуултыг ашиглана уу.
СОНГОХ НИЙТЛЭГ (Тоо хэмжээ*Үнэ) Борлуулалтаас;
Дараах асуулга нь баганын жагсаалт болон WHERE заалтын нөхцөлийн аль алинд нь тооцоолсон илэрхийллүүдийг агуулна. Тэрээр борлуулалтын хүснэгтээс борлуулалтын орлого нь 1000-аас дээш байгаа бүтээгдэхүүнийг сонгодог.
Бүтээгдэхүүний_төрөл, Тоо хэмжээ*Үнэ AS Нийт
Борлуулалтаас
ХААНА Тоо хэмжээ*Үнэ > 1000;
Та хоёр баганатай хүснэгт авахыг хүсч байна гэж бодъё:
Бүтээгдэхүүний төрөл, үнийг агуулсан бүтээгдэхүүн;
Орлогыг агуулсан нийт.
Борлуулалтын анхны хүснэгтийн Бүтээгдэхүүний төрөл баганыг тэмдэгт (CHAR төрөл), Үнэ багана нь тоон байна гэж үздэг тул эдгээр баганаас өгөгдлийг нэгтгэх (наах) үед тоон төрлийг тэмдэгтийн төрөлд шилжүүлэх шаардлагатай. CAST() функц. Энэ ажлыг гүйцэтгэх асуулга дараах байдалтай байна (Зураг 11):
Бүтээгдэхүүний_төрлийг СОНГОХ | | " (Үнэ: " | | CAST(Үнэ AS CHAR(5)) | | ")" AS Бүтээгдэхүүн, Тоо хэмжээ*Үнэ AS Нийт
Борлуулалтаас;
Цагаан будаа. 11. Нэг баганад янз бүрийн төрлийн өгөгдлийг нэгтгэсэн асуулгын үр дүн
Анхаарна уу. Microsoft Access-д ижил төстэй асуулга дараах байдалтай байна.
Бүтээгдэхүүний_төрлийг SELECT + " (Үнэ: " + C Str (Үнэ) + ")" AS Бүтээгдэхүүн,
Тоо хэмжээ*Үнэ AS Нийт
Борлуулалтаас;
CASE мэдэгдэл бүхий нөхцөлт илэрхийллүүд
Уламжлалт програмчлалын хэл нь нөхцөлт үсрэлт операторуудтай бөгөөд зарим нөхцөл үнэн эсэхээс хамааран тооцооллын процессыг удирдах боломжийг олгодог. SQL-д энэ оператор нь CASE (тохиолдол, нөхцөл байдал, тохиолдол). SQL:2003 дээр энэ оператор утгыг буцаадаг тул илэрхийлэлд ашиглаж болно. Энэ нь хоёр үндсэн хэлбэртэй бөгөөд бид үүнийг энэ хэсэгт авч үзэх болно.
Утга бүхий CASE мэдэгдэл
Утга бүхий CASE мэдэгдэл нь дараах синтакстай байна:
CASE шалгагдсан_утга
ХЭЗЭЭ утга1 ТЭГВЭЛ үр дүн1
ХЭЗЭЭ утга2 Дараа нь үр дүн2
. . .
ХЭЗЭЭ N-ийн утга ТЭГВЭЛ Н-ийн үр дүн
БУСДАА үр дүнX
Хэрэв шалгасан_утгаутгатай тэнцүү 1 , CASE мэдэгдэл нь утгыг буцаанаүр дүн1 , дараа заасан түлхүүр үгТЭГЭЭД (тэр). Үгүй бол шалгасан_утгыг харьцуулнаүнэ цэнэ2 , хэрэв тэдгээр нь тэнцүү бол үр дүн2 утгыг буцаана. Үгүй бол, шалгаж буй утгыг WHEN түлхүүр үг гэх мэтийн дараа заасан дараагийн утгатай харьцуулна. Хэрэв шалгагдсан_утга нь эдгээр утгуудын аль нэгэнтэй нь тэнцүү биш бол утгыг буцаана.үр дүн X , ELSE (else) түлхүүр үгийн дараа заасан.
ELSE түлхүүр үг нь сонголттой. Хэрэв энэ нь байхгүй бөгөөд харьцуулж буй утгуудын аль нь ч туршиж буй утгатай тэнцүү биш бол CASE мэдэгдэл NULL утгыг буцаана.
Үйлчлүүлэгчдийн хүснэгтэд үндэслэн (1-р зургийг үз) та бүс нутгийн нэрийг кодын дугаараар сольсон хүснэгтийг авахыг хүсч байна гэж бодъё. Хэрэв эх хүснэгтэд хэт олон өөр бүс байхгүй бол энэ асуудлыг шийдэхийн тулд CASE оператортой асуулга ашиглах нь тохиромжтой.
Нэр, хаяг СОНГОХ,
CASE бүс
ХЭЗЭЭ "Москва" Дараа нь "77"
ХЭЗЭЭ "Тверь муж" Дараа нь "69"
. . .
ӨӨР бүс
AS бүсийн код
Үйлчлүүлэгчдээс;
Хайлтын нөхцөл бүхий CASE мэдэгдэл
CASE операторын хоёрдахь хэлбэр нь тодорхой нөхцөлийг хангасан эдгээр бичлэгийн хүснэгтийг хайхдаа ашиглах явдал юм.
CASE
ХЭЗЭЭ нөхцөл1 ТЭГВЭЛ үр дүн1
ХЭЗЭЭ catch2 Дараа нь үр дүн2
. . .
Нөхцөл N ТЭГВЭЛ Н үр дүн гарна
БУСДАА үр дүнX
CASE мэдэгдэл нь WHERE заалтаар тодорхойлсон багцын эхний бичлэгийн хувьд нөхцөл1 үнэн эсэхийг, эсвэл WHERE байхгүй бол хүснэгтийг бүхэлд нь шалгадаг. Хэрэв тийм бол CASE үр дүн1-ийг буцаана. Үгүй бол энэ бичлэгийн нөхцөл 2-ыг шалгана. Хэрэв үнэн бол үр дүн2 гэсэн утгыг буцаана гэх мэт. Хэрэв нөхцөлүүдийн аль нь ч үнэн биш бол үр дүнгийн утгыг буцаана. X , ELSE түлхүүр үгийн дараа заасан.
ELSE түлхүүр үг нь сонголттой. Хэрэв энэ нь байхгүй бөгөөд аль ч нөхцөл нь үнэн биш бол CASE хэллэг NULL-ийг эргүүлнэ. CASE агуулсан мэдэгдлийг эхний бичлэгт гүйцэтгэсний дараа дараагийн бичлэг рүү шилжинэ. Энэ нь бүх бичлэгийг боловсруулж дуустал үргэлжилнэ.
Номын хүснэгтэд (Гарчиг, Үнэ) харгалзах ном байхгүй бол багана NULL байна гэж бодъё. Дараах асуулга нь NULL биш харин "Хөөцөөгүй" гэсэн хүснэгтийг буцаана:
Сонгох Гарчиг,
CASE
Үнэ ҮНЭГҮЙ БОЛСОН ҮЕД "Хувьцаагүй"
БУСАД ДҮГСЭЛТ(Үнэ CHAR(8))
AS үнэ
Номноос;
Нэг баганад байгаа бүх утгууд ижил төрлийн байх ёстой. Иймээс энэ асуулга нь дамжуулахын тулд CAST төрлийн хөрвүүлэх функцийг ашигладаг тоон утгуудбагана үнэ тэмдэгтийн төрөл.
Та эхнийх нь оронд CASE мэдэгдлийн хоёр дахь хэлбэрийг үргэлж ашиглаж болно гэдгийг анхаарна уу:
CASE
Шалгасан_утга = утга1 ТЭГВЭЛ үр дүн1
Шалгасан_утга = утга2 ТЭГВЭЛ үр дүн2
. . .
Шалгасан_утга = утга N Дараа нь үр дүнN
БУСДАА үр дүн
NULLIF болон COALESCE функцууд
Зарим тохиолдолд, ялангуяа өгөгдлийг шинэчлэх хүсэлтэд (UPDATE оператор) төвөгтэй CASE операторын оронд илүү авсаархан NULLIF() (NULL if) болон COALESCE() (нэгтгэх) функцийг ашиглах нь тохиромжтой байдаг.
NULLIF функц ( утга1, утга2) эхний параметрийн утга хоёр дахь параметрийн утгатай тохирч байвал NULL утгыг буцаана, хэрэв таарахгүй бол эхний параметрийн утгыг өөрчлөхгүй буцаана. Өөрөөр хэлбэл, тэгш байдлын утга1 = утга2 үнэн бол функц нь NULL, үгүй бол утга1 утгыг буцаана.
Энэ функцдараах хоёр хэлбэрээр CASE мэдэгдэлтэй тэнцүү байна:
ХЭЗЭЭ утга2 ДАРАА NULL
БУСДАА утга1
ХЭЗЭЭ утга1 = утга2 Дараа нь NULL
БУСДАА утга1
COALESCE функц( утга1, утга2, ... , N утга) NULL эсвэл NULL байж болох утгуудын жагсаалтыг хүлээн авдаг. Функц нь жагсаалтаас заасан утгыг буцаана эсвэл бүх утгууд нь тодорхойгүй бол NULL.
Энэ функц нь дараах CASE мэдэгдэлтэй тэнцүү байна:
CASE
ХЭЗЭЭ 1 утга NULL БАЙХГҮЙ БОЛ 1-р утга
ХЭЗЭЭ 2 утга NULL БАЙХГҮЙ БОЛ 2-р утга
. . .
ХЭРЭГСЛИЙН N утга нь NULL БАЙХГҮЙ БОЛ ТЭГВЭЛ N утга
ELSE NULL
Номууд (Гарчиг, Үнэ) хүснэгтэд харгалзах ном байхгүй бол Үнэ багана NULL байна гэж бодъё. Дараах асуулга нь хаана-ын оронд хүснэгтийг буцаана NULL "Хувьцаагүй" гэсэн бичвэр гарч ирнэ:
Нэр СОНГОХ, COALESCE (CAST(Үнэ AS CHAR(8)),
"Хувьцаагүй") AS Үнэ
Номноос;
Энэ зааварт та хэрхэн ашиглах талаар сурах болно SUM функцВ SQL сервер(Transact-SQL) синтакс болон жишээнүүдийн хамт.
SQL сервер дээр (Transact-SQL) SUM функцилэрхийллийн нийт утгыг буцаана.
Синтакс SUM функцууд SQL сервер дээр (Transact-SQL):
ЭСВЭЛ үр дүнг нэг буюу хэд хэдэн баганаар бүлэглэх үед SUM функцийн синтакс нь:
илэрхийлэл1 , илэрхийлэл2 , ... илэрхийлэл_n нь SUM функцэд ороогүй илэрхийллүүд бөгөөд SQL хэллэгийн төгсгөлийн GROUP BY заалтад заавал байх ёстой.
нэгтгэх_илэрхийлэл нь нэгтгэх багана эсвэл илэрхийлэл юм.
хүснэгтүүд - бүртгэл авахыг хүссэн хүснэгтүүд. FROM заалтад дор хаяж нэг хүснэгт байх ёстой.
WHERE нөхцөл - сонголттой. Эдгээр нь сонгосон бичлэгийн хувьд хангагдсан байх ёстой нөхцөлүүд юм.
SUM функцийг SQL Server (Transact-SQL)-ийн дараах хувилбаруудад ашиглаж болно:
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
SQL Server (Transact-SQL) дээр SUM функцийг хэрхэн ашиглахыг ойлгохын тулд SQL Server SUM функцийн зарим жишээг харцгаая.
Жишээлбэл, тоо хэмжээ нь 10-аас дээш байгаа бүх бүтээгдэхүүний нийт тоог олж мэдэх боломжтой.
SUM функцийн энэ жишээнд бид SUM(тоо хэмжээ) "Нийт Тоо хэмжээ" гэсэн илэрхийллийг нэрлэсэн. Үр дүнгийн багцыг буцаах үед талбарын нэрээр "Нийт тоо хэмжээ" гарч ирнэ.
Та SUM функцэд DISTINCT операторыг ашиглаж болно. Жишээлбэл, доорх SQL мэдэгдэл нь цалин нь жилд 29,000 доллараас бага цалингийн өвөрмөц утгатай нийт цалинг буцаана.
Хэрэв хоёр цалин нь жилд 24,000 доллар байсан бол эдгээр утгуудын зөвхөн нэгийг нь SUM функцэд ашиглах болно.
SUM функцэд агуулагдах илэрхийлэл нь нэг талбар байх албагүй. Та мөн томъёог ашиглаж болно. Жишээлбэл, та нийт комиссыг тооцоолж болно.
Transact-SQL
Захиалгагаас "Нийт Комисс" ГЭЖ СОНГОХ СУМ(борлуулалт * 0.03);
СОНГОХ SUM (борлуулалт * 0.03 ) "Нийт комисс" захиалгаас; |
Зарим тохиолдолд та SUM функцтэй GROUP BY операторыг ашиглах хэрэгтэй болно.
Үнээс MIN(үнэ), MAX(үнэ), AVG(үнэ) СОНГОХ;
Одоо бид "Хэвлэх байшин" (id=2) нийлүүлэгчээс хэдэн төгрөгийн бараа авчирсныг тодруулахыг хүсч байна. Ийм хүсэлт гаргах нь тийм ч хялбар биш юм. Үүнийг хэрхэн зохиох талаар бодож үзье:
1. Эхлээд "Хэвлэх үйлдвэр" (id=2) ханган нийлүүлэгчийн гүйцэтгэсэн хүргэлтийн тодорхойлогчийг (id_incoming) "Нилүүлэлтийн (ирж буй)" хүснэгтээс сонгоно уу:
2. Одоо нийлүүлэлтийн журналын хүснэгтээс (сэтгүүлийн_орж) 1-р зүйлд заасан хүргэлтэд хийгдсэн бараа (id_бүтээгдэхүүн) болон тэдгээрийн тоо хэмжээ (тоо хэмжээ) -ийг сонгох хэрэгтэй. Өөрөөр хэлбэл, 1-р цэгийн асуулга үүрлэсэн болно:
3. Одоо бид Үнийн хүснэгтэд хадгалагдсан олдсон бүтээгдэхүүний үнийг үр дүнгийн хүснэгтэд нэмэх хэрэгтэй. Өөрөөр хэлбэл, бид id_product баганыг ашиглан Хангамжийн сэтгүүл (сэтгүүлийн_орж байгаа) болон үнийн хүснэгтэд нэгдэх шаардлагатай болно:
4. Үүссэн хүснэгтэд Дүн багана тодорхой дутагдалтай байна, өөрөөр хэлбэл тооцоолсон багана. Ийм багана үүсгэх чадварыг MySQL-д өгсөн. Үүнийг хийхийн тулд та асуулгад тооцоолсон баганын нэр, юуг тооцоолох ёстойг зааж өгөх хэрэгтэй. Бидний жишээн дээр ийм баганыг нийлбэр гэж нэрлэх бөгөөд энэ нь тоо хэмжээ, үнийн баганын бүтээгдэхүүнийг тооцоолох болно. Шинэ баганын нэрийг AS гэсэн үгээр тусгаарлана:
сэтгүүлийн_орж буй.id_бүтээгдэхүүн, сэтгүүлийн_орж буй.тоо хэмжээ, үнэ.үнэ, сэтгүүлийн_орж буй.тоо хэмжээ*үнэ.үнэ Сэтгүүлээс ирсэн, үнэ ХААНА сэтгүүлийн_орж буй.id_product= үнэ.id_бүтээгдэхүүн БА id_incoming=ирж буй WHERE (SELECT_incoming.id_or2);
5. Гайхалтай, бидний хийх ёстой зүйл бол нийлбэр баганыг нэмж, эцэст нь нийлүүлэгч "Хэвлэх" компани бидэнд барааг хэдэн төгрөгөөр авчирсныг олж мэдэх явдал юм. SUM() функцийг ашиглах синтакс дараах байдалтай байна.
Хүснэгтийн нэрээс нийлбэр(баганын_нэр) СОНГОХ;
Бид баганын нэрийг мэддэг - summa, гэхдээ энэ нь асуулгын үр дүн тул хүснэгтийн нэр байхгүй байна. Юу хийх вэ? Ийм тохиолдолд MySQL нь Views-тэй байдаг. Харагдах байдал нь өвөрмөц нэр өгсөн сонголтын асуулга бөгөөд дараа нь ашиглах зорилгоор мэдээллийн санд хадгалагдах боломжтой.
Харагдах байдлыг үүсгэх синтакс нь дараах байдалтай байна.
CREATE VIEW view_name AS хүсэлт;
Бид хүсэлтээ report_vendor нэртэй харагдац болгон хадгалцгаая:
Report_vendor-г сэтгүүлийн_ирж буй.id_бүтээгдэхүүн, сэтгүүлийн_орж буй.тоо хэмжээ, үнэ.үнэ, сэтгүүлийн_орж буй.тоо хэмжээ*үнэ.үнэ Сэтгүүлээс ирсэн, үнэ ХААНА сэтгүүлийн_орж буй.id_product=WHERE үнэ (ANDHERE_incoming.id_product= WHERE үнэ. SELECT.id_productincom-д) ҮЗЭХ. _vendor=2 );
6. Одоо та SUM() гэсэн эцсийн функцийг ашиглаж болно:
report_vendor-оос нийлбэр(нийлбэр) СОНГОХ;
Тиймээс бид үр дүнд хүрсэн, гэхдээ үүний тулд бид үүрлэсэн асуулга, нэгдэл, тооцоолсон багана, харагдацыг ашиглах шаардлагатай болсон. Тийм ээ, заримдаа та үр дүнд хүрэхийн тулд бодох хэрэгтэй, үүнгүйгээр та хаашаа ч хүрч чадахгүй. Гэхдээ бид маш чухал хоёр сэдвийг хөндсөн - тооцоолсон багана, үзэл бодол. Тэдний талаар илүү дэлгэрэнгүй ярилцъя.
SELECT баганын_нэр 1, баганын_нэр 2, баганын_нэр 1 * баганын_нэр 2 AS тооцоолсон_баганын_нэр хүснэгтийн_нэрээс;
Хоёрдахь нюанс бол AS түлхүүр үг бөгөөд бид үүнийг тооцоолсон баганын нэрийг тохируулахад ашигласан. Үнэн хэрэгтээ энэ түлхүүр үг нь ямар ч баганад өөр нэр тавихад хэрэглэгддэг. Энэ яагаад хэрэгтэй вэ? Кодыг багасгах, уншихад тохиромжтой. Жишээлбэл, бидний үзэл бодол дараах байдалтай байж болно.
A.id_product, A.quaantity, B.price, A.quaantity*B.price AS summa AS SECO VIEW report_vendor A.id_product, A.quaantity, B.price AS summagazin_incoming A AS, үнэ B AS HHERE A.id_product= B.id_product AND id_incoming= (ирж буй id_id_FROM-аас сонго. WHERE id_vendor=2);
Энэ нь илүү богино бөгөөд ойлгомжтой гэдэгтэй санал нэг байна.
Гэхдээ харагдац нь хүснэгт биш, өөрөөр хэлбэл өгөгдөл хадгалахгүй, зөвхөн бусад хүснэгтээс олж авдаг гэдгийг санаарай. Тиймээс, нэгдүгээрт, хүснэгтийн өгөгдөл өөрчлөгдөхөд танилцуулгын үр дүн ч өөрчлөгдөнө. Хоёрдугаарт, харагдац руу хүсэлт гаргахад шаардлагатай өгөгдлийг хайдаг, өөрөөр хэлбэл DBMS-ийн гүйцэтгэл буурдаг. Тиймээс та тэднийг буруугаар ашиглах ёсгүй.
Дүгнэж сурцгаая. Үгүй ээ, эдгээр нь SQL-ийг судалсны үр дүн биш, харин өгөгдлийн сангийн хүснэгтийн баганын утгын үр дүн юм. SQL нэгтгэсэн функцууд нь баганын утгууд дээр ажилладаг бөгөөд үр дүнд нь нэг утгыг гаргадаг. Хамгийн түгээмэл хэрэглэгддэг SQL агрегат функцууд нь SUM, MIN, MAX, AVG, COUNT юм. Агрегат функцийг ашиглах хоёр тохиолдлыг ялгах шаардлагатай. Нэгдүгээрт, нэгтгэх функцийг дангаар нь ашигладаг бөгөөд үр дүнд нь нэг утгыг буцаана. Хоёрдугаарт, нэгтгэх функцийг SQL GROUP BY заалттай хамт ашигладаг, өөрөөр хэлбэл бүлэг тус бүр дэх үр дүнгийн утгыг авахын тулд талбаруудаар (баганаар) бүлэглэдэг. Эхлээд нэгтгэх функцийг бүлэглэхгүйгээр ашиглах тохиолдлыг авч үзье.
SQL SUM функц нь өгөгдлийн сангийн хүснэгтийн баганад байгаа утгуудын нийлбэрийг буцаана. Үүнийг зөвхөн тоон утга бүхий баганад ашиглах боломжтой. Үр дүнгийн нийлбэрийг авахын тулд SQL асуулга дараах байдлаар эхэлнэ.
НИЙТЛЭЛИЙГ СОНГОХ (COLUMN_NAME) ...
Энэ илэрхийллийн араас FROM (TABLE_NAME) бичигдсэн бөгөөд WHERE заалтыг ашиглан нөхцөлийг зааж өгч болно. Нэмж дурдахад, баганын нэрний өмнө DISTINCT тэмдэглэгдсэн байж болох бөгөөд энэ нь зөвхөн өвөрмөц утгуудыг тоолно гэсэн үг юм. Анхдагч байдлаар, бүх утгыг харгалзан үздэг (үүнд та DISTINCT биш, харин БҮХ гэж тусгайлан зааж өгч болно, гэхдээ БҮХ гэдэг үгийг оруулах шаардлагагүй).
Хэрэв та энэ хичээлээс MS SQL Server дээр өгөгдлийн сангийн асуулга ажиллуулахыг хүсэж байгаа боловч энэ DBMS таны компьютер дээр суулгаагүй бол энэ холбоос дээрх зааврыг ашиглан суулгаж болно. .
Эхлээд бид компанийн мэдээллийн сантай ажиллах болно - Company1. Энэхүү өгөгдлийн сан, түүний хүснэгтүүдийг үүсгэх, хүснэгтүүдийг өгөгдөлөөр дүүргэх скрипт нь энэ холбоос дээрх файлд байна .
Жишээ 1.Компанийн хэлтэс, ажилчдын талаархи мэдээлэл бүхий мэдээллийн сан байдаг. Ажилтны хүснэгт нь ажилчдын цалингийн талаархи мэдээлэл бүхий баганатай байдаг. Хүснэгтээс сонголт дараах байдалтай байна (зураг томруулахын тулд хулганы зүүн товчийг дарна уу):
Бүх цалингийн нийлбэрийг авахын тулд бид дараах асуулгыг ашиглана (MS SQL Server дээр - өмнөх барилгын USE company1;):
Ажилтнуудаас СОНГО (Цалин).
Энэ асуулга нь 287664.63 утгыг буцаана.
Одоо . Дасгал дээр бид даалгавруудыг аль хэдийн хүндрүүлж, практик дээр тулгарсан ажлуудад ойртуулж эхэлж байна.
SQL MIN функц нь утгууд нь тоонууд дээр ажилладаг ба баганын бүх утгын хамгийн бага утгыг буцаана. Энэ функц нь SUM функцтэй төстэй синтакстай.
Жишээ 3.Өгөгдлийн сан болон хүснэгт нь 1-р жишээтэй ижил байна.
Бид 42 дугаар хэлтсийн ажилчдын хөдөлмөрийн хөлсний доод хэмжээг олж мэдэх хэрэгтэй. Үүнийг хийхийн тулд дараах асуултыг бичнэ үү (MS SQL Server дээр - USE company1 угтвартай);
Асуулга нь 10505.90 утгыг буцаана.
Бас дахин зориулсан дасгал бие даасан шийдвэр . Энэ болон бусад дасгалуудад танд зөвхөн Ажилтны хүснэгт төдийгүй компанийн хэлтсийн талаархи мэдээллийг агуулсан Байгууллагын хүснэгт хэрэгтэй болно.
Жишээ 4.Байгууллагын хүснэгтийг ажилтнуудын хүснэгтэд нэмж, компанийн хэлтэсүүдийн талаархи мэдээллийг агуулсан болно. Бостонд байрлах хэлтэст нэг ажилтны ажилласан хамгийн бага жилийн тоог хэвлэ.
SQL MAX функц нь ижил төстэй ажилладаг бөгөөд ижил төстэй синтакстай бөгөөд энэ нь баганын бүх утгуудын хамгийн их утгыг тодорхойлох шаардлагатай үед ашиглагддаг.
Жишээ 5.
Бид 42 дугаар хэлтсийн ажилчдын цалингийн дээд хэмжээг олж мэдэх хэрэгтэй. Үүнийг хийхийн тулд дараах асуултыг бичнэ үү (MS SQL Server дээр - USE company1 угтвартай);
Асуулга нь 18352.80 утгыг буцаана
Цаг нь боллоо бие даасан шийдэлд зориулсан дасгалууд.
Жишээ 6.Бид дахин хоёр хүснэгттэй ажилладаг - Ажилтнууд болон Орг. Хэлтсийн нэр, Дорнодын хэлтэс (хэлтэс) бүлэгт хамаарах хэлтсийн нэг ажилтны хүлээн авсан комиссын хамгийн их утгыг харуулна. Ашиглах НЭГДЭХ (хүснэгтүүдийг нэгтгэх) .
Өмнө нь тайлбарласан функцүүдийн синтаксийн талаар заасан зүйл нь SQL AVG функцийн хувьд мөн үнэн юм. Энэ функц нь баганын бүх утгын дундажийг буцаана.
Жишээ 7.Өгөгдлийн сан болон хүснэгт нь өмнөх жишээнүүдийн адил байна.
Та 42-р хэлтсийн ажилчдын дундаж ажилласан хугацааг мэдэхийг хүсч байна гэж бодъё. Үүнийг хийхийн тулд дараах асуултыг бичнэ үү (MS SQL Server дээр - USE company1 гэсэн өмнөх бүтэцтэй);
Үр дүн нь 6.33 болно
Жишээ 8.Бид нэг ширээтэй ажилладаг - Ажилтнууд. 4-6 жилийн туршлагатай ажилчдын дундаж цалинг харуул.
SQL COUNT функц нь өгөгдлийн сангийн хүснэгт дэх бичлэгийн тоог буцаана. Хэрэв та асуулгад SELECT COUNT(COLUMN_NAME) ... гэж зааж өгвөл үр дүн нь баганын утга NULL (тодорхойгүй) байгаа бичлэгүүдийг харгалзахгүйгээр бичлэгийн тоо байх болно. Хэрэв та одыг аргумент болгон ашиглаад SELECT COUNT(*) ... асуулга эхлүүлбэл үр дүн нь хүснэгтийн бүх бичлэгийн (мөр) тоо болно.
Жишээ 9.Өгөгдлийн сан болон хүснэгт нь өмнөх жишээнүүдийн адил байна.
Та шимтгэл авдаг бүх ажилчдын тоог мэдэхийг хүсч байна. Comm баганын утгууд NULL биш ажилчдын тоог дараах асуулгад буцаана (MS SQL Server дээр - USE company1 угтвартай);
Ажилтнаас COUNT (Comm) СОНГОХ
Үр дүн нь 11 болно.
Жишээ 10.Өгөгдлийн сан болон хүснэгт нь өмнөх жишээнүүдийн адил байна.
Хэрэв та хүснэгтэд байгаа нийт бичлэгийн тоог мэдэхийг хүсвэл COUNT функцийн аргумент болгон одоор тэмдэглэсэн асуулга ашиглана уу (MS SQL Server дээр - USE company1 гэсэн өмнөх бүтэцтэй);
Ажилтнаас COUNT (*) СОНГОХ
Үр дүн нь 17 болно.
Дараагийнх нь бие даасан шийдэл гаргах дасгалта дэд асуулга ашиглах хэрэгтэй болно.
Жишээ 11.Бид нэг ширээтэй ажилладаг - Ажилтнууд. Төлөвлөлтийн хэлтсийн ажилчдын тоог харуулах (Plains).
Одоо нэгтгэсэн функцуудыг SQL GROUP BY мэдэгдлийн хамт ашиглахыг харцгаая. SQL GROUP BY мэдэгдлийг өгөгдлийн сангийн хүснэгтийн үр дүнгийн утгыг баганаар бүлэглэхэд ашигладаг. вэб сайт байна энэ операторт тусад нь зориулсан хичээл .
Бид "Зар сурталчилгааны портал 1" мэдээллийн сантай ажиллах болно. Энэхүү мэдээллийн сан, түүний хүснэгт, өгөгдлийн хүснэгтийг бөглөх скрипт нь энэ холбоос дээрх файлд байна .
Жишээ 12.Тэгэхээр зар сурталчилгааны портал мэдээллийн сан бий. Энэ нь долоо хоногт оруулсан зарын мэдээллийг агуулсан Зарын хүснэгттэй. Ангилал багана нь зар сурталчилгааны томоохон категорийн талаарх мэдээллийг (жишээ нь, Үл хөдлөх хөрөнгө), эд анги баганад ангилалд багтсан жижиг хэсгүүдийн талаарх мэдээллийг агуулна (жишээ нь, Орон сууц, Зуслангийн байшингийн хэсэг нь Үл хөдлөх хөрөнгийн ангиллын хэсэг). Нэгж баганад ирүүлсэн зарын тоо, Мөнгө баганад сурталчилгаа явуулахад хүлээн авсан мөнгөний дүнгийн мэдээллийг агуулна.
Ангилал | Хэсэг | Нэгж | Мөнгө |
Тээвэрлэлт | Машинууд | 110 | 17600 |
Үл хөдлөх хөрөнгө | Орон сууц | 89 | 18690 |
Үл хөдлөх хөрөнгө | Дача | 57 | 11970 |
Тээвэрлэлт | Мотоцикл | 131 | 20960 |
Барилгын материал | Самбарууд | 68 | 7140 |
Электроникийн инженер | ТВ | 127 | 8255 |
Электроникийн инженер | Хөргөгч | 137 | 8905 |
Барилгын материал | Регипүүд | 112 | 11760 |
Чөлөөт цаг | Номууд | 96 | 6240 |
Үл хөдлөх хөрөнгө | Гэртээ | 47 | 9870 |
Чөлөөт цаг | Хөгжим | 117 | 7605 |
Чөлөөт цаг | Тоглоомууд | 41 | 2665 |
SQL GROUP BY мэдэгдлийг ашиглан ангилал тус бүрээр зар байршуулж олсон мөнгөний хэмжээг олоорой. Бид дараах асуулгыг бичнэ (MS SQL сервер дээр - USE adportal1 өмнөх бүтэцтэй);
Ангилал, нийлбэр (мөнгө)-ийг зарын бүлгээс мөнгө болгон сонго
Жишээ 13.Өгөгдлийн сан болон хүснэгт нь өмнөх жишээтэй ижил байна.
SQL GROUP BY мэдэгдлийг ашиглан ангилал тус бүрийн аль хэсэгт хамгийн олон жагсаалт байгааг олж мэдээрэй. Бид дараах асуулгыг бичнэ (MS SQL сервер дээр - USE adportal1 өмнөх бүтэцтэй);
Ангилал, хэсэг, MAX (нэгж)-ийг ЗАРЫН БҮЛГЭЭС АНГИЛЛАГААР СОНГОХ
Үр дүн нь дараах хүснэгт болно.
Нийт болон бие даасан утгыг нэг хүснэгтээс авах боломжтой UNION оператор ашиглан асуулгын үр дүнг нэгтгэх .
Харилцааны мэдээллийн сан ба SQL хэл