Age: 150 Joined: 04 Oct 2007 Posts: 1218 Location: Санкт-Петербург
Posted: Tue Sep 14, 2010 11:49 pm Post subject: SQL
Коллеги, какие еще варианты select возможны? Пример для MySQL, но думаю всем понятно о чем речь
Для примера взяты только два типа признаков (школа и институт) и их характеристики, но в реалии их может быть много. А на количество Inner Join как известно есть ограничение. Задача - написать селект, ограничивающий записи по нескольким признакам.
Code:
CREATE TABLE test (
id INT NOT NULL AUTO_INCREMENT,
User VARCHAR(20) NOT NULL,
Type VARCHAR(20) NOT NULL,
Value VARCHAR(30) NULL,
PRIMARY KEY (id)
);
CREATE INDEX test_User_Index ON test
(
User
);
CREATE INDEX test_Type_Index ON test
(
Type
);
CREATE INDEX test_Value_Index ON test
(
Value
);
show index from test;
INSERT INTO test ( User, Type, Value ) VALUES ( 'Ivanov', 'School', '11' );
INSERT INTO test ( User, Type, Value ) VALUES ( 'Ivanov', 'Institute', 'MGU' );
INSERT INTO test ( User, Type, Value ) VALUES ( 'Petrov', 'School', '12' );
INSERT INTO test ( User, Type, Value ) VALUES ( 'Petrov', 'Institute', 'LKI' );
INSERT INTO test ( User, Type, Value ) VALUES ( 'Sidorov', 'School', '13' );
INSERT INTO test ( User, Type, Value ) VALUES ( 'Sidorov', 'Institute', 'LETI' );
select * from test AS t1
INNER JOIN test AS t2 ON t2.User = t1.User
where t1.User = 'Ivanov' and (t1.Type = 'School' and t1.Value = '11') and (t2.Type = 'Institute' and t2.Value = 'MGU');
_________________ Молитва - это запрос разработчику на изменение кода программы.
Age: 150 Joined: 04 Oct 2007 Posts: 1218 Location: Санкт-Петербург
Posted: Wed Sep 15, 2010 10:41 am Post subject:
Во, для теста добавил. Немцы в результате не должны появиться при фильтре по Школа = 11 И Институт = МГУ.
Code:
INSERT INTO test ( User, Type, Value ) VALUES ( 'Borman', 'School', '11' );
INSERT INTO test ( User, Type, Value ) VALUES ( 'Borman', 'Institute', 'LKI' );
INSERT INTO test ( User, Type, Value ) VALUES ( 'Muller', 'School', '12' );
INSERT INTO test ( User, Type, Value ) VALUES ( 'Muller', 'Institute', 'MGU' );
INSERT INTO test ( User, Type, Value ) VALUES ( 'Shtirlitz', 'School', '11' );
INSERT INTO test ( User, Type, Value ) VALUES ( 'Shtirlitz', 'Institute', 'MGU' );
select * from test AS t1
INNER JOIN test AS t2 ON t2.User = t1.User
where (t1.Type = 'School' and t1.Value = '11') and (t2.Type = 'Institute' and t2.Value = 'MGU');
_________________ Молитва - это запрос разработчику на изменение кода программы.
Last edited by vga on Wed Sep 15, 2010 11:11 am; edited 1 time in total
select * from test as t1
where type = 'School' and value = '11'
and exists ( select * from test as t2
where user = t1.user and type = 'Institute' and value = 'MGU' )
Тут правда можно натолкнуться на ограничение на количество вложенных запросов, если оно есть
Age: 150 Joined: 04 Oct 2007 Posts: 1218 Location: Санкт-Петербург
Posted: Wed Sep 15, 2010 11:10 am Post subject:
Armann wrote:
Тогда так:
Code:
select * from test as t1
where type = 'School' and value = '11'
and exists ( select * from test as t2
where user = t1.user and type = 'Institute' and value = 'MGU' )
Тут правда можно натолкнуться на ограничение на количество вложенных запросов, если оно есть
Ага, этот вариант рабочий!
На Inner Join тоже есть ограничение, так что может еще кто "безограничительные" варианты предложит _________________ Молитва - это запрос разработчику на изменение кода программы.
# Создание временной таблицы
create temporary table tbl_tmp ...
# Заполнение временной таблицы по одному из условий
insert into tbl_tmp
select * from test as t1 where type = 'School' and value = '11'
# Удаление лишних данных
delete from tbl_tmp
where not exist ( select * from test as t2
where user = t1.user and type = 'Institute' and value = 'MGU' )
# Повторять DELETE с нужными условиями до удовлетворения
Здесь потенциальное ограничение - размер временной таблицы. На время выполнения внимания не обращаем
Age: 150 Joined: 04 Oct 2007 Posts: 1218 Location: Санкт-Петербург
Posted: Wed Sep 15, 2010 4:14 pm Post subject:
Ну и еще один вариант, где 2 - количество показателей, по которым должно быть совпадение.
Code:
SELECT * FROM test WHERE ( Type = 'School' and Value = '11') or ( Type = 'Institute' and Value = 'MGU')
GROUP BY User HAVING COUNT(*)=2
Правда план выполнения запроса в этом случае хреновый
Quote:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE test ALL test_Type_Index,test_Value_Index 11 Using where; Using temporary; Using filesort
Using temporary; Using filesort - это то, из-за чего в первую очередь тормозят запросы.
Для первого запроса:
Code:
explain select * from test AS t1
INNER JOIN test AS t2 ON t2.User = t1.User
where t1.User = 'Ivanov' and (t1.Type = 'School' and t1.Value = '11') and (t2.Type = 'Institute' and t2.Value = 'MGU');
Quote:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref test_User_Index,test_Type_Index,test_Value_Index test_User_Index 22 const 2 Using where
1 SIMPLE t2 ref test_User_Index,test_Type_Index,test_Value_Index test_User_Index 22 const 2 Using where
Для второго:
Code:
explain select * from test as t1
where type = 'School' and value = '11'
and exists ( select * from test as t2
where user = t1.user and type = 'Institute' and value = 'MGU' )
Quote:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ref test_Type_Index,test_Value_Index test_Value_Index 33 const 3 Using where
2 DEPENDENT SUBQUERY t2 ref test_User_Index,test_Type_Index,test_Value_Index test_User_Index 22 wwwsapnetru.t1.User 2 Using where
_________________ Молитва - это запрос разработчику на изменение кода программы.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
, счас ниже добавлю.