<\/div>\n

Bu yaz\u0131m\u0131zda da T-SQL’e devam ediyoruz.Transact Sql de yard\u0131mc\u0131 olacak bir \u00e7ok teferruata de\u011finelim.
\nUNION – UNION ALL<\/strong><\/p>\n

Birden fazla select sorgusunun sonucunu tek seferde alt alta g\u00f6stermemize yarar.\u00d6rne\u011fin,tablonun birinde 5 di\u011ferinde 7 kay\u0131t geliyorsa,bunlar\u0131 tek tablo halinde 12 kay\u0131t olarak g\u00f6sterir.<\/p>\n

\n
\r\nselect Adi from Personeller\r\nunion\r\nselect Soyadi from Personeller\r\n<\/pre>\n
\n
Dikkat etmemiz gereken ko\u015fullar;<\/p>\n
\n
Union sorgusunun sonucunda olu\u015fan tablonun kolon isimleri, \u00fcstteki sorgunun kolon isimlerinden olu\u015fturulur.<\/li>\n
\u00dcstteki sorgudan ka\u00e7 kolon \u00e7ekilmi\u015fse alttaki sorgudan da o kadar \u00e7ekilmek zorundad\u0131r.<\/li>\n
\u00dcstteki sorgudan \u00e7ekilen kolonlar\u0131n tipleriyle,alttaki sorgudan \u00e7ekilen kolonlar\u0131n tipleriyle uyumlu olmal\u0131d\u0131r.<\/li>\n
Joinler yan yana,Union alt alta birle\u015ftirir.Joinlerde belirli bir kolon \u00fczerinden birle\u015ftirirken,Union da b\u00f6yle bir durum yoktur.<\/li>\n
Union tekrarl\u0131 kay\u0131tlar\u0131 getirmez.Tekrarl\u0131 kay\u0131tlar\u0131 getirmek i\u00e7in Union All komutu kullan\u0131lmal\u0131d\u0131r.<\/li>\n<\/ul>\n
Union da kullan\u0131lan tablolara kolon eklenebilir.Dikkat etmemiz gereken nokta a\u015fa\u011f\u0131yada yukar\u0131yada ayn\u0131 kolondan eklememizdir.<\/p>\n
\n
\r\nselect SirketAdi,Telefon,'Nakliyeci' from Nakliyeciler\r\nunion\r\nselect MusteriAdi,Telefon,'M\u00fc\u015fteri' from Musteriler\r\n<\/pre>\n
\n
WITH ROLLUP, WITH CUBE<\/strong><\/p>\n
WITH ROLLUP :<\/strong> group by ile gruplanm\u0131\u015f veri k\u00fcmesinde ara toplam al\u0131nmas\u0131n\u0131 sa\u011flar.
\nWITH CUBE :<\/strong> group by ile gruplanm\u0131\u015f veri k\u00fcmesinde teker teker toplam al\u0131nmas\u0131n\u0131 sa\u011flar.<\/p>\n
\n
\r\nselect SatisID,UrunID,SUM(Miktar) from [Satis Detaylari]\r\nwhere SatisID < 10251\r\ngroup by SatisID,UrunID with rollup\r\nselect SatisID,UrunID,SUM(Miktar) from [Satis Detaylari]\r\nwhere SatisID < 10251\r\ngroup by SatisID,UrunID with rollup\r\nhaving SUM(Miktar)>40 and SatisID is not null\r\n<\/pre>\n
\n
Dikkat ettiyseniz group by dan hemen sonra kullan\u0131yoruz.<\/p>\n
\n
\r\nselect SatisID,UrunID,SUM(Miktar) from [Satis Detaylari]\r\nwhere SatisID < 10251\r\ngroup by SatisID,UrunID with cube\r\nhaving SatisId is null and UrunID = 51\r\norder by SatisID,UrunId\r\n<\/pre>\n
\n
CASE – WHEN – ELSE \u2013 END<\/strong>
\nPersonellerimin isim ve soyisiminin yan\u0131nda, UnvanEki Mr. ise ERKEK, Mrs. ya da Ms. ise KADIN yazs\u0131n.<\/p>\n
\n
\r\nselect case when UnvanEki = 'Mrs.' or UnvanEki = 'Ms.' then 'Kad\u0131n'\r\nwhen UnvanEki = 'Mr.' then 'Erkek'\r\nelse UnvanEki\r\nend\r\nfrom Personeller\r\n<\/pre>\n
\n
E\u011fer \u00fcr\u00fcn\u00fcm\u00fcn birim fiyat\u0131 0 – 50 aras\u0131 ise \u00c7\u0130N MALI, 50 – 100 aras\u0131 ise UCUZ, 100 – 200 aras\u0131 ise NORMAL , 200 den fazla ise PAHALI yazs\u0131n.<\/p>\n
\n
\r\nselect UrunAdi,\r\ncase when BirimFiyati between 0 and 50 then '\u00c7\u00ddN MALI'\r\nwhen BirimFiyati between 50 and 100 then 'UCUZ'\r\nwhen BirimFiyati between 100 and 200 then 'NORMAL'\r\nwhen BirimFiyati >200 then 'PAHALI'\r\nelse convert(nvarchar(40),BirimFiyati) end as 'Durum'\u2014Bu kolona isim verir.\r\nfrom Urunler\r\n<\/pre>\n
\n
WITH TIES<\/strong><\/p>\n
\n
\r\nselect top 6 * from [Satis Detaylari]\r\n<\/pre>\n
\n
Yukar\u0131daki yap\u0131da \u201cSatis Detaylar\u0131\u201d adl\u0131 tablodan ilk 6 veri gelecektir.Ancak bu verilerden sonuncu olan\u0131 \u201c10250\u201d nolu kay\u0131t\u0131n iki adeti d\u0131\u015farda kalmaktad\u0131r.Bu ikisinide getirmek i\u00e7in \u201cWITH TIES\u201d yap\u0131s\u0131n\u0131 kullanmaktay\u0131z.<\/p>\n
With Ties order by ile s\u0131ralanm\u0131\u015f kolonda, en son kayda bakar, e\u011fer onun alt\u0131nda ayn\u0131 nolu kay\u0131t varsa onu da getirir.<\/p>\n
Bu yap\u0131y\u0131 kullanabilmek i\u00e7in tek \u015fart order by kullan\u0131lmal\u0131d\u0131r.<\/p>\n
\n
\r\nselect top 6 with ties * from [Satis Detaylari] order by SatisID\r\n<\/pre>\n
\n
WITH<\/strong>
\nBu yap\u0131yla ilgili direkt bir \u00f6rnek sunuyorum ve incelemenizi tavsiye ediyorum.<\/p>\n
\n
\r\nwith sonuc(isim,id)\r\nas\r\n(\r\nselect Musteriler.MusteriAdi,Satislar.SatisID from Musteriler inner join Satislar\r\non Musteriler.MusteriID=Satislar.MusteriID\r\n)\r\nselect sonuc.isim,sonuc.id from sonuc inner join [Satis Detaylari] on sonuc.id=[Satis Detaylari].SatisID\r\n<\/pre>\n
\n
SUBQUERY(\u0130\u00c7 \u0130\u00c7E SORGU YAZMA TEKN\u0130\u011e\u0130)<\/strong><\/p>\n
Baz\u0131 durumlarda i\u00e7 i\u00e7e sorgu yazmam\u0131z gerekebilir.Bu durumda Subquery tekni\u011fini kullanmal\u0131y\u0131z.
\nAd\u0131 Nancy olan personelimin yapt\u0131\u011f\u0131 sat\u0131\u015flar ve Nakliye \u00dccretleri ?
\nBu soruyu ilk \u00f6nce inner join ile yapal\u0131m.<\/p>\n
\n
\r\nselect Satislar.SatisID,Satislar.NakliyeUcreti from Personeller inner join Satislar on Personeller.PersonelID=Satislar.PersonelID where Personeller.Adi='nancy'\r\n<\/pre>\n
\n
\u015eimdide subquery y\u00f6ntemiyle yapal\u0131m..<\/p>\n
\n
\r\nselect SatisID,NakliyeUcreti from Satislar where PersonelID =(select PersonelID from Personeller where Adi = 'nancy')\r\n<\/pre>\n
\n
Aradaki fark\u0131 g\u00f6rebiliyorsunuzdur.Subquery tekni\u011fini kullanarakta,inner joinle yapt\u0131klar\u0131m\u0131z\u0131 yapabiliriz.
\nUnvanEki doktor olan \u00e7al\u0131\u015fan\u0131m\u0131n yapm\u0131\u015f oldu\u011fu sat\u0131\u015flar (Personeller, Satislar)<\/p>\n
\n
\r\nselect SatisID from Satislar where PersonelID=(select PersonelID from Personeller where UnvanEki='Dr.')\r\n<\/pre>\n
\n","protected":false},"excerpt":{"rendered":"
Bu yaz\u0131m\u0131zda da T-SQL’e devam ediyoruz.Transact Sql de yard\u0131mc\u0131 olacak bir \u00e7ok teferruata de\u011finelim.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22],"tags":[37,90,91,92,85,93,94,95,96,97,98,99],"class_list":["post-162","post","type-post","status-publish","format-standard","hentry","category-veritabani","tag-case","tag-else","tag-end","tag-subquery","tag-transact-sql","tag-union","tag-union-all","tag-when","tag-with","tag-with-cube","tag-with-rollup","tag-with-ties"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/162","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/comments?post=162"}],"version-history":[{"count":0,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/162\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/media?parent=162"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/categories?post=162"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/tags?post=162"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}