1. SQL / Говнокод #26334

    +1

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    39. 39
    40. 40
    41. 41
    42. 42
    SELECT SQL_NO_CACHE IT.itemCode,
                                    SUBSTRING_INDEX(GROUP_CONCAT(IT.ean),",", 1) as ean,
                                    SUBSTRING_INDEX(GROUP_CONCAT(IT.artCode),",", 1) as artCode,
                                    SUBSTRING_INDEX(GROUP_CONCAT(IT.description),",", 1) as description,
                                    SUBSTRING_INDEX(GROUP_CONCAT(IT.siteName),",", 1) as siteName,
                                    SUBSTRING_INDEX(GROUP_CONCAT(IT.familyName),",", 1) as familyName,
                                    SUBSTRING_INDEX(GROUP_CONCAT(IT.familyCode),",", 1) as familyCode,
                                    SUBSTRING_INDEX(GROUP_CONCAT(IT.SSfamilyCode),",", 1) as SSfamilyCode,
                                    SUBSTRING_INDEX(GROUP_CONCAT(IT.SSfamilyName),",", 1) as SSfamilyName,
                                    SUBSTRING_INDEX(GROUP_CONCAT(IT.sectorCode),",", 1) as sectorCode,
                                    SUBSTRING_INDEX(GROUP_CONCAT(IT.sectorName),",", 1) as sectorName,
                                    SUBSTRING_INDEX(GROUP_CONCAT(IT.radiusCode),",", 1) as radiusCode,
                                    SUBSTRING_INDEX(GROUP_CONCAT(IT.radiusName),",", 1) as radiusName,
                                    FI.areaName,
                                    FI.areaCode,
                                    ROUND(SUM(IT.price), 2) as price,
                                    SUM(IT.quantity) as theoreticalQty,
                                    IFNULL(FI.countedQty, 0) as countedQty,
                                    ROUND(SUM(IT.quantity) * ROUND(SUM(IT.price), 2), 2) as theoreticalQtyValue,
                                    ROUND(IFNULL(FI.countedQty, 0) * ROUND(SUM(IT.price), 2), 2) as countedQtyValue,
                                    IFNULL(FI.countedQty, 0) - SUM(IT.quantity) as stockGapQty,
                                    ROUND((IFNULL(FI.countedQty, 0) - SUM(IT.quantity)) * SUM(IT.price), 2) as stockGapValue,
                                    DATE_FORMAT(ST.lastImport, "%d-%m-%Y %H:%i:%s") as dateOfLastImport,
                                    DATE_FORMAT(NOW(), "%d-%m-%Y %H:%i:%s") as dateOfExport
                            FROM Items as IT
                            LEFT JOIN ( 
                                SELECT MIN(FI.ean) as ean, SUM(FI.quantity) as countedQty, IT.itemCode,
                                GROUP_CONCAT(DISTINCT AR.name) as areaName,
                                GROUP_CONCAT(DISTINCT AR.code) as areaCode
                                FROM FoundItems as FI
                                INNER JOIN Items as IT ON FI.ean = IT.ean
                                LEFT JOIN Areas as AR ON AR.id = FI.areaId
                                
                                INNER JOIN Stores as ST ON ST.id = IT.storeId
                                
                                WHERE eventId = ${args.eventId}
                                AND IT.storeId = (SELECT storeId FROM Events WHERE id = ${args.eventId})
                                GROUP BY IT.itemCode
                            ) as FI ON IT.itemCode = FI.itemCode
                            INNER JOIN Stores as ST ON ST.id = IT.storeId
                            WHERE IT.storeId = (SELECT storeId FROM Events WHERE id = ${args.eventId})
                            GROUP BY IT.itemCode, FI.areaName, FI.areaCode, FI.countedQty, dateOfLastImport

    а що ця конструкція вміє, а ета що, про та ще й ето потрібно впихнути сюди

    Запостил: silverreve23, 09 Января 2020

    Комментарии (6) RSS

    Добавить комментарий