| Autor | Zpráva | ||
|---|---|---|---|
| kohl Profil |
#1 · Zasláno: 9. 2. 2013, 18:08:13
Dobrý den,
napsal jsem dotaz, ale vyhazuje chybu "Unknown column 'num_l' in 'where clause'" a jiné řešení mě nenapadá. Potřeboval bych nasměrovat. select edition_title, (select count(lend_id) from lend inner join identifier on identifier_id=lend_identifier where identifier_edition=edition_id)as num_l, (select count(lend_history_id) from lend_history inner join identifier on identifier_id=lend_history_identifier where identifier_edition=edition_id)as num_lh from edition inner join author on author.author_id=edition.edition_author where (num_l+num_lh)>0 order by (num_l+num_lh) desc,edition_title limit 7 Děkuji za odpověď. |
||
| Joker Profil |
#2 · Zasláno: 9. 2. 2013, 20:12:54
kohl:
Znamená to, že v SQL dotazu je použitý sloupec num_l, který v tabulce neexistuje. |
||
| Tori Profil |
#3 · Zasláno: 9. 2. 2013, 21:53:07
kohl:
Místo where by muselo asi být having, když odkazujete na výsledek agregační funkce. Zkoušel jste to zapsat jako joiny, jestli to je nějak výrazně horší? select edition_title, count(lend_id) + count(lend_history_id) cnt
from edition
inner join author on author.author_id=edition.edition_author
left join
(lend inner join identifier i1 on i1.identifier_id=lend_identifier)
on i1.identifier_edition=edition_id
left join
(lend_history inner join identifier i2 on i2.identifier_id=lend_history_identifier)
on i2.identifier_edition=edition_id
group by edition_title
having cnt > 0
order by cnt desc, edition_title
limit 7 |
||
| kohl Profil |
#4 · Zasláno: 11. 2. 2013, 08:26:59
Děkuji za pomoc, ale na konec jsem to vyřešil tahle, je to asi 3x rychlejší.
SELECT edition_id, edition_title, year_all, author_all
FROM (
SELECT edition_id, edition_title, CONCAT( '(', edition_year, ')' ) AS year_all, CONCAT_WS( ', ', CONCAT_WS( ' ', author_surname, author_name ) , author_year ) AS author_all, (
(
SELECT COUNT( lend_id )
FROM lend
INNER JOIN identifier ON identifier_id = lend_identifier
WHERE identifier_edition = edition_id
) + (
SELECT COUNT( lend_history_id )
FROM lend_history
INNER JOIN identifier ON identifier_id = lend_history_identifier
WHERE identifier_edition = edition_id )
) AS num
FROM edition
INNER JOIN author ON author.author_id = edition.edition_author
ORDER BY num DESC , edition_title
LIMIT 7
) AS t1
WHERE num >0 |
||
|
Časová prodleva: 11 let
|
|||
0