Ms-sql中以下存储过程中的'Stuff'和'FOR XML PATH'在Mysql中有哪些替代方法？

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 迁移到 MySQL，但无法在 MySQL 中编写替代性查询。请帮忙。

- Ratna

Answer 1

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

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

这是一个SQLFiddle演示。

• GROUP_CONCAT()函数。

- peterm

Answer 2

SELECT 
    (SELECT GROUP_CONCAT(cd.diNumber ORDER BY cd.diNumber ASC SEPARATOR ',') FROM transactionalPlms.vw_consignmentDetails cd WHERE cd.tripid = t.tripid) as di_numberaa,   
    (SELECT (cd.diNumber)  FROM transactionalPlms.vw_consignmentDetails cd 
                      where cd.tripid = t.tripid) as di_number,
    (SELECT (cd.MaterialCode)  FROM transactionalPlms.vw_consignmentDetails cd 
                      where cd.tripid = t.tripid) as MaterialCode,
    (SELECT (cd.noofbags)  FROM transactionalPlms.vw_consignmentDetails cd 
                      where cd.tripid = t.tripid) as NoofBags, sd.plannedPacker
FROM transactionalPlms.trip t
Inner join transactionalPlms.sequencingDetail sd on t.tripid = sd.tripId and sd.status = 'A'
Inner join masterPLms.vehicleMaster vm on t.vehicleNumber = vm.registration_number and vm.status = 'A'
where t.status = 'A'
order by sd.manualSequence desc, t.yardIn desc;

- Prince Roy