mysql - 'Stuff' 和 'FOR XML PATH' 在 Mysql 中替代 Ms-sql 的以下存储过程

Question

我在 Ms-Sql 中有以下查询

  INSERT INTO tbl_web_price_update 

    Select bd_book_code,

    Case 
        When pd.bpd_price is null then  
            cast((a.bd_price*c.ptm_price) as numeric(10))   
        else
            cast((pd.bpd_price*c.ptm_price) as numeric(10))             
        end
    As Price

    from tbl_books_details a 
        inner join tbl_price_type_master c on a.bd_price_type = c.ptm_price_type_id 
        left join tbl_books_price_details pd on pd.bpd_book_code = a.bd_book_code 
    Where c.ptm_price_type_id = @price_type     

    SELECT distinct r.price, STUFF((SELECT distinct ','+ Cast(a.bd_book_code as varchar) FROM tbl_web_price_update a WHERE r.price = a.price FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') FROM tbl_web_price_update r

我已经从 ms-sql 迁移到 my-sql，但我无法在 Mysql 中编写替代查询。请帮忙。

Answer 1

看起来你正在寻找这样的东西

SELECT price, GROUP_CONCAT(DISTINCT bd_book_code) bd_book_code
  FROM tbl_web_price_update
 GROUP BY price

这是 SQLFiddle 演示

• GROUP_CONCAT()功能。