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

    +2

    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

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