Avatar billede JacoDK Nybegynder
08. august 2015 - 18:01 Der er 8 kommentarer og
3 løsninger

Sum af mappe hierarki, child/parent

Hej eksperter,

Jeg leger med et lille projekt, hvor jeg har en tabel med mapper, hvori der kan laves hoved og undermapper i et utal af kombinationer. Et eksempel på dette kan være

mID, mNavn, mForældre
1, Hovedmappe, Null
2, Undermappe 1, 1
3, Undermappe 2, 1
  4, Undermappe 2.1, 2
  5, Undermappe 2.1.1, 4
6, Hovedmappe 2


Til alle mapperne er en tabel med Mappe linjer, som indeholder en værdi, som via JOIN kan hentes ind på mapperne.

Mit problem er dog at jeg ønsker en total af alle undermapper, så hovedmappen vil få en ALIAS kolonne med totalen af samtlige undermapper tilknyttet hovedmappens ID.

Jeg har en steng, som fungere nogenlunde, men den tager kun hoved og undermappen, og kommer ikke helt i bund.

SELECT
    `Parent`.`FolderID`,
    `Parent`.`FolderParentID`,
    `Parent`.`FolderName`,
    `Parent`.`FolderPriority`,
    SUM(`Parent`.`Total`) AS `FolderValue`,
    SUM(`Child`.`Sum`) AS `FolderTotal`,
    IFNULL(SUM(`Parent`.`Total`), 0) + IFNULL(SUM(`Child`.`Sum`), 0) AS `FolderSum`
FROM
(
SELECT
    `xFolder`.`FolderID`, `xFolder`.`FolderName`, `xFolder`.`FolderParentID`, `xFolder`.`FolderPriority`, SUM(`xLine`.`FolderLineTotal` * `xLine`.`FolderLineQuantity`) AS `Total`
FROM `x_Folder` AS `xFolder`
LEFT JOIN `x_FolderLine` AS `xLine` ON `xLine`.`FolderID` = `xFolder`.`FolderID`
GROUP BY `xFolder`.`FolderID`
) AS `Parent`

LEFT OUTER JOIN
(
SELECT
    `yFolder`.`FolderID`, `yFolder`.`FolderParentID`, SUM(`yLine`.`FolderLineTotal` * `yLine`.`FolderLineQuantity`) AS `Sum`
FROM `x_Folder` AS `yFolder`
LEFT JOIN `x_FolderLine` AS `yLine` ON `yLine`.`FolderID` = `yFolder`.`FolderID`
GROUP BY `yFolder`.`FolderID`
) AS `Child`
ON `Child`.`FolderParentID` = `Parent`.`FolderID`

GROUP BY `Parent`.`FolderID`
ORDER BY
    `Parent`.`FolderParentID` IS NOT NULL,
    `Parent`.`FolderPriority` DESC,
    COALESCE(`Parent`.`FolderID`, `Parent`.`FolderParentID`)


Jeg håber det giver mening, og jeg har vedlagt et skærmbillede, som forhåbentlig kan uddybe min forklaring http://tinypic.com/r/32zqer4/8

på forhånd tak
Avatar billede olsensweb.dk Ekspert
08. august 2015 - 18:36 #1
kan du ikke lave en sql dump af tabellerne der indgår i queryen ??, samt nogle test værdier
tænker vi skal ud i noget recursive kald
https://www.google.com/search?q=mysql+recursive+query
https://www.google.com/search?q=mysql+recursive+query+example
https://www.google.com/search?q=mysql+recursive+query+parent+child
Avatar billede JacoDK Nybegynder
08. august 2015 - 20:11 #2
Hej Ronols,

Hermed SQL dump


CREATE TABLE IF NOT EXISTS `x_Folder` (
`FolderID` int(11) NOT NULL,
  `FolderParentID` int(11) DEFAULT NULL,
  `FolderNumber` varchar(65) COLLATE utf8_danish_ci NOT NULL,
  `FolderName` varchar(125) COLLATE utf8_danish_ci NOT NULL,
  `FolderDescription` text COLLATE utf8_danish_ci NOT NULL,
  `FolderQuantity` double(11,2) NOT NULL,
  `FolderUnit` varchar(5) COLLATE utf8_danish_ci NOT NULL,
  `FolderPrecision` double(11,2) NOT NULL,
  `FolderPriority` int(11) DEFAULT NULL,
  `FolderProjectID` int(11) NOT NULL,
  `FolderCompanyID` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

--
-- Data dump for tabellen `x_Folder`
--

INSERT INTO `x_Folder` (`FolderID`, `FolderParentID`, `FolderNumber`, `FolderName`, `FolderDescription`, `FolderQuantity`, `FolderUnit`, `FolderPrecision`, `FolderPriority`, `FolderProjectID`, `FolderCompanyID`) VALUES
(1, NULL, '00', 'Mappe 1', 'Dette er den første mappe i projektet', 1.00, 'STK', 0.10, NULL, 1, 1000),
(2, NULL, '01', 'Mappe 2', 'Dette er mappe 2, som er et barn til den første mappe', 1.00, 'STK', 0.10, NULL, 1, 1000),
(3, 2, '02', 'Mappe 3', 'Dette er mappe 3, som er et barn til mappe 2', 1.00, 'STK', 0.10, NULL, 1, 1000),
(4, 2, '04', 'Mappe 4', 'Dette er mappe 4, som er et barn til mappe 2. Denne skulle gerne stå under mappe 2.', 1.00, 'STK', 0.10, 100, 1, 1000),
(5, 1, '05', 'Mappe 5', 'Dette er mappe 5, som er et barn til mappe 1', 1.00, 'STK', 0.10, NULL, 1, 1000),
(6, 2, '06', 'Mappe 6', 'Denne mappe er en undermappe til mappe 5', 1.00, 'STK', 0.00, NULL, 1, 1000),
(7, 2, '06', 'Mappe 6', 'Denne mappe er en undermappe til mappe 5', 1.00, 'STK', 0.10, 10, 1, 1000),
(8, 7, '08', 'Undermappe 8', 'Dette er en undermappe til mappe 7', 1.00, 'STK', 0.10, NULL, 1, 1000),
(9, 5, '09', 'Mappe 8', 'Dette er mapppe 8', 10.00, 'STK', 0.10, NULL, 1, 1000),
(10, NULL, 'Mappe 2', 'Dette er en ny hovedmappe', 'Dette er en ny hovedmappe', 1.00, 'STK', 0.10, NULL, 1, 1000);


og linjer

CREATE TABLE IF NOT EXISTS `x_FolderLine` (
`FolderLineID` int(11) NOT NULL,
  `FolderLineType` varchar(12) COLLATE utf8_danish_ci NOT NULL,
  `FolderLineNumber` varchar(35) COLLATE utf8_danish_ci NOT NULL,
  `FolderLineQuantity` double(11,3) NOT NULL,
  `FolderLineTotal` double(11,3) NOT NULL,
  `FolderLinePriority` int(11) NOT NULL,
  `CompanyID` int(11) NOT NULL,
  `ProjectID` int(11) NOT NULL,
  `FolderID` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

--
-- Data dump for tabellen `x_FolderLine`
--

INSERT INTO `x_FolderLine` (`FolderLineID`, `FolderLineType`, `FolderLineNumber`, `FolderLineQuantity`, `FolderLineTotal`, `FolderLinePriority`, `CompanyID`, `ProjectID`, `FolderID`) VALUES
(1, 'PRODUCT', '094092', 10.000, 1.000, 0, 1000, 1, 1),
(2, 'PRODUCT', '094092', 15.000, 1.000, 0, 1000, 1, 6),
(3, 'PRODUCT', '094092', 10.000, 1.000, 0, 1000, 1, 6),
(4, 'PRODUCT', '094092', 10.000, 7.000, 0, 1000, 1, 3),
(5, 'PRODUCT', '094092', 10.000, 1.000, 0, 1000, 1, 8),
(6, 'PRODUCT', '094092', 10.000, 1.000, 0, 1000, 1, 9);
08. august 2015 - 20:36 #3
JakoDK, vi har aftalt andetsteds, at jeg skal være med her.  Men jeg er enig med #1, at du ikke har gjort det klart hvordan dine tabeler ser ud og hvad de indeholder.  Det er heller ikke let at forstå hvad du vil opnå.  Jeg tillader mig, som et gæt, at opstille denne lille database over et antal grupper og deres medlemmer, og hvor mange points hvert medlem har.  Grupperne består af over- og undergrupper og har geografiske navne.

Tabel 1 Grupper
ID  Navn  Forældergruppe
1  Danmark  Null
2  Fyn          1
3  Odense    2
4  Næsby      3
5  Jylland      1

Tabel 2 Medlemmer
ID  Gruppe  Navn  Punkter
1      1        Hans      5
2      2        Ella      14
3      4        Jens      3
4      4        Tove      7
5      4        Lise      22
6      5        Mads      2

Hans er således medlem af gruppen Danmark direkte.  Jens er medlem af Fyn og dermed indirekte medlem af Danmark.  Næsby har tre medlemmer, der således er indirekte medlemmer af Odense og af Fyn og af Danmark.  O.s.v.

Nu vil jeg lave et udskrift af alle grupperne og for hver gruppe hvor mange punkter dens direkte plus indirekte medlemmer har, så vil resultatet blive dette:

Danmark  53
Fyn  46
Odense  32
Næsby  32
Jylland 2

Det var så mit lille eksempel.  Ligner det i nogen måde dine data og det resultat du vil opnå?  Eller er det en hel anden slags data du har og vil du opnå noget helt anderledes?
Avatar billede JacoDK Nybegynder
08. august 2015 - 21:05 #4
Hej Christian,

Det princip du skitsere ligner netop den slags data jeg ønsker, og jeg kan ikke beskrive det bedre end du lige har gjort.

Så hvordan kan jeg ved hjælp af MySQL fremvise data'en på den måde ?

På forhånd tusind tak
Avatar billede arne_v Ekspert
09. august 2015 - 02:09 #5
Det kan goeres med en rekursiv user defined function / stored procedure.

Men med stor sandsynlighed vil det vaere langt nemmere at lave den aggregering i din applikation (PHP/Java/C#/whatever).
Avatar billede JacoDK Nybegynder
09. august 2015 - 18:37 #6
Okay, så må jeg arbejde på at summere det hele i PHP.

Hvis ikke der er andre som kender en MYSQL løsning, så må I gerne lægge et svar, da alle er kommet med konstruktive idéer :)
09. august 2015 - 21:07 #8
Jeg var væk for en kort bemærkning; nu er jeg her igen.  Jeg var så længe om at færtiggøre mit indlæg #3, at du i mellemtiden fik lavet indlæg #2, og det så jeg ikke.  Jeg er iøvrigt enig med #5 - SQL's styrke ligger i at opbevare data og gengive dem igen; presentation af de gengivne data sker så i applikationen.
Avatar billede arne_v Ekspert
10. august 2015 - 03:23 #9
Det er ret nemt i PHP.

Her er et eksempel.

Jeg har valgt andre data, men teknikken skulle vaere nem at gennemskue.


CREATE TABLE dirs (
    id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    parent INTEGER,
    PRIMARY KEY (id)
);

CREATE TABLE files (
    id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    size INTEGER NOT NULL,
    dir INTEGER NOT NULL,
    PRIMARY KEY(id)
);

INSERT INTO dirs VALUES (1, 'C:\\', NULL);
INSERT INTO dirs VALUES (2, 'C:\\adir', 1);
INSERT INTO dirs VALUES (3, 'C:\\bdir', 1);
INSERT INTO dirs VALUES (4, 'C:\\adir\\1dir', 2);
INSERT INTO dirs VALUES (5, 'C:\\adir\\2dir', 2);

INSERT INTO files VALUES (1, 'root.txt', 123, 1);
INSERT INTO files VALUES (2, 'a.txt', 456, 2);
INSERT INTO files VALUES (3, 'b.txt', 789, 3);
INSERT INTO files VALUES (4, 'bbig.txt', 10000, 3);
INSERT INTO files VALUES (5, 'a1.txt', 111, 4);
INSERT INTO files VALUES (6, 'a1big.txt', 11111, 4);
INSERT INTO files VALUES (7, 'a2.txt', 222, 5);
INSERT INTO files VALUES (8, 'a2big.txt', 22222, 5);



<?php
class Dir {
    public $id;
    public $name;
    public $parentid;
    public $immedsize;
    public $subdir;
    public $totalsize;
    public function __construct($id, $name, $parentid, $immedsize) {
        $this->id = $id;
        $this->name = $name;
        $this->parentid = $parentid;
        $this->immedsize = $immedsize;
        $this->subdir = array();
        $this->totalsize = 0;
    }
};

function fixup_subdir($dirlist) {
    foreach($dirlist as $id => $dirobj) {
        if($dirobj->parentid != 0) {
            $dirlist[$dirobj->parentid]->subdir[] = $dirobj;
        }
    }
}

function calculate_totalsize($dir) {
    $res = $dir->immedsize;
    foreach($dir->subdir as $subdir) {
        $res += calculate_totalsize($subdir);
    }
    return $res;
    /*
    // use this version for serious use:
    if($dir->totalsize == 0) {
        $dir->totalsize = $dir->immedsize;
        foreach($dir->subdir as $subdir) {
            $dir->totalsize += calculate_totalsize($subdir);
        }
    }
    return $dir->totalsize;
    */
}

function fixup_totalsize($dirlist) {
    foreach($dirlist as $id => $dirobj) {
        $dirobj->totalsize = calculate_totalsize($dirobj);
    }
}

function load() {
    $dirlist = array();
    $con = new mysqli('localhost', 'root', '', 'Test');
    $stmt = $con->prepare('SELECT dirs.id,dirs.name,dirs.parent,SUM(size) AS sumsize FROM dirs LEFT JOIN files ON dirs.id=files.dir GROUP BY dirs.id,dirs.name,dirs.parent;');
    $stmt->execute();
    $stmt->bind_result($id, $name, $parentid, $immedsize);
    while($stmt->fetch()) {
        $dirlist[$id] = new Dir($id, $name, $parentid, $immedsize);
    }
    $stmt->close();
    $con->close();
    fixup_subdir($dirlist);
    fixup_totalsize($dirlist);
    return $dirlist;
}

$dirlist = load();

foreach($dirlist as $id => $dirobj) {
    echo sprintf("%-20s %8d\r\n", $dirobj->name, $dirobj->totalsize);
}

?>
Avatar billede JacoDK Nybegynder
11. august 2015 - 17:56 #10
Tusind tak for jeres bidrag :D

arne_v du må gerne smide et svar også :)
Avatar billede arne_v Ekspert
11. august 2015 - 18:03 #11
svar
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester