Обсуждение: Subquery with multiple rows
<div class="WordSection1"><p class="MsoNormal"><span lang="EN-US">Hi all!</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">How can I outsource a subquery?</span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">An Example:</span><p class="MsoNormal"><spanlang="EN-US">SELECT DISTINCT a.*,</span><p class="MsoNormal"><span lang="EN-US">( SELECT SUM(std)</span><p class="MsoNormal"><span lang="EN-US"> FROM all_std </span><p class="MsoNormal"><span lang="EN-US"> WHERE(a.status <=5 AND status = 5) </span><p class="MsoNormal"><span lang="EN-US"> AND foerd_idIN (SELECT foerd_id FROM foerds WHERE mass_id = '55896') </span><p class="MsoNormal"><span lang="EN-US">) AS done_std</span><pclass="MsoNormal"><span lang="EN-US">( SELECT SUM(anerk_std) </span><p class="MsoNormal"><span lang="EN-US"> FROMall_std</span><p class="MsoNormal"><span lang="EN-US"> WHERE (a.status >5 AND status <5)</span><p class="MsoNormal"><span lang="EN-US"> AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id ='55896')</span><p class="MsoNormal"><span lang="EN-US">) AS accepted_std</span><p class="MsoNormal"><span lang="EN-US">FROMtable_a a </span><p class="MsoNormal"><span lang="EN-US">WHERE a.mass_id = '55896';</span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">The sub-subquery is “SELECT foerd_idFROM foerds WHERE mass_id = '55896')” an delivers more than one row.</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">Now I want to run the subquery only one time. </span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">I tried this: </span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">SELECT DISTINCT a.*,</span><p class="MsoNormal"><spanlang="EN-US">( SELECT SUM(std) </span><p class="MsoNormal"><span lang="EN-US"> FROM all_std</span><p class="MsoNormal"><span lang="EN-US"> WHERE (a.status <=5 AND status = 5) </span><p class="MsoNormal"><spanlang="EN-US"> AND foerd_id IN (f.foerd_id) </span><p class="MsoNormal"><span lang="EN-US">)AS done_std,</span><p class="MsoNormal"><span lang="EN-US">( SELECT SUM(anerk_std) </span><p class="MsoNormal"><spanlang="EN-US"> FROM all_std</span><p class="MsoNormal"><span lang="EN-US"> WHERE (a.status>5 AND status < 5)</span><p class="MsoNormal"><span lang="EN-US"> AND foerd_id IN (f.foerd_id)</span><pclass="MsoNormal"><span lang="EN-US">) AS accepted_std</span><p class="MsoNormal"><span lang="EN-US">FROMtable_a a,</span><p class="MsoNormal"><span lang="EN-US">(SELECT foerd_id FROM foerds WHERE mass_id = '55896') f</span><p class="MsoNormal"><span lang="EN-US">WHERE a.mass_id = '55896';</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">But the result is not correct because I got one row for everyrow of the of “f.foerd_id”.</span><p class="MsoNormal"><span lang="EN-US">Allowed is only one row.</span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">How must the SQL looks like to get theright result?</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal">Regards…<pclass="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE"> </span><pclass="MsoNormal"><span style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">Mitfreundlichen Grüßen</span><p class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">J. Weiss</span><pclass="MsoNormal"><span style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE"> </span><pclass="MsoNormal"><span style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">EntwicklerSachgebiet GEW / e-Lösungen</span><pclass="MsoNormal"><span style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">E-Mail:<a href="mailto:j.weiss@dvz-mv.de"><spanstyle="color:blue">j.weiss@dvz-mv.de</span></a></span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">Telefon:+49 (3 85) 48 00 351</span><pclass="MsoNormal" style="margin-bottom:12.0pt"><span style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">Telefax:+49 (3 85) 48 00 98 351<br />Internet: <a href="http://www.dvz-mv.de/"><span style="color:blue">www.dvz-mv.de</span></a><br /><br /></span><span style="font-size:7.5pt;font-family:"Arial",sans-serif;color:#1F497D;mso-fareast-language:DE">_____________________________________</span><span style="color:#1F497D;mso-fareast-language:DE"><br/></span><span style="font-size:7.5pt;font-family:"Arial",sans-serif;color:#1F497D;mso-fareast-language:DE">DVZ Datenverarbeitungszentrum<br/> Mecklenburg-Vorpommern GmbH<br /> Lübecker Str. 283 - 19059 Schwerin</span><span style="color:#1F497D;mso-fareast-language:DE"><br/></span><span style="font-size:7.5pt;font-family:"Arial",sans-serif;color:#1F497D;mso-fareast-language:DE">Sitzder Gesellschaft: Schwerin| Eintrag im Handelsregister: HRB 187 / Amtsgericht Schwerin<br /> Geschäftsführer: Hubert Ludwig | Aufsichtsratsvorsitzender:Staatssekretär Peter Bäumer</span><span style="color:#1F497D;mso-fareast-language:DE"><br /></span><span style="font-size:7.5pt;font-family:"Arial",sans-serif;color:#1F497D;mso-fareast-language:DE">_____________________________________</span><span style="color:#1F497D;mso-fareast-language:DE"></span><pclass="MsoNormal"> </div>
Hi Jörg, > How can I outsource a subquery? maybe you could try to use "Common Table Expressions" and do something like: WITH foerd_id AS (SELECT foerd_id FROM foerds WHERE mass_id = '55896') SELECT DISTINCT a.*, ( SELECT SUM(std) FROM all_std WHERE (a.status <=5 AND status = 5) AND foerd_id IN (SELECT * FROMfoerd_id) ) AS done_std, ( SELECT SUM(anerk_std) FROM all_std WHERE (a.status >5 AND status < 5) AND foerd_id IN (SELECT* FROM foerd_id) ) AS accepted_std FROM table_a a WHERE a.mass_id = '55896’; or with some more “outsourcing”: WITH all_std_foerds AS (SELECT * FROM all_std WHERE foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id= '55896') AND status <= 5) SELECT DISTINCT a.*, ( SELECT SUM(std) FROM all_std_foerds WHERE (a.status <=5 AND status = 5) ) AS done_std, ( SELECT SUM(anerk_std) FROM all_std_foerds WHERE (a.status >5 AND status < 5) ) AS accepted_std FROM table_a a WHERE a.mass_id = '55896'; Or maybe unnesting both aggregate subqueries in order to merge them and make use of FILTER-clauses (https://www.postgresql.org/docs/9.5/static/sql-expressions.html#SYNTAX-AGGREGATES)might be a nice option. Something like: SELECT DISTINCT a.*, SUM(std) FILTER (WHERE a.status <=5 AND s.status = 5) AS done_std, SUM(anerk_std)FILTER (WHERE a.status >5 AND s.status < 5) AS accepted_std FROM table_a a, all_std s WHERE a.mass_id = '55896' AND s.status<=5 AND s.foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id = '55896') GROUP BY a.mass_id, a.status; Regards, Benjamin
Hi Benjamin! Thank You! All Versions are working. I Think i will use the Version with FILTER clause. The bad thing is, all versions are not much faster than my old version. Regards, Jörg -----Ursprüngliche Nachricht----- Von: Benjamin Dietrich [mailto:b.dietrich@uni-tuebingen.de] Gesendet: Dienstag, 14. Juni 2016 11:39 An: Weiss, Jörg <J.Weiss@dvz-mv.de> Cc: pgsql-sql@postgresql.org Betreff: Re: [SQL] Subquery with multiple rows Hi Jörg, > How can I outsource a subquery? maybe you could try to use "Common Table Expressions" and do something like: WITH foerd_id AS (SELECT foerd_id FROM foerds WHERE mass_id = '55896') SELECT DISTINCT a.*, ( SELECT SUM(std) FROM all_std WHERE (a.status <=5 AND status = 5) AND foerd_id IN (SELECT * FROMfoerd_id) ) AS done_std, ( SELECT SUM(anerk_std) FROM all_std WHERE (a.status >5 AND status < 5) AND foerd_id IN (SELECT* FROM foerd_id) ) AS accepted_std FROM table_a a WHERE a.mass_id = '55896’; or with some more “outsourcing”: WITH all_std_foerds AS (SELECT * FROM all_std WHERE foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id= '55896') AND status <= 5) SELECT DISTINCT a.*, ( SELECT SUM(std) FROM all_std_foerds WHERE (a.status <=5 AND status = 5) ) AS done_std, ( SELECT SUM(anerk_std) FROM all_std_foerds WHERE (a.status >5 AND status < 5) ) AS accepted_std FROM table_a a WHERE a.mass_id = '55896'; Or maybe unnesting both aggregate subqueries in order to merge them and make use of FILTER-clauses (https://www.postgresql.org/docs/9.5/static/sql-expressions.html#SYNTAX-AGGREGATES)might be a nice option. Something like: SELECT DISTINCT a.*, SUM(std) FILTER (WHERE a.status <=5 AND s.status = 5) AS done_std, SUM(anerk_std)FILTER (WHERE a.status >5 AND s.status < 5) AS accepted_std FROM table_a a, all_std s WHERE a.mass_id = '55896'AND s.status<=5 AND s.foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id = '55896') GROUP BY a.mass_id, a.status; Regards, Benjamin
Hi all!
How can I outsource a subquery?
An Example:
SELECT DISTINCT a.*,
( SELECT SUM(std)
FROM all_std
WHERE (a.status <=5 AND status = 5)
AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id = '55896')
) AS done_std
( SELECT SUM(anerk_std)
FROM all_std
WHERE (a.status >5 AND status < 5)
AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id = '55896')
) AS accepted_std
FROM table_a a
WHERE a.mass_id = '55896';
The sub-subquery is “SELECT foerd_id FROM foerds WHERE mass_id = '55896')” an delivers more than one row.
Now I want to run the subquery only one time.
I tried this:
SELECT DISTINCT a.*,
( SELECT SUM(std)
FROM all_std
WHERE (a.status <=5 AND status = 5)
AND foerd_id IN (f.foerd_id)
) AS done_std,
( SELECT SUM(anerk_std)
FROM all_std
WHERE (a.status >5 AND status < 5)
AND foerd_id IN (f.foerd_id)
) AS accepted_std
FROM table_a a,
(SELECT foerd_id FROM foerds WHERE mass_id = '55896') f
WHERE a.mass_id = '55896';
But the result is not correct because I got one row for every row of the of “f.foerd_id”.
Allowed is only one row.
How must the SQL looks like to get the right result?
Von: David G. Johnston [mailto:david.g.johnston@gmail.com]
Gesendet: Dienstag, 14. Juni 2016 15:04
An: Weiss, Jörg <J.Weiss@dvz-mv.de>
Cc: pgsql-sql@postgresql.org
Betreff: Re: [SQL] Subquery with multiple rows
On Tue, Jun 14, 2016 at 2:22 AM, Weiss, Jörg <J.Weiss@dvz-mv.de> wrote:
Hi all!
How can I outsource a subquery?
An Example:
SELECT DISTINCT a.*,
Lose the DISTINCT. DISTINCT is a code smell. In this case it is also pointless since a.* had better already be unique and its the only table in the query..
And, please don't top-post.
( SELECT SUM(std)
FROM all_std
WHERE (a.status <=5 AND status = 5)
AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id = '55896')
) AS done_std
( SELECT SUM(anerk_std)
FROM all_std
WHERE (a.status >5 AND status < 5)
AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id = '55896')
) AS accepted_std
FROM table_a a
WHERE a.mass_id = '55896';
The sub-subquery is “SELECT foerd_id FROM foerds WHERE mass_id = '55896')” an delivers more than one row.
Now I want to run the subquery only one time.
I tried this:
SELECT DISTINCT a.*,
( SELECT SUM(std)
FROM all_std
WHERE (a.status <=5 AND status = 5)
AND foerd_id IN (f.foerd_id)
) AS done_std,
( SELECT SUM(anerk_std)
FROM all_std
WHERE (a.status >5 AND status < 5)
AND foerd_id IN (f.foerd_id)
) AS accepted_std
FROM table_a a,
(SELECT foerd_id FROM foerds WHERE mass_id = '55896') f
WHERE a.mass_id = '55896';
But the result is not correct because I got one row for every row of the of “f.foerd_id”.
Allowed is only one row.
How must the SQL looks like to get the right result?
The following should work on recent versions though you will need to play with the syntax.
SELECT a.*, my_sums.*
FROM table_a a
LATERAL JOIN (SELECT SUM(anerk_std) FILTER (...) AS accepted_std, SUM(std) FILTER (...) AS done_std FROM all_std WHERE all_std.mass_id = a.mass_id)
AS my_sums
WHERE a.mass_id = $1
David J.
OK,
works fine! Thank You
Jörg
Hi,
Why don't you join table_a and all_std and leave " WHERE (a.status <=5 AND status = 5 AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id = '55896') " do fix until got the result you wanted. Sorry not showing you in detail, just to give idea if it is logic for your solution.
Thanks
Hi all!
How can I outsource a subquery?
An Example:
SELECT DISTINCT a.*,
( SELECT SUM(std)
FROM all_std
WHERE (a.status <=5 AND status = 5)
AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id = '55896')
) AS done_std
( SELECT SUM(anerk_std)
FROM all_std
WHERE (a.status >5 AND status < 5)
AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id = '55896')
) AS accepted_std
FROM table_a a
WHERE a.mass_id = '55896';
The sub-subquery is “SELECT foerd_id FROM foerds WHERE mass_id = '55896')” an delivers more than one row.
Now I want to run the subquery only one time.
I tried this:
SELECT DISTINCT a.*,
( SELECT SUM(std)
FROM all_std
WHERE (a.status <=5 AND status = 5)
AND foerd_id IN (f.foerd_id)
) AS done_std,
( SELECT SUM(anerk_std)
FROM all_std
WHERE (a.status >5 AND status < 5)
AND foerd_id IN (f.foerd_id)
) AS accepted_std
FROM table_a a,
(SELECT foerd_id FROM foerds WHERE mass_id = '55896') f
WHERE a.mass_id = '55896';
But the result is not correct because I got one row for every row of the of “f.foerd_id”.
Allowed is only one row.
How must the SQL looks like to get the right result?
Regards…
Mit freundlichen Grüßen
J. Weiss
Entwickler Sachgebiet GEW / e-Lösungen
E-Mail: j.weiss@dvz-mv.de
Telefon: +49 (3 85) 48 00 351
Telefax: +49 (3 85) 48 00 98 351
Internet: www.dvz-mv.de
_____________________________________
DVZ Datenverarbeitungszentrum
Mecklenburg-Vorpommern GmbH
Lübecker Str. 283 - 19059 Schwerin
Sitz der Gesellschaft: Schwerin | Eintrag im Handelsregister: HRB 187 / Amtsgericht Schwerin
Geschäftsführer: Hubert Ludwig | Aufsichtsratsvorsitzender: Staatssekretär Peter Bäumer
_____________________________________