Sql表自连接，数据库中查询没问题，Hibernate中查询有问题？

来源：互联网发布：淘宝直播中控台网址编辑：程序博客网时间：2024/05/01 08:45

   原先的SQL：
SELECT md.repertory,
       md.mid,
       md.status,
       m.pic,
       m.name,
       m.selling_price,
       m.vendor,
       mp.property_value,
       mp2.property_value AS gyzz,    这两个在数据库中查询时不一致，，但在Hibernate中查询出来结果一样
       md.did,
       md.id,
       (CASE md.retail_price
          WHEN NULL THEN 0.00
          ELSE md.retail_price
        END) AS retail_price
FROM   yk_medicine_drugstore md
       INNER JOIN yk_medicine m
         ON m.id = md.mid
       INNER JOIN yk_medicine_property_detail mp
         ON mp.medicine_id = md.mid
            AND mp.property_id = 6
       INNER JOIN yk_medicine_property_detail mp2
         ON mp2.medicine_id = md.mid
            AND mp2.property_id = 17
WHERE md.did = 19

修改后的SQL：

sql="select md.repertory,md.mID,md.status," +
               "m.pic,m.name,m.selling_price,m.vendor,mp.property_value," +
               "concat('[',mp2.property_value)as gyzz,md.dID,md.id ," +
               "cast(retail_price as decimal(8,2))" +
               " from yk_medicine_drugstore md " +
               "inner join yk_medicine m on m.id=md.mID " +
               "inner join yk_medicine_property_detail mp " +
               "on mp.medicine_id=md.mID and mp.property_id=6 " +
               "inner join yk_medicine_property_detail mp2 " +
               "on mp2.medicine_id=md.mID and mp2.property_id=17 "
               +"where md.dID=19";

0 0