Jak to resit pomoci SQL ?

[Karel Zak]

On Tue, Mar 11, 2003 at 09:39:58AM +0100, Frantisek Darena wrote:
> 
> Tohle by melo fungovat, zkousel jsem na oracle (ta imaginarni tabulka se
> jmenuje iin, protoze in je povazovany za ten operator).
> F. Darena
> 
> SELECT DISTINCT substr(tabulka.rule, 0, length(tabulka.rule)-4) as rule,
> 	iin.data AS data_in,
> 	out.data AS data_out
> 
> FROM (SELECT substr(rule, 0, length(tabulka.rule)-4) as rule, data
> 	FROM tabulka
> 	WHERE rule LIKE '%-->'
>      ) iin,
>      (SELECT substr(rule, 0, length(tabulka.rule)-4) as rule, data
> 	FROM tabulka
> 	WHERE rule LIKE '%<--'
>      ) out,
>      tabulka
> WHERE substr(tabulka.rule, 0, length(tabulka.rule)-4) = iin.rule
>      AND substr(tabulka.rule, 0, length(tabulka.rule)-4) = out.rule
> 

 Zajimave zadani, jen par poznamek. Pouzivani tech "rule" v podobe
 "<--" nebo "-->" primo uvnitr atributu, ktery se pouziva samostatne je z
 databazovaho pohledu pekna prasarnicka (neodpovida to ani 1. normalni
 forme:-). To by melo byt vyreseno uz pri vkladani tech dat.

 Zkousel jsem reseni co bere jako moznost, ze u nekoho neexistuje
 dvojice zaznamu nebo ze pro jeden ze smeru (in / out) je vice zaznamu.

 (smer je urcen pomoci boolean 'isin')

SELECT * FROM rule ORDER BY name;
 name | isin | data  
------+------+-------
 AAA  | t    |   125
 AAA  | f    |    25
 AAA  | f    | 10000
 BBB  | t    |  1025
 BBB  | f    |  1000
 CCC  | t    |   100
 CCC  | f    |    46
 DDD  | f    |    46
 EEE  | t    | 40000


SELECT name, i.data AS in, o.data AS out 
FROM  (
    SELECT name, sum(data) AS data FROM rule WHERE isin='t' GROUP BY name
) i FULL JOIN (
    SELECT name, sum(data) AS data FROM rule WHERE isin='f' GROUP BY name
) o USING( name );

 name |  in   |  out  
------+-------+-------
 AAA  |   125 | 10025
 BBB  |  1025 |  1000
 CCC  |   100 |    46
 DDD  |       |    46
 EEE  | 40000 |      


    Karel

-- 
 Karel Zak  <zakkr na zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/