SELECT
ANY_VALUE(t5.categoryName) AS categoryName,
t5.model,
t1.standard_asset_id,
t1.management_dept_id,
ANY_VALUE(t1.user_dept_id) AS user_dept_id,
ANY_VALUE(t1.user_id) AS user_id,
ANY_VALUE(t1.provider_flag) AS provider_flag,
ROUND(t1.price, 2) AS price,
ANY_VALUE(t1.position_id) AS position_id,
SUM(IFNULL(t2.account_num,0)) AS num,
SUM(IFNULL(t1.num,0)) AS amount,
ANY_VALUE(t2.remarks) AS remarks,
ANY_VALUE(t1.purchase_date) AS purchase_date,
SUM(IFNULL(t2.first_inventory_num,0)) AS inventory_num,
SUM(IFNULL(t2.second_inventory_num,0)) AS secondInventoryNum,
SUM((IFNULL(t2.account_num,0) - IFNULL(t2.first_inventory_num,0))) AS firstInventoryWinAndLossNum,
SUM((IFNULL(t2.account_num,0) - IFNULL(t2.second_inventory_num,0))) AS secondInventoryWinAndLossNum,
SUM(ROUND(((IFNULL(t2.account_num,0) - IFNULL(t2.first_inventory_num,0)) * price), 2)) AS firstInventoryAmount,
SUM(ROUND(((IFNULL(t2.account_num,0) - IFNULL(t2.second_inventory_num,0)) * price), 2)) AS secondInventoryAmount
FROM
asset_escrow_ledger t1
LEFT JOIN asset_escrow_company_inventory_ledger t2 ON t1.id = t2.ledger_id
LEFT JOIN (
SELECT
t1.id,
t1.NAME,
t1.model,
t1.unit,
t1.category_id,
t1.manufacturer_id,
t2.NAME AS childCategoryName,
t3.NAME AS categoryName,
t4.NAME AS parentCategoryName
FROM
asset_standard_asset t1
LEFT JOIN asset_category t2 ON t1.category_id = t2.id
LEFT JOIN asset_category t3 ON t2.parent_id = t3.id
LEFT JOIN asset_category t4 ON t3.parent_id = t4.id
WHERE
t1.data_type = ‘escrow’) t5 ON t1.standard_asset_id = t5.id
WHERE
inventory_id = 7
AND ledger_id IN (26580, 26596, 27189, 33805, 47091, 47151, 47152, 47312, 47313, 47327, 47917, 47938, 48103, 48104, 48105, 50312, 50320, 50323, 50324, 50325, 50784, 50785, 50809, 50810, 50813, 60377, 60414, 60415, 60569, 61159, 61160, 61240, 61242, 61243, 61244, 61245, 61246, 61264, 61266, 61267, 61268, 61270, 61272, 61273, 61274, 61275, 61276, 61278, 61280, 61282, 61283, 61284, 61286, 61287, 61290, 61292, 61293, 61300, 61301, 61302, 61304, 61305, 61306, 61307, 61312, 61313, 61314, 61320, 61321, 61322, 61324, 61325, 61326, 61327, 61328, 61330, 61331, 61332, 61333, 61348, 61349, 61350, 61351, 61353, 61355, 61356, 61357, 61358, 61361, 61362, 61363, 61364, 61365, 61366, 61367, 61368, 62991, 62992, 62994, 63770, 63872, 63885, 63892, 64120, 64121, 64122, 64126, 64128, 64129, 64130, 64149, 64161, 64163, 64164, 64165, 64199, 64200, 64212, 64213, 64214, 64219, 64245, 64249, 64250, 64252, 64255, 64262, 64286, 64312, 64313, 64314, 64315, 64316, 64317, 64318, 64319, 64320, 64321, 64322, 64323, 64324, 64325, 64326, 64327, 64328, 64329, 64330, 64331, 64333, 64340, 64345, 64416, 64426, 64427, 64440, 64453, 64460, 64463, 64469, 64591, 64592, 64593, 64594, 64595, 64596, 64597, 64699, 79854, 80087, 80176, 80644, 80730, 80789, 80792, 80803, 81099)
GROUP BY t1.standard_asset_id, t5.model, t1.management_dept_id
这是我的SQL语句，目前报错原因是因为sql_mode=only_full_group_by，我的MYSQL版本是5.7，现在我要在能够获取SELECT列表中所有内容的情况下解决这个问题，但是我希望我的price要是分组情况下拿去的是每一组t1.purchase_date最晚的时间
最新发布
本文介绍了如何使用 SQL 中的 CONCAT 和 SUBSTRING 函数来处理字符串。通过具体示例展示了如何截取字符串的一部分并将其与其他部分组合起来，这对于保护敏感信息非常有用。
扫一扫
1524
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
