mysql - 在mysql中按问题排序

Question

这里共有两个联合绑定(bind)了三个查询，第一个查询从虚拟表中检索记录(内部查询)，两个简单查询从物理表中检索记录。所有三个查询都创建了一个名为“vertulatable”的虚拟表，使用 group by 和 order by 子句从“vertualtable”中检索所有记录。但问题是它没有按最终价格进行适当的降序排列。从中介查询中检索到的某些记录的值为 150，但显示在 0 或小于 150 之后，但在结果记录中应位于 0 或小于 150 的顶部。

SELECT 
sum(vertualtable.WyoPrice) as WyoPrice, 
       sum(vertualtable.normalPrice) as normalPrice, 
       vertualtable.sbnam,
       vertualtable.sbwynum, 
      if(vertualtable.sbwynum in(25,43), 
           sum(vertualtable.WyoPrice), sum(vertualtable.normalPrice)) as finalPricePrev,
        sum(vertualtable.finalPrice) as  finalPrice,
     vertualtable.BuilderStatus

 FROM (


SELECT sum(vertualtbl.WyoPrice) as WyoPrice, 
       sum(vertualtbl.normalPrice) as normalPrice, 
       vertualtbl.sbnam,
       vertualtbl.sbwynum, 
      if(vertualtbl.sbwynum in(25,43), 
           sum(vertualtbl.WyoPrice), sum(vertualtbl.normalPrice)) as finalPrice,
     'Main' as BuilderStatus
FROM(

  select 
        b.sbnam,
        b.sbwynum,
        'g'        as g,
        sum(if (s.adjprice > 0, s.adjprice, if (s.price > 0, s.price,
         s.estprice))) as normalPrice,
        st.forsearchwoy,
        SUM(if (st.forsearchwoy = 'Y',
               F_offshorePrice(s.topsbwynum, s.hulsbwynum, s.sbwynum, s.adjprice,
            s.price, s.estprice, s.pricehulint, s.pricetopint, s.pricehulcons,
             s.pricetop, s.priceint, s.pricetht),0)) as WyoPrice
 from tblship s
      left join tblbuilder b on b.sbwynum = s.sbwynum and b.deleted = 'N'
      left join tblshiptype as st on st.wytypid = s.wytypid
 where s.deleted != 'Y' and
      (s.sbwynum > 0) and
       status in ('O', 'FO', 'JV', 'PR', 'C', 'F')

 group by s.tblshipwynum



 ) vertualtbl group by vertualtbl.sbwynum 




UNION 

  select 
        '0' as WyoPrice,
        '0' as normalPrice,
        b.sbnam as sbnam,        
        s.hulsbwynum as sbwynum,
        SUM(if (st.forsearchwoy = 'Y',s.pricehulcons,0)) as finalPrice,
        'HUL' as BuilderStatus
 from tblship s
      left join tblbuilder b on b.sbwynum = s.hulsbwynum and b.deleted = 'N'
      left join tblshiptype as st on st.wytypid = s.wytypid
 where s.deleted != 'Y' and
      (s.sbwynum != s.hulsbwynum and
       s.hulsbwynum > 0 ) and
       status in ('O', 'FO', 'JV', 'PR', 'C', 'F')
 group by s.hulsbwynum



  UNION

  select 
       '0' as WyoPrice,
       '0' as normalPrice,
        b.sbnam as sbnam,        
        s.topsbwynum as sbwynum,        
        SUM(if (st.forsearchwoy = 'Y',s.pricetop,0)) as finalPrice,
        'TOP' as BuilderStatus
 from tblship s
      left join tblbuilder b on b.sbwynum = s.topsbwynum and b.deleted = 'N'
      left join tblshiptype as st on st.wytypid = s.wytypid
 where s.deleted != 'Y' and
      (s.sbwynum != s.topsbwynum and
       s.topsbwynum > 0 ) and
       status in ('O', 'FO', 'JV', 'PR', 'C', 'F')
  group by s.hulsbwynum 

 ) as vertualtable 

 group by vertualtable.sbnam
 order by vertualtable.finalPrice desc 

非常感谢。

Answer 1

我对您的查询做了一些小的修改 - 试试看结果是否是您预期的结果

SELECT 
sum(vertualtable.WyoPrice) as WyoPrice, 
       sum(vertualtable.normalPrice) as normalPrice, 
       vertualtable.sbnam AS sbnam,
       vertualtable.sbwynum, 
      if(vertualtable.sbwynum in(25,43), 
           sum(vertualtable.WyoPrice), sum(vertualtable.normalPrice)) as finalPricePrev,
        sum(vertualtable.finalPrice) as  finalPrice,
     vertualtable.BuilderStatus

 FROM (


SELECT sum(vertualtbl.WyoPrice) as WyoPrice, 
       sum(vertualtbl.normalPrice) as normalPrice, 
       vertualtbl.sbnam AS sbnam,
       vertualtbl.sbwynum, 
      if(vertualtbl.sbwynum in(25,43), 
           sum(vertualtbl.WyoPrice), sum(vertualtbl.normalPrice)) as finalPrice,
     'Main' as BuilderStatus
FROM(

  select 
        b.sbnam AS sbnam,
        b.sbwynum,
        'g'        as g,
        sum(if (s.adjprice > 0, s.adjprice, if (s.price > 0, s.price,
         s.estprice))) as normalPrice,
        st.forsearchwoy,
        SUM(if (st.forsearchwoy = 'Y',
               F_offshorePrice(s.topsbwynum, s.hulsbwynum, s.sbwynum, s.adjprice,
            s.price, s.estprice, s.pricehulint, s.pricetopint, s.pricehulcons,
             s.pricetop, s.priceint, s.pricetht),0)) as WyoPrice
 from tblship s
      left join tblbuilder b on b.sbwynum = s.sbwynum and b.deleted = 'N'
      left join tblshiptype as st on st.wytypid = s.wytypid
 where s.deleted != 'Y' and
      (s.sbwynum > 0) and
       status in ('O', 'FO', 'JV', 'PR', 'C', 'F')

 group by s.tblshipwynum



 ) vertualtbl group by vertualtbl.sbwynum 




UNION 

  select 
        '0' as WyoPrice,
        '0' as normalPrice,
        b.sbnam as sbnam,        
        s.hulsbwynum as sbwynum,
        SUM(if (st.forsearchwoy = 'Y',s.pricehulcons,0)) as finalPrice,
        'HUL' as BuilderStatus
 from tblship s
      left join tblbuilder b on b.sbwynum = s.hulsbwynum and b.deleted = 'N'
      left join tblshiptype as st on st.wytypid = s.wytypid
 where s.deleted != 'Y' and
      (s.sbwynum != s.hulsbwynum and
       s.hulsbwynum > 0 ) and
       status in ('O', 'FO', 'JV', 'PR', 'C', 'F')
 group by s.hulsbwynum



  UNION

  select 
       '0' as WyoPrice,
       '0' as normalPrice,
        b.sbnam as sbnam,        
        s.topsbwynum as sbwynum,        
        SUM(if (st.forsearchwoy = 'Y',s.pricetop,0)) as finalPrice,
        'TOP' as BuilderStatus
 from tblship s
      left join tblbuilder b on b.sbwynum = s.topsbwynum and b.deleted = 'N'
      left join tblshiptype as st on st.wytypid = s.wytypid
 where s.deleted != 'Y' and
      (s.sbwynum != s.topsbwynum and
       s.topsbwynum > 0 ) and
       status in ('O', 'FO', 'JV', 'PR', 'C', 'F')
  group by s.hulsbwynum 

 ) as vertualtable 

 group by sbnam
 order by finalPrice desc