Forum

Forumlar
Microsoft
SQL Server
Between ile çoklu S...
 
Bildirimler
Hepsini Temizle

Between ile çoklu Sorgu

 
SQL Server
Son Cevaplar gön: Can Kaya 6 yıl önce
7 Yazılar
3 Üyeler
0 Reactions
1,010 Görüntüleme
RSS
 Nail İŞLER
(@nailisler)
Gönderiler: 152
Estimable Member
Konu başlatıcı
 

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,

 
Gönderildi : 05/03/2019 14:28

 İsmail ADAR
(@ismailadar)
Gönderiler: 134
Estimable Member
 

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] 

 
Gönderildi : 05/03/2019 14:48

 Nail İŞLER
(@nailisler)
Gönderiler: 152
Estimable Member
Konu başlatıcı
 

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.

 
Gönderildi : 05/03/2019 15:21

 Nail İŞLER
(@nailisler)
Gönderiler: 152
Estimable Member
Konu başlatıcı
 

İ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] 

 
Gönderildi : 05/03/2019 15:58

 İsmail ADAR
(@ismailadar)
Gönderiler: 134
Estimable Member
 

Merhaba 

sorgunuz dogru gorunuyor. elinizde ornek veri varsa ben de deneyebilirim

 
Gönderildi : 05/03/2019 16:41

 Nail İŞLER
(@nailisler)
Gönderiler: 152
Estimable Member
Konu başlatıcı
 

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,

 
Gönderildi : 05/03/2019 17:32

 Can Kaya
(@cankaya)
Gönderiler: 119
Üye
 

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)

 

 
Gönderildi : 06/03/2019 23:50

Forum Jump:
  Önceki Konu
Sonraki Konu  
Paylaş: