[Outros] Consultas (query) L2OFF

[SOLITARIO]

Uma pequena ajuda para quem precisa...
espero poder ajudar alguem.

/*Procura item pela quantidade*/
select dbo.user_item.item_id, dbo.user_item.char_id, dbo.user_item.amount, dbo.user_data.char_name, dbo.user_item.enchant from dbo.user_item join dbo.user_data on dbo.user_item.char_id = dbo.user_data.char_id
where item_type = 5643 and amount > 10

/*lista as contas de gm*/
select char_name, account_name, builder from user_data where builder <> 0


/*Mover char */
declare @idconta int declare @charname varchar(20) declare @conta varchar(20)
select @charname = 'HulkZinha' -- <-nome do char que vai ser tranferido
select @conta = 'bebo4' -- <- nome da conta para onde vai o char
select @idconta = uid from lin2db.dbo.user_account where account = @conta
if @idconta > 0 update user_data set account_name = @conta, account_id = @idconta where char_name = @charname

/* insere nobre */
insert user_nobless (char_id, nobless_type, hero_type, olympiad_point)
select char_id, 1, 0, 0 from user_data where char_name = 'FininhoW'

/*checa o status de um char*/
select account_name conta, char_name char, block_flag2 banido, block_start_date inicio from lin2db.dbo.user_account join user_data on uid = account_id
where char_name = '^^'

/*Encontra a conta de um char*/
select account_name, account_id from user_data where char_name = 'nome';


/*DESBANIR 1 CONTA:*/
update lin2db.dbo.user_account set warn_flag = 0, block_flag = 0, block_flag2 = 0, block_end_date = null, block_start_date = 0
where uid = (select account_id from user_data where char_name = 'aconta');


/*BANIR 1 CONTA:*/
update lin2db.dbo.user_account set warn_flag = 1, block_flag = 1, block_flag2 = 1, block_end_date = '2009-08-12 19:49:21.000', block_start_date = getdate()
where uid = (select account_id from user_data where char_name = 'aconta');


/**PELA CONTA ** BANIR 1 CONTA:**/
update lin2db.dbo.user_account set warn_flag = 1, block_flag = 1, block_flag2 = 1, block_end_date = '2009-08-12 19:49:21.000', block_start_date = getdate()
where account = 'conta'

/**PELA CONTA **DESBANIR 1 CONTA:**/
update lin2db.dbo.user_account set warn_flag = 0, block_flag = 0, block_flag2 = 0, block_end_date = null, block_start_date = 0
where account = 'conta'


/*Encontra itens enchantados*/
select ui.item_id, ui.char_id, ui.amount, ud.char_name, ui.enchant from dbo.user_item ui join dbo.user_data ud on ui.char_id = ud.char_id
where item_type = 5643 and enchant > 4;

/*TROCAR SENHA*/
update lin2db.dbo.user_auth set password = (select password from lin2db.dbo.user_auth
where account = 'tl2acc') where account = 'destino';


/*Busca IPs relacionados*/
select account_name, char_name, account_id, char_id, warn_flag, block_flag, block_flag2 from lin2db.dbo.user_account join user_data on uid = account_id where lin2db.dbo.user_account.last_ip = (
select lin2db.dbo.user_account.last_ip from lin2db.dbo.user_account where lin2db.dbo.user_account.account =
(select account_name from user_data where char_name = 'aconta') collate Korean_Wansung_CI_AS );


/*Busca SENHAS iguais */
select account_name, char_name, account_id, char_id, warn_flag, block_flag, block_flag2 from lin2db.dbo.user_account join
(user_data join lin2db.dbo.user_auth on ((account_name collate SQL_Latin1_General_CP1_CI_AS)= account))
on uid = account_id where password = (select password from lin2db.dbo.user_auth where account =
(select account_name from user_data where char_name = 'aconta')
collate SQL_Latin1_General_CP1_CI_AS) order by char_name


/*Lista CHARS logadas em um IP*/
select account_name, char_name from user_data where account_id in
(select uid from lin2db.dbo.user_account where last_ip = '127.0.0.1)

/*Lista os donos de CLAN HALL*/
select agit.name, pledge.name, char_name, warn_flag, block_flag, block_flag2, last_login
from lin2db.dbo.user_account join (user_data join (agit join pledge on agit.pledge_id = pledge.pledge_id) on ruler_id = char_id) on uid = account_id
where block_flag <> 5 order by last_login desc



/*top 10 pvp para os clans*/
select top 10 pvp = sum(duel), pk = sum(pk), name from user_data join pledge on user_data.pledge_id = pledge.pledge_id group by pledge.pledge_id, name order by pvp desc

/*top 10 pvp para as allys*/
select top 10 pvp = sum(duel), pk = sum(pk), alliance.name from alliance join (user_data join pledge on user_data.pledge_id = pledge.pledge_id)on alliance_id = alliance.id group by alliance.name order by pvp desc

--TOP CLANS
declare pledgegg cursor for
select top 20 pledge_id from pledge order by name_value desc
declare @pledge_id int
drop table #topclans
create table #topclans (id int, nome varchar(20), rep int, pvp int, pk int, castelo varchar(20), heros int)
open pledgegg
fetch next from pledgegg into @pledge_id

WHILE @@FETCH_STATUS = 0
BEGIN
insert into #topclans (id, nome, rep, pvp, pk, castelo,heros) select p.pledge_id, p.name, name_value, pvp = sum(duel), pk = sum(pk),castelo = (select name from castle where pledge_id = @pledge_id), herois = (select count(*) from user_nobless un where char_id in (select char_id from user_data where pledge_id = @pledge_id and hero_type > 0)) from user_data ud join pledge p on ud.pledge_id = p.pledge_id where p.pledge_id = @pledge_id group by p.pledge_id, name, name_value
fetch next from pledgegg into @pledge_id
END
close pledgegg
deallocate pledgegg
select * from #topclans


/*andamento olympiad*/
select char_name char, cl.name, count(*) wins, un.olympiad_point points
from class_list cl join (user_nobless un join(olympiad_match om join user_data ud on om.char_id = ud.char_id) on ud.char_id = un.char_id ) on cl.class = om.class
where is_winner = 1 and season = (select top 1 season from olympiad order by season desc)
group by om.char_id, cl.name, char_name, olympiad_point order by points desc, wins desc


/*Encontra um codigo na funcoes do banco de dados*/
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%foobar%'
AND ROUTINE_TYPE='PROCEDURE'

Creditos pra eu mesmo