Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / use parent field value in a subquery / 15 сообщений из 15, страница 1 из 1
09.04.2014, 13:06:02
    #38609752
maxtorchel
maxtorchel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
use parent field value in a subquery
Есть такой запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
SELECT b.id,bmcount FROM bill_count bc 
LEFT JOIN contractor c ON c.id=bc.filial_id 
LEFT JOIN bill b ON b.id=bc.bill_id 
LEFT JOIN 
	(
	SELECT b.id,SUM((IFNULL(insum,0)-IFNULL(outsum,0))*score_weight/bills_weight) bmcount FROM bill b 
	LEFT JOIN rel_bill_manifest r ON r.bill_id=b.id 
	LEFT JOIN manifest m ON m.id=r.manifest_id 
	LEFT JOIN (
		SELECT SUM(sc.count) insum,sc.model_id m1 FROM score_count sc WHERE sc.supplier_id=46 AND sc.model_name='Manifest' GROUP BY sc.model_id
		) incount ON m1=m.id 
	LEFT JOIN (
		SELECT SUM(sc.count) outsum,sc.model_id m2 FROM score_count sc WHERE sc.payer_id=46 AND sc.model_name='Manifest' GROUP BY sc.model_id
		) outcount ON m2=m.id 
	GROUP BY b.id
	) t ON b.id=t.id
WHERE c.id IN (46,514) 
AND b.id IN (SELECT DISTINCT(r2.bill_id) FROM bill b 
	LEFT JOIN rel_bill_manifest r1 ON r1.bill_id=b.id 
	LEFT JOIN rel_bill_manifest r2 ON r2.manifest_id=r1.manifest_id 
	WHERE b.id=5)



Как передать c.id в подзапросы где sc.supplier_id=46 (надо sc.supplier_id=c.id) и sc.payer_id=46 (надо sc.payer_id=c.id). Сейчас запрос работает но выдает верные значения только для контрагента 46, надо чтоб выдавал и для 514 соответственно. Уже голову всю сломал. Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.04.2014, 13:30:14
    #38609812
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
use parent field value in a subquery
maxtorchel,

передать - никак. Ибо вложенный запрос (derived table) не может быть коррелированным, а apply-подзапросов в MySQL пока нет.
поэтому единственный вариант -полностью переписать запрос, пересмотрев логику его работы
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.04.2014, 13:49:52
    #38609872
maxtorchel
maxtorchel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
use parent field value in a subquery
похоже вот так заработало:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
SELECT b.id,bmcount FROM bill_count bc 
LEFT JOIN contractor c ON c.id=bc.filial_id 
LEFT JOIN bill b ON b.id=bc.bill_id 
LEFT JOIN 
	(
	SELECT b.id bid,c.id cid,SUM((IFNULL(insum,0)-IFNULL(outsum,0))*score_weight/bills_weight) bmcount FROM bill b 
	LEFT JOIN bill_count bc ON bc.bill_id=b.id
	LEFT JOIN contractor c ON c.id=bc.filial_id 
	LEFT JOIN rel_bill_manifest r ON r.bill_id=b.id 
	LEFT JOIN manifest m ON m.id=r.manifest_id 
	LEFT JOIN (
		SELECT SUM(sc.count) insum,sc.model_id m1,sc.supplier_id s FROM score_count sc WHERE sc.model_name='Manifest' GROUP BY sc.model_id
		) incount ON m1=m.id AND s=c.id
	LEFT JOIN (
		SELECT SUM(sc.count) outsum,sc.model_id m2,sc.payer_id p FROM score_count sc WHERE sc.model_name='Manifest' GROUP BY sc.model_id
		) outcount ON m2=m.id AND p=c.id
	GROUP BY b.id
	) t ON b.id=t.bid AND c.id=t.cid
WHERE c.id IN (SELECT id FROM contractor WHERE type_id='filial') 
AND b.id IN (SELECT DISTINCT(r2.bill_id) FROM bill b 
	LEFT JOIN rel_bill_manifest r1 ON r1.bill_id=b.id 
	LEFT JOIN rel_bill_manifest r2 ON r2.manifest_id=r1.manifest_id 
	WHERE b.id=5)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.04.2014, 12:52:46
    #38612229
maxtorchel
maxtorchel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
use parent field value in a subquery
Появилась проблема с данным запросом, я его вставил в триггер AFTER UPDATE и теперь мне постоянно выдает ошибку:
Код: sql
1.
Deadlock found when trying to get lock



Как от нее избавиться, начитался про дедлоки но всеравно не догоняю как найти что имеенно где блокируется.

Запрос немного изменил но не думаю что это принципиально:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
UPDATE bill_count bc 
LEFT JOIN contractor c ON c.id=bc.filial_id 
LEFT JOIN bill b ON b.id=bc.bill_id 
LEFT JOIN 
	(
	SELECT b.id bid,c.id cid,SUM((IFNULL(insum,0)-IFNULL(outsum,0))*IFNULL(score_weight,0)/IF(bills_weight IS NULL OR bills_weight=0,1,bills_weight)) bmcount FROM bill b 
	LEFT JOIN bill_count bc ON bc.bill_id=b.id
	LEFT JOIN contractor c ON c.id=bc.filial_id 
	LEFT JOIN rel_bill_manifest r ON r.bill_id=b.id 
	LEFT JOIN manifest m ON m.id=r.manifest_id 
	LEFT JOIN (
		SELECT SUM(sc.count) insum,sc.model_id m1,sc.supplier_id s FROM score_count sc WHERE sc.model_name='Manifest' AND sc.service_id NOT IN ('cash_on_delivery','payment_on_delivery') GROUP BY m1,s
		) incount ON m1=m.id AND s=c.id
	LEFT JOIN (
		SELECT SUM(sc.count) outsum,sc.model_id m2,sc.payer_id p FROM score_count sc WHERE sc.model_name='Manifest' AND sc.service_id NOT IN ('cash_on_delivery','payment_on_delivery') GROUP BY m2,p
		) outcount ON m2=m.id AND p=c.id
	GROUP BY bid,cid
	) t ON b.id=t.bid AND c.id=t.cid
SET bc.manifest_count = bmcount 
WHERE c.id IN (SELECT id FROM contractor WHERE type_id='filial') 
AND b.id IN (SELECT DISTINCT(r1.bill_id) FROM rel_bill_manifest r 
	LEFT JOIN rel_bill_manifest r1 ON r1.manifest_id=r.manifest_id 
	WHERE r.bill_id=NEW.id);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.04.2014, 13:06:49
    #38612244
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
use parent field value in a subquery
передать - никак. Ибо вложенный запрос (derived table) не может быть коррелированным, а apply-подзапросов в MySQL пока нет.

Впервые такое слышу.



Но можно сделать во FROM некорелированный запрос с группировкой по sc.supplier_id (возможно и с фильтром по нужным sc.supplier_id), а пото JON-ить его с остальным запросом по sc.supplier_id.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.04.2014, 14:13:25
    #38612368
maxtorchel
maxtorchel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
use parent field value in a subquery
БД кcтати innodb, и еще вопрос как ускорить данный запрос?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.04.2014, 14:16:44
    #38612379
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
use parent field value in a subquery
maxtorchelкак ускорить данный запрос?Избавьтесь от конструкций IN (SELECT ... )
И пересмотрите необходимость LEFT JOIN-ов. Некоторые из них явно можно заменить на просто JOIN.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.04.2014, 14:58:27
    #38612442
maxtorchel
maxtorchel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
use parent field value in a subquery
хм, заменил in на exists и почти все leftы убрал и запрос стал выполняться даже чуть медленне((
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.04.2014, 15:05:21
    #38612449
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
use parent field value in a subquery
maxtorchel,

показывайте.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.04.2014, 15:06:58
    #38612455
maxtorchel
maxtorchel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
use parent field value in a subquery
вот так сделал:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
UPDATE bill_count bc 
JOIN contractor c ON c.id=bc.filial_id 
JOIN bill b ON b.id=bc.bill_id 
JOIN 
	(
	SELECT b.id bid,c.id cid,SUM((IFNULL(insum,0)-IFNULL(outsum,0))*IFNULL(score_weight,0)/IF(bills_weight IS NULL OR bills_weight=0,1,bills_weight)) bmcount FROM bill b 
	LEFT JOIN bill_count bc ON bc.bill_id=b.id
	JOIN contractor c ON c.id=bc.filial_id 
	LEFT JOIN rel_bill_manifest r ON r.bill_id=b.id 
	JOIN manifest m ON m.id=r.manifest_id 
	LEFT JOIN (
		SELECT SUM(sc.count) insum,sc.model_id m1,sc.supplier_id s FROM score_count sc WHERE sc.model_name='Manifest' AND sc.service_id NOT IN ('cash_on_delivery','payment_on_delivery') GROUP BY m1,s
		) incount ON m1=m.id AND s=c.id
	LEFT JOIN (
		SELECT SUM(sc.count) outsum,sc.model_id m2,sc.payer_id p FROM score_count sc WHERE sc.model_name='Manifest' AND sc.service_id NOT IN ('cash_on_delivery','payment_on_delivery') GROUP BY m2,p
		) outcount ON m2=m.id AND p=c.id
	GROUP BY bid,cid
	) t ON b.id=t.bid AND c.id=t.cid
SET bc.manifest_count = bmcount 
WHERE EXISTS (SELECT 1 FROM contractor cont WHERE cont.type_id='filial' AND c.id=cont.id) 
AND EXISTS (SELECT 1 FROM rel_bill_manifest rel 
	LEFT JOIN rel_bill_manifest rel1 ON rel1.manifest_id=rel.manifest_id 
	WHERE rel.bill_id=NEW.id AND b.id=rel1.bill_id);



Медленнее наверно я погорячился, скорее также, разница в рамках погрешности.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.04.2014, 15:11:16
    #38612461
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
use parent field value in a subquery
maxtorchel,

что такое NEW.id и почему остался самый последний LEFT JOIN (это что первое в глаза бросается)?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.04.2014, 17:09:25
    #38612635
maxtorchel
maxtorchel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
use parent field value in a subquery
NEW.id это id записи в триггере после обновления, а таблица rel_bill_manifest служит для связи bill и manifest, связь many to many и мне нужно выбрать все bill которые есть у manifest-ов которые есть у данной bill. например, у данной bill есть 2 manifest, мне нужны все bill, которые есть у данных manifest-ов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.04.2014, 17:17:08
    #38612649
maxtorchel
maxtorchel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
use parent field value in a subquery
вобщем вроде как последний left тоже же нужен)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.04.2014, 17:48:01
    #38612707
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
use parent field value in a subquery
maxtorchelвобщем вроде как последний left тоже же нужен)Ну как же?
Условие b.id=rel1.bill_id исключает возможность того, чтобы в выборку попали записи из rel, которых нет в rel1. Т.е. LEFT теряет весь смысл.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.04.2014, 13:56:12
    #38615299
maxtorchel
maxtorchel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
use parent field value in a subquery
в итоге вот такой запрос получился:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SELECT b.id bid,c.id cid,SUM((IFNULL(insum,0)-IFNULL(outsum,0))*score_weight/bills_weight) bmcount FROM bill b 
	JOIN bill_count bc ON bc.bill_id=b.id
	JOIN contractor c ON c.id=bc.filial_id 
	JOIN rel_bill_manifest r ON r.bill_id=b.id 
	JOIN manifest m ON m.id=r.manifest_id 
	LEFT JOIN (
		SELECT SUM(sc.count) insum,sc.model_id m1,sc.supplier_id s FROM score_count sc WHERE sc.model_name='Manifest' AND sc.service_id NOT IN ('cash_on_delivery','payment_on_delivery') GROUP BY m1,s
		) incount ON m1=m.id AND s=c.id
	LEFT JOIN (
		SELECT SUM(sc.count) outsum,sc.model_id m2,sc.payer_id p FROM score_count sc WHERE sc.model_name='Manifest' AND sc.service_id NOT IN ('cash_on_delivery','payment_on_delivery') GROUP BY m2,p
		) outcount ON m2=m.id AND p=c.id 
WHERE c.type_id='filial' 
AND EXISTS (SELECT 1 FROM rel_bill_manifest rel 
	JOIN rel_bill_manifest rel1 ON rel1.manifest_id=rel.manifest_id 
	WHERE rel.bill_id=5 AND b.id=rel1.bill_id) GROUP BY bid,cid


Думаю, что лучше уже не получится.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / use parent field value in a subquery / 15 сообщений из 15, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 424ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1834980]:
 0ms
get settings:
 8ms
get forum list:
 10ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 97ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 43ms
get tp. blocked users:
 1ms
others: 251ms
total:  424ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]