j'ai la requête mysql suivante :

SELECT 
    a.id, a.sbu_name, b.month,b.week, b.DateRange, b.planned
FROM
    (SELECT 
        id, sbu_name
    FROM
        tbl_sbu) a
        LEFT JOIN
    (SELECT 
        sbu_id,
            month,
            WEEK(month) week,
            CONCAT(DATE_FORMAT(DATE_ADD(month, INTERVAL (1 - DAYOFWEEK(month)) DAY), '%Y-%m-%e'), ' TO ', DATE_FORMAT(DATE_ADD(month, INTERVAL (7 - DAYOFWEEK(month)) DAY), '%Y-%m-%e')) AS DateRange,
            SUM(amount) / 100000 AS planned
    FROM
        tbl_collection_planed
    WHERE
        DATE_FORMAT(month, '%Y-%m') = '2016-08'
    GROUP BY DateRange) b ON b.sbu_id = a.id order by week asc

La sortie de la requête ci-dessus est : image de la sortie actuelle

Je veux ma sortie comme suit: image du résultat recherché

0
Nitin 29 juin 2016 à 09:52

2 réponses

Meilleure réponse

Ceci est une question pivot de table, si juste pour ce cas, vous pouvez essayer la requête suivante ;)

SELECT 
    sbu_name,
    IF(DateRange = '2016-08-7 TO 2016-08-13', planned, '') AS `2016-08-7 TO 2016-08-13`,
    IF(DateRange = '2016-08-14 TO 2016-08-20', planned, '') AS `2016-08-14 TO 2016-08-20`,
    IF(DateRange = '2016-08-21 TO 2016-08-27', planned, '') AS `2016-08-21 TO 2016-08-27`,
    IF(DateRange = '2016-08-28 TO 2016-09-3', planned, '') AS `2016-08-28 TO 2016-09-3`
FROM (
    SELECT 
        a.id, a.sbu_name, b.month,b.week, b.DateRange, b.planned
    FROM
        (SELECT 
            id, sbu_name
        FROM
            tbl_sbu) a
            LEFT JOIN
        (SELECT 
            sbu_id,
                month,
                WEEK(month) week,
                CONCAT(DATE_FORMAT(DATE_ADD(month, INTERVAL (1 - DAYOFWEEK(month)) DAY), '%Y-%m-%e'), ' TO ', DATE_FORMAT(DATE_ADD(month, INTERVAL (7 - DAYOFWEEK(month)) DAY), '%Y-%m-%e')) AS DateRange,
                SUM(amount) / 100000 AS planned
        FROM
            tbl_collection_planed
        WHERE
            DATE_FORMAT(month, '%Y-%m') = '2016-08'
        GROUP BY DateRange) b ON b.sbu_id = a.id order by week asc
) t
GROUP BY sub_name

Si ce DateRange a des valeurs dynamiques, vous devez le faire avec un sql dynamique.

ÉDITÉ (sql dynamique)

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(DateRange = ''',
      CONCAT(DATE_FORMAT(DATE_ADD(month, INTERVAL (1 - DAYOFWEEK(month)) DAY), '%Y-%m-%e'), ' TO ', DATE_FORMAT(DATE_ADD(month, INTERVAL (7 - DAYOFWEEK(month)) DAY), '%Y-%m-%e')),
      ''', planned, '')) AS `',
      CONCAT(DATE_FORMAT(DATE_ADD(month, INTERVAL (1 - DAYOFWEEK(month)) DAY), '%Y-%m-%e'), ' TO ', DATE_FORMAT(DATE_ADD(month, INTERVAL (7 - DAYOFWEEK(month)) DAY), '%Y-%m-%e')), '`'
    )
  ) INTO @sql
FROM tbl_collection_planed
WHERE DATE_FORMAT(month, '%Y-%m') = '2016-08'
;
SET @sql = CONCAT('SELECT sbu_name, ', @sql, ' FROM (
    SELECT 
        a.id, a.sbu_name, b.month,b.week, b.DateRange, b.planned
    FROM
        (SELECT 
            id, sbu_name
        FROM
            tbl_sbu) a
            LEFT JOIN
        (SELECT 
            sbu_id,
                month,
                WEEK(month) week,
                CONCAT(DATE_FORMAT(DATE_ADD(month, INTERVAL (1 - DAYOFWEEK(month)) DAY), \'%Y-%m-%e\'), \' TO \', DATE_FORMAT(DATE_ADD(month, INTERVAL (7 - DAYOFWEEK(month)) DAY), \'%Y-%m-%e\')) AS DateRange,
                SUM(amount) / 100000 AS planned
        FROM
            tbl_collection_planed
        WHERE
            DATE_FORMAT(month, \'%Y-%m\') = \'2016-08\'
        GROUP BY DateRange) b ON b.sbu_id = a.id order by week asc
) t
GROUP BY sub_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
0
Blank 29 juin 2016 à 11:42

Essaye ça

**select * from (**
SELECT 
a.id, a.sbu_name, b.month,b.week, b.DateRange, b.planned
FROM
    (SELECT 
    id, sbu_name
    FROM
    tbl_sbu) a
    LEFT JOIN
    (SELECT 
    sbu_id,
        month,
        WEEK(month) week,
        CONCAT(DATE_FORMAT(DATE_ADD(month, INTERVAL (1 -  DAYOFWEEK(month)) DAY), '%Y-%m-%e'), ' TO ', DATE_FORMAT(DATE_ADD(month,  INTERVAL (7 - DAYOFWEEK(month)) DAY), '%Y-%m-%e')) AS DateRange,
        SUM(amount) / 100000 AS planned
    FROM
    tbl_collection_planed
    WHERE
    DATE_FORMAT(month, '%Y-%m') = '2016-08'
    GROUP BY DateRange) b ON b.sbu_id = a.id **) as b group by DateRange**
-1
Maria Ivanova 29 juin 2016 à 12:07