V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
推荐工具
RoboMongo
推荐书目
50 Tips and Tricks for MongoDB Developers
Related Blogs
Snail in a Turtleneck
coderabbit
V2EX  ›  MongoDB

mysql group sum concat 等转 mongo 查询语句 concat 拼接不对。

  •  
  •   coderabbit · 2019-11-12 16:35:52 +08:00 · 12790 次点击
    这是一个创建于 1598 天前的主题,其中的信息可能已经有所发展或是发生改变。

    从来没用过 mongodb 转写不熟悉现在遇到如下情况 mysql 语句

    SELECT `*`,GROUP_CONCAT(`invoice_number`) as `invoice_number_all`,ROUND(SUM(`invoice_amount`)/100,2) as `invoice_amount_all`,ROUND(SUM(`premium_amount`)/100,2) as `premium_amount_all` FROM `t_invoice` 
    WHERE `id` != 0
    GROUP BY `insurer_id`,`region_id`,`invoice_company_id`,`data_type`,`invoice_date`,`invoice_remark` 
    ORDER BY `invoice_date` DESC LIMIT 0,15
    

    mysql concat 拼接结果: 1

    mongodb 查询语句

    db.t_invoice.aggregate(
    	{
    		"$group": {
    			_id: {
    				insurer_id:"$insurer_id", 
    				region_id: "$region_id", 
    				invoice_company_id:"$invoice_company_id",
    				data_type: "$data_type",
    				invoice_date: "$invoice_date",
    				invoice_remark: "$invoice_remark",
    			},
    			count:{$sum:1},
    			"create_by": {"$first": "$create_by"},
    			"invoice_date": {"$first": {$dateToString: {format: "%Y-%m-%d", "date": {"$add": [new Date(0), {"$multiply" : ["$invoice_date", 1000]}]}}}},
    			"invoice_amount": {"$sum": {"$divide": ["$invoice_amount", 100]}},
    			"invoice_number": {"$first": {"$concat": ["$invoice_number", "-"]}},
    			"invoice_code": {"$first": "$invoice_code"},
    			"invoice_company_name": {"$first": "$invoice_company_name"},
    			"insurer_name": {"$first": "$insurer_name"},
    			"premium_amount": {"$sum": "$premium_amount"},
    			"region_name": {"$first": "$region_name"},		
    			"invoice_remark": {"$first": "$invoice_remark"},		
    		}
    	}
    )
    

    mongo 查询拼接结果 2

    结果很悲剧 无论我用 concat concatArrays 它都不是把分组里的拿出来,而是只拿一个,如何才能像 mysql 一样拿出分组后的结果来拼接呢?

    2 条回复    2019-11-12 19:03:16 +08:00
    reus
        1
    reus  
       2019-11-12 17:55:32 +08:00 via Android
    谁做这种决定的,谁傻逼。好好的 sql 不用,用残疾的查询语言。真那么喜欢 json,就该换 postgresql
    coderabbit
        2
    coderabbit  
    OP
       2019-11-12 19:03:16 +08:00 via Android
    @reus 看文档解决了,拿他只是做二级数据库的,没接触过,正好要求用,就试试了…
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1049 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 22:34 · PVG 06:34 · LAX 15:34 · JFK 18:34
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.