Forum

Between ile çoklu S...
 
Bildirimler
Hepsini Temizle

Between ile çoklu Sorgu

Nail İŞLER
(@nailisler)
Üye

Merhaba,
Bir proje için ip adreslerinin proxy kullanıyor mu kontrol etmeye çalışıyorum. Elimde bakılacak yaklaşık 1000 i aşkın ip adresi var. İnternette baktığımda bu işlem için ücretsiz hizmet veren https://lite.ip2location.com/database/px4-ip-proxytype-country-region-city-isp adresine ulaştım ve bu adreste ki bilgilere göre databaseyi oluşturdum ve verdikleri komut ile ip istedikleri formatta convert ettim.

buraya kadar ok fakat söyle bir sorun oluştu  :

select * from [ip2location_db3] where {DottedIP} BETWEEN [ip_from] and [ip_to] 

şeklinde tek tek ip 'lere ait bilgileri görebiliyorum. ama bin tane satir için tek tek okumak işlem yapmak çok uzun sürmek te işlemi In komutu gibi tek seferde alma şansım var mıdır?

teşekkürler,

Alıntı
Konu başlatıcı Gönderildi : 05/03/2019 14:28
İsmail ADAR
(@ismailadar)
Üye

Merhaba,

Elinizdeki ip adreslerini({DottedIP}) bir tane table variable veya temp tabloya insert ederek join ile yapabilirsinz. Ornegin ;

declare @data table(IpAddr varchar(100))
insert into @data values('192.168.1.1'),('192.168.1.3'),('192.168.1.5'),

select * from [ip2location_db3] as p , @data d
where d.IpAddr BETWEEN [ip_from] and [ip_to] 

CevapAlıntı
Gönderildi : 05/03/2019 14:48
Nail İŞLER
(@nailisler)
Üye

Yöntem için teşekkürler İsmail bey 
Vb.net üzerinden ado.net ile db bağlantısı yaptım ve declare komutu desteklenmiyor fakat altarnatif olarak internette araştırma yapacağım.

CevapAlıntı
Konu başlatıcı Gönderildi : 05/03/2019 15:21
Nail İŞLER
(@nailisler)
Üye

İsmail bey merhaba, 
Söyle bi yöntem buldum ama beceremedim galiba,

CREATE TABLE [data] ([IpAddr] float NOT NULL); ile yeni bir tablo açtım,
ve 
INSERT INTO [data] ([IpAddr]) VALUES (ipadresi); ..... gibi 1000 satırı ekledim. ve sorguyu yazdım ama sonuç çıkmadı hatayı nerede yapmış olabilirim.

SELECT * FROM [ip2proxy_px4] as p, data as d where d.Ipaddr BETWEEN [ip_from] and [ip_to] 

CevapAlıntı
Konu başlatıcı Gönderildi : 05/03/2019 15:58
İsmail ADAR
(@ismailadar)
Üye

Merhaba 

sorgunuz dogru gorunuyor. elinizde ornek veri varsa ben de deneyebilirim

CevapAlıntı
Gönderildi : 05/03/2019 16:41
Nail İŞLER
(@nailisler)
Üye

2 tabloyu size ileteceğim. fakat proxy olduğu (yukarıda linki verdiğim db 7.5m satır var onun için orada ki ilk birkaç satırı yazacağım.


IP Bulunduğu DB;
DROP TABLE [data];
GO
CREATE TABLE [data] (
[IpAddr] float NOT NULL
);
GO

Proxy'lerin bulundu DB;
DROP TABLE [ip2proxy_px4];
GO
CREATE TABLE [ip2proxy_px4] (
  [ip_from] bigint NOT NULL
, [ip_to] bigint NOT NULL
, [proxy_type] nvarchar(3) NOT NULL
, [country_code] nvarchar(2) NOT NULL
, [country_name] nvarchar(64) NOT NULL
, [region_name] nvarchar(128) NOT NULL
, [city_name] nvarchar(128) NOT NULL
, [isp] nvarchar(256) NOT NULL
);
GO
CREATE INDEX [ip_to] ON [ip2proxy_px4] ([ip_from] ASC,[ip_to] ASC);
GO

Proxy db bulunan satirlar;
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16777216,16777216,N'PUB',N'AU',N'Australia',N'Queensland',N'Brisbane',N'APNIC and Cloudflare DNS Resolver project');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16777343,16777343,N'PUB',N'AU',N'Australia',N'Queensland',N'Brisbane',N'APNIC and Cloudflare DNS Resolver project');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16777392,16777392,N'PUB',N'AU',N'Australia',N'Queensland',N'Brisbane',N'APNIC and Cloudflare DNS Resolver project');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810009,16810009,N'PUB',N'TH',N'Thailand',N'Chiang Mai',N'Chiang Mai',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810016,16810016,N'PUB',N'TH',N'Thailand',N'Chiang Mai',N'Chiang Mai',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810029,16810029,N'PUB',N'TH',N'Thailand',N'Chiang Mai',N'Chiang Mai',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810077,16810077,N'PUB',N'TH',N'Thailand',N'Chiang Mai',N'Chiang Mai',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810085,16810085,N'PUB',N'TH',N'Thailand',N'Chiang Mai',N'Chiang Mai',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810088,16810088,N'PUB',N'TH',N'Thailand',N'Chiang Mai',N'Chiang Mai',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810122,16810122,N'PUB',N'TH',N'Thailand',N'Chiang Mai',N'Chiang Mai',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810137,16810137,N'PUB',N'TH',N'Thailand',N'Chiang Mai',N'Chiang Mai',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810160,16810160,N'PUB',N'TH',N'Thailand',N'Chiang Mai',N'Chiang Mai',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810199,16810199,N'PUB',N'TH',N'Thailand',N'Chiang Mai',N'Chiang Mai',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810213,16810213,N'PUB',N'TH',N'Thailand',N'Chiang Mai',N'Chiang Mai',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810232,16810232,N'PUB',N'TH',N'Thailand',N'Chiang Mai',N'Chiang Mai',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810235,16810235,N'PUB',N'TH',N'Thailand',N'Chiang Mai',N'Chiang Mai',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810770,16810770,N'PUB',N'TH',N'Thailand',N'Songkhla',N'Songkhla',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810793,16810793,N'PUB',N'TH',N'Thailand',N'Songkhla',N'Songkhla',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810802,16810802,N'PUB',N'TH',N'Thailand',N'Songkhla',N'Songkhla',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810817,16810817,N'PUB',N'TH',N'Thailand',N'Songkhla',N'Songkhla',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810960,16810960,N'PUB',N'TH',N'Thailand',N'Songkhla',N'Songkhla',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16810990,16810990,N'PUB',N'TH',N'Thailand',N'Songkhla',N'Songkhla',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16811000,16811000,N'PUB',N'TH',N'Thailand',N'Songkhla',N'Songkhla',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16811004,16811004,N'PUB',N'TH',N'Thailand',N'Songkhla',N'Songkhla',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16811025,16811025,N'PUB',N'TH',N'Thailand',N'Ang Thong',N'Wiset Chaichan',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16811034,16811034,N'PUB',N'TH',N'Thailand',N'Ang Thong',N'Wiset Chaichan',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16811036,16811036,N'PUB',N'TH',N'Thailand',N'Ang Thong',N'Wiset Chaichan',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16811053,16811053,N'PUB',N'TH',N'Thailand',N'Ang Thong',N'Wiset Chaichan',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16811056,16811056,N'PUB',N'TH',N'Thailand',N'Ang Thong',N'Wiset Chaichan',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16811079,16811080,N'PUB',N'TH',N'Thailand',N'Ang Thong',N'Wiset Chaichan',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16811083,16811083,N'PUB',N'TH',N'Thailand',N'Ang Thong',N'Wiset Chaichan',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16811100,16811100,N'PUB',N'TH',N'Thailand',N'Ang Thong',N'Wiset Chaichan',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16811102,16811102,N'PUB',N'TH',N'Thailand',N'Ang Thong',N'Wiset Chaichan',N'TOT Public Company Limited');
GO
INSERT INTO [ip2proxy_px4] ([ip_from],[ip_to],[proxy_type],[country_code],[country_name],[region_name],[city_name],[isp]) VALUES (
16811115,16811115,N'PUB',N'TH',N'Thailand',N'Ang Thong',N'Wiset Chaichan',N'TOT Public Company Limited');
GO

IP tablosunda olan veriler;
INSERT INTO [data] ([IpAddr]) VALUES (
16811100);
GO

INSERT INTO [data] ([IpAddr]) VALUES (
16811115);
GO

INSERT INTO [data] ([IpAddr]) VALUES (
16811025);
GO

INSERT INTO [data] ([IpAddr]) VALUES (
16811083);
GO

 

 

desteğiniz için teşekkürler,

CevapAlıntı
Konu başlatıcı Gönderildi : 05/03/2019 17:32
Can Kaya
(@cankaya)
Üye Forum Yöneticisi

Merhaba,

 

ipFrom ve ipTo veriler aynıydı örnekte aynı olmadığını varsayarak böyle bir join işinizi görecektir. 

 

select a.* from [dbo].[ip2proxy_px4] a
INNER JOIN [dbo].[data] b ON a.ip_from>=b.IpAddr and a.ip_from<=b.IpAddr

 

data tablosunu truncate edip üzerine datalarınızı insert edip yukardaki sorguyu koşturabilirsiniz. ADO.Net' in Temp table gibi bir kısıtlaması olmamalı. Elinizde kayıt seti varsa havada bu data tablosunu oluşturmak daha mantıklı. 

şu statement belki işinizi görür.

select * from (VALUES ('16811100'),('16811115')) as data(IpAddr)

 

CevapAlıntı
Gönderildi : 06/03/2019 23:50
Paylaş: