Форум пользователей MySQL

alexmarch · 29.08.2009 15:26:13

Код:

SELECT DISTINCT
  accounts.login, accounts.lastIP, characters.account_name, characters.char_name, characters.online
FROM
  l2rt.accounts
  INNER JOIN l2rt.characters ON accounts.login = characters.account_name
WHERE
  characters.online = 1

Теперь мне нужно сравнить получившиеся две таблицы accounts - characters и goodboy так, что бы все значения котрые нету в goodboy-е попали в выборку.

Такая конструкция мне неподходит, потому как у меня непападет в выборку те значения котроые совпадат по lastIP. т.к. при обеденение таблиц он перед ключевое поле goodboy.id.

Код:

SELECT DISTINCT
  accounts.login, accounts.lastIP, characters.account_name, characters.char_name, characters.online, goodboy.lastIP, goodboy.lastactive
FROM
  l2rt.accounts
  INNER JOIN l2rt.characters ON accounts.login = characters.account_name
  LEFT OUTER JOIN l2rt.goodboy ON accounts.lastIP = goodboy.lastIP
WHERE
  characters.online = 1 AND goodboy.lastIP IS NOT NULL

3-ий день неполучаеться его осазнать... помогите...

Иными словами
после такого запроса

Код:

SELECT DISTINCT
  accounts.login, accounts.lastIP, characters.account_name, characters.char_name, characters.online
FROM
  l2rt.accounts
  INNER JOIN l2rt.characters ON accounts.login = characters.account_name
WHERE
  characters.online = 1

у меня вот такие данные:

Код:

================================================================
| login  | lastIP |account_|char_nam| online |lastIP1 |lastacti|
|        |        |  name  |   e    |        |        |   ve   |
================================================================
|  seb   |192.168.|  seb   |  DDDD  |    1   |  null  |        |
|        |  0.5   |        |        |        |        |        |
----------------------------------------------------------------
|  123   |192.168.|  123   | werewq |    1   |  null  |        |
|        |  0.1   |        |        |        |        |        |
----------------------------------------------------------------
| aaaaa  |192.168.| aaaaa  |  sdf   |    1   |192.168.|12515490|
|        | 0.105  |        |        |        | 0.105  |   07   |
----------------------------------------------------------------

а мне нужно что бы строка 3 непопадала сюда... как это сделать?

Отредактированно alexmarch (29.08.2009 15:37:23)

alexmarch · 29.08.2009 18:38:46

Все что написанно выше можно описать короче:
Вот два случая

есть две таблицы

Код:

Вариант1
        t1
192.168.0.105
192.168.0.105
192.168.0.104
192.168.0.103
192.168.0.102
192.168.0.101
        t2
192.168.0.105
Результат запроса
192.168.0.105
192.168.0.105
192.168.0.104
192.168.0.103
192.168.0.102
192.168.0.101
Вариант2
         t1
192.168.0.105
192.168.0.104
192.168.0.103
192.168.0.102
192.168.0.101
         t2
192.168.0.105
Результат запроса
192.168.0.104
192.168.0.103
192.168.0.102
192.168.0.101

Как постоить такой запрос???

alexmarch · 30.08.2009 23:54:29

Я как то некорректно задал вопрос?
Мне сможет кто нибудь здесь помочь?

vasya · 31.08.2009 00:30:23

Вопрос вы задали корректно, только я все равно не понял о чем речь.

Насколько я понимаю вы хотите выбрать из первой таблицы те записи, которых нет во второй, объединяя их по полю lastIP? В этом случае условие в вашем запросе должно иметь вид AND goodboy.lastIP IS NULL, т.е.

SELECT DISTINCT

  accounts.login, accounts.lastIP, characters.account_name, characters.char_name, characters.online, goodboy.lastIP, goodboy.lastactive

FROM

  l2rt.accounts

  INNER JOIN l2rt.characters ON accounts.login = characters.account_name

  LEFT OUTER JOIN l2rt.goodboy ON accounts.lastIP = goodboy.lastIP

WHERE

  characters.online = 1 AND goodboy.lastIP IS NULL

Но, во-первых, не понятно какой смысл перечислять в части SELECT поля относящиеся к таблице goodboy, если они заведомо будут иметь NULL значения, так как вы выбираете те строки, которые не имеют соответствия в таблице goodboy.

Во-вторых, в первом варианте второго поста в таблице t1 присутствует строка 192.168.0.105 и она же присутствует во второй таблице и в итоговой выборке. Но это противоречит логике вашего первого поста - выбирать те записи из первой таблице, которых нет во второй.

Или я вас неправильно понял?

alexmarch · 31.08.2009 07:52:52

Вася благодарю что вы откликнулись. Эта выборка за 4 дня сводит меня уже с ума.
Я попробую объяснить по другому.
Это игровая база. Таблица accounts отвечает за приLogin-нившехся игроков. Там храниться их последний IP адрес, password и его послдение посещение. Таблица characters отвечает за хранения игровых персонажей. Имя, характеристики и т.д. И когда player в игре characters.online = 1. Но так как таблица characters не имеет поля lastIP, но единственным способом узнать IP играющего это объединить characters и accounts по ключевым полям accounts.login = characters.account_name. Таким образом мы узнаем ник играющего, его IP и все его характеристики. в этой части в 100% работает и все здорово.
Есть программа анитибот. И когда игрок ее запускает то анитбот регистрирует клиента в таблице goodboy

CREATE TABLE IF NOT EXISTS goodboy (id INT (7), lastIP CHAR (15) NOT NULL DEFAULT 0, lastactive DECIMAL (20), PRIMARY KEY(id))

Поэтому любой хороший парень будет зарегистрирован в таблице goodboy.

Этим запросом который я пытаюсь сформировать мне нужно выявить все characters.account_name которых нет goodboy.
вот этот запрос:

SELECT DISTINCT

  accounts.login, accounts.lastIP, characters.account_name, characters.char_name, characters.online, goodboy.lastIP, goodboy.lastactive

FROM

  l2rt.accounts

  INNER JOIN l2rt.characters ON accounts.login = characters.account_name

  LEFT OUTER JOIN l2rt.goodboy ON accounts.lastIP = goodboy.lastIP

WHERE

  characters.online = 1 AND goodboy.lastIP IS NULL

Да, он выявляет всех IP которых нет goodboy, но мне нужно что бы в выборку попали так же те characters.char_name которые есть goodboy.lastIP но количество записей goodboy.lastIP < accounts.lastIP
Это проблема серых подсетей когда на одном IP может работать несколько компьютеров и у одного есть антибот и он его регистриует в goodboy, а на другом нет анибота и получается такая ситуация что в таблице goodboy.lastIP 192.168.0.5 - 1 записи а в таблице characters.account_name 2 записи. Вот именно таких мне нужно и поймать в запросе.

Прости что гружу вас этим но мне реально никто из окружения не может помочь в таком запросе.

vasya · 31.08.2009 10:32:36

Следующий запрос дает вам нужные lastIP из таблицы accounts:

select a.lastIP from

(select lastIP, count(*) as s1 from accounts group by lastIP) a

left join

(select lastIP, count(*) as s2 from goodboy group by lastIP) b using(lastIP)

where b.lastIP is null or s1>s2;

alexmarch · 31.08.2009 20:36:16

vasya спасибо, огромное но мне он неподходит.
мне как раз нужны characters.char_name

vasya · 31.08.2009 21:14:34

Так проблема была в том, чтобы получить нужные lastIP, а зная их в чем сложность объединить с таблицей characters и выбрать нужные поля.

SELECT characters.char_name FROM

(SELECT login, lastIP, COUNT(*) AS s1 FROM accounts GROUP BY lastIP) a

LEFT JOIN

(SELECT lastIP, COUNT(*) AS s2 FROM goodboy GROUP BY lastIP) b USING(lastIP)

INNER JOIN characters ON a.login = characters.account_name

WHERE characters.online = 1 AND (b.lastIP IS NULL OR s1>s2);

alexmarch · 01.09.2009 22:02:06

vasya огромное спасибо.... Дай вам бог здоровья

alexmarch · 02.09.2009 09:03:57

Блин... он не работает... последний запрос по своей сути такой же как и этот

SELECT DISTINCT

  accounts.login, accounts.lastIP, characters.account_name, characters.char_name, characters.online, goodboy.lastIP, goodboy.lastactive

FROM

  l2rt.accounts

  INNER JOIN l2rt.characters ON accounts.login = characters.account_name

  LEFT OUTER JOIN l2rt.goodboy ON accounts.lastIP = goodboy.lastIP

WHERE

  characters.online = 1 AND goodboy.lastIP IS NULL

у меня не попадают выбору те поля которые characters > goodboy

alexmarch · 02.09.2009 09:44:58

вот этот запрос работал верно

SELECT a.lastIP FROM

(SELECT lastIP, COUNT(*) AS s1 FROM accounts GROUP BY lastIP) a

LEFT JOIN

(SELECT lastIP, COUNT(*) AS s2 FROM goodboy GROUP BY lastIP) b USING(lastIP)

WHERE b.lastIP IS NULL OR s1>s2;

но только без ников

vasya · 02.09.2009 11:55:42

alexmarch написал:
вот этот запрос работал верно

но только без ников

Вот как раз этот запрос-то и работает неправильно. Он возвращает только одно значение из повторяющихся, а нужны, то все. Правильно будет

select lastIP from accounts where lastIP in(

SELECT a.lastIP FROM

(SELECT lastIP, COUNT(*) AS s1 FROM accounts GROUP BY lastIP) a

LEFT JOIN

(SELECT lastIP, COUNT(*) AS s2 FROM goodboy GROUP BY lastIP) b USING(lastIP)

WHERE b.lastIP IS NULL OR s1>s2);

Ну и соответственно ники:

select characters.char_name from accounts 

INNER JOIN characters ON accounts.login = characters.account_name

where characters.online = 1 AND lastIP in(

SELECT a.lastIP FROM

(SELECT lastIP, COUNT(*) AS s1 FROM accounts GROUP BY lastIP) a

LEFT JOIN

(SELECT lastIP, COUNT(*) AS s2 FROM goodboy GROUP BY lastIP) b USING(lastIP)

WHERE b.lastIP IS NULL OR s1>s2);

alexmarch · 02.09.2009 16:42:12

Вот второй запрос работает идеально так как мне был нужен. Я бы никогда не смог бы его составить.
Вы мне очень помогли огромное спасибо.

alexmarch · 02.09.2009 18:28:20

Рано я отписался... к сожалению последний запрос тоже не правильно работает.
Дело в том что мы с начало выбираем записи из accounts и goodboy а это неправельно.
Вот ситуация
в таблице
accounts IP 192.168.0.105 -10 шт.
goodboy 192.168.0.105 - 1 шт.
И если по связке accounts и characters мой хороший игрок в игре то он попадает в выборку.
А это не должно быть .т.к. он числиться в goodboy
Я понимаю что сначало надо выбрать все IP которые online = '1' и только потом их сравнивать с goodboy
как это сделать в одном запросе?

vasya · 03.09.2009 00:24:47

Если я правильно понял вас на этот раз, то вам нужно следующее:

SELECT characters.char_name FROM accounts

INNER JOIN characters ON accounts.login = characters.account_name

WHERE characters.online = 1 AND lastIP IN(

SELECT a.lastIP FROM

(SELECT lastIP, COUNT(*) AS s1 FROM accounts INNER JOIN characters ON accounts.login = characters.account_name WHERE characters.online = 1 GROUP BY lastIP) a

LEFT JOIN

(SELECT lastIP, COUNT(*) AS s2 FROM goodboy GROUP BY lastIP) b USING(lastIP)

WHERE b.lastIP IS NULL OR s1>s2);

alexmarch · 03.09.2009 06:23:28

Сделал серию тестов. Вроде все как надо.

alexmarch · 03.09.2009 10:45:42

Да работает идеально, еще раз спасибо.

Форум пользователей MySQL

#1 29.08.2009 15:26:13

Сравнение двух таблиц.

Код:

Код:

Код:

Код:

#2 29.08.2009 18:38:46

Re: Сравнение двух таблиц.

Код:

#3 30.08.2009 23:54:29

Re: Сравнение двух таблиц.

#4 31.08.2009 00:30:23

Re: Сравнение двух таблиц.

#5 31.08.2009 07:52:52

Re: Сравнение двух таблиц.

#6 31.08.2009 10:32:36

Re: Сравнение двух таблиц.

#7 31.08.2009 20:36:16

Re: Сравнение двух таблиц.

#8 31.08.2009 21:14:34

Re: Сравнение двух таблиц.

#9 01.09.2009 22:02:06

Re: Сравнение двух таблиц.

#10 02.09.2009 09:03:57

Re: Сравнение двух таблиц.

#11 02.09.2009 09:44:58

Re: Сравнение двух таблиц.

#12 02.09.2009 11:55:42

Re: Сравнение двух таблиц.

alexmarch написал:

#13 02.09.2009 16:42:12

Re: Сравнение двух таблиц.

#14 02.09.2009 18:28:20

Re: Сравнение двух таблиц.

#15 03.09.2009 00:24:47

Re: Сравнение двух таблиц.

#16 03.09.2009 06:23:28

Re: Сравнение двух таблиц.

#17 03.09.2009 10:45:42

Re: Сравнение двух таблиц.

Board footer