MySQL分组字段与SQL Server，Oracle分组字段的查询字段的坑！

首先，Mysql分组后，查询的字段可以不是分组的字段，而SQL Server和Oracle必须是

目前的问题

select
  billData1.constractId
from
  (
    select
      voucher_order_Order.`constractId` as `constractId`,
      voucher_order_Order.`constractName` as `constractName`,
      voucher_order_Order.`constractCode` as `constractCode`,
      voucher_order_Order.`zrztName` as `zrztName`
    from
      (
        select
          ifnull(T1.bigint_0, T7.id) as `constractId`,
          ifnull(T8.code, T7.code) as constractCode,
          ifnull(T8.ctname, T7.ctname) as constractName,
          T3.name as `zrztName`,
          T0.cOrderNo
        from
          uorders.orders T0
          left join uorders.orders_character_define_1 T2 on T2.id = T0.orderDefineCharacter
          left join iuap_apdoc_basedoc.org_admin T3 on T3.id = T2.vcol1
          left join uorders.iuap_extend_gal3au79_orders_0 T1 on T1.id = T0.id
          left join sact.sact_salescontract T8 on T1.bigint_0 = T8.id
          left join sact.sact_salescontract T7 on T7.Code = T2.vcol3
        where
          T0.iShopID = -1
          and T0.verifystate = '2'
      ) voucher_order_Order
    where
      voucher_order_Order.constractId is not null
    GROUP by
      voucher_order_Order.constractId,
      voucher_order_Order.`zrztName`
  ) billData1
group by
  billData1.constractId
  

很简单的一个关联，但是问题就出现了！根据上面的SQL查询，乍看一看没什么问题，但是当在group外面加上条件后

select zrztName from (
select
  billdata1.constractId,
  billdata1.constractName,
  billdata1.constractCode,
  billdata1.zrztName
from
  (
    select
      voucher_order_Order.`constractId` as `constractId`,
      voucher_order_Order.`constractName` as `constractName`,
      voucher_order_Order.`constractCode` as `constractCode`,
      voucher_order_Order.`zrztName` as `zrztName`
    from
      (
        select
          ifnull(T1.bigint_0, T7.id) as `constractId`,
          ifnull(T8.code, T7.code) as constractCode,
          ifnull(T8.ctname, T7.ctname) as constractName,
          T3.name as `zrztName`,
          T0.cOrderNo
        from
          uorders.orders T0
          left join uorders.orders_character_define_1 T2 on T2.id = T0.orderDefineCharacter
          left join iuap_apdoc_basedoc.org_admin T3 on T3.id = T2.vcol1
          left join uorders.iuap_extend_gal3au79_orders_0 T1 on T1.id = T0.id
          left join sact.sact_salescontract T8 on T1.bigint_0 = T8.id
          left join sact.sact_salescontract T7 on T7.Code = T2.vcol3
        where
          T0.iShopID = -1
          and T0.verifystate = '2'
      ) voucher_order_Order
    where
      voucher_order_Order.constractId is not null
    GROUP by
      voucher_order_Order.constractId,
      voucher_order_Order.`zrztName`
  ) billData1
group by
  billData1.constractId
  )a where a.zrztName = '杨学彬大区'

查询记录为17条，这时也没发现什么问题，但是把where的条件加到group by前面后

select
  zrztName
from
  (
    select
      billdata1.constractId,
      billdata1.constractName,
      billdata1.constractCode,
      billdata1.zrztName
    from
      (
        select
          voucher_order_Order.`constractId` as `constractId`,
          voucher_order_Order.`constractName` as `constractName`,
          voucher_order_Order.`constractCode` as `constractCode`,
          voucher_order_Order.`zrztName` as `zrztName`
        from
          (
            select
              ifnull(T1.bigint_0, T7.id) as `constractId`,
              ifnull(T8.code, T7.code) as constractCode,
              ifnull(T8.ctname, T7.ctname) as constractName,
              T3.name as `zrztName`,
              T0.cOrderNo
            from
              uorders.orders T0
              left join uorders.orders_character_define_1 T2 on T2.id = T0.orderDefineCharacter
              left join iuap_apdoc_basedoc.org_admin T3 on T3.id = T2.vcol1
              left join uorders.iuap_extend_gal3au79_orders_0 T1 on T1.id = T0.id
              left join sact.sact_salescontract T8 on T1.bigint_0 = T8.id
              left join sact.sact_salescontract T7 on T7.Code = T2.vcol3
            where
              T0.iShopID = -1
              and T0.verifystate = '2'
          ) voucher_order_Order
        where
          voucher_order_Order.constractId is not null
        GROUP by
          voucher_order_Order.constractId,
          voucher_order_Order.`zrztName`
      ) billData1
    where
      zrztName = '杨学彬大区'
    group by
      billData1.constractId
  ) a

奇葩的来了，这时候查出来数据是18条
为啥呢？
原因很简单！！！！！！！！

不带条件的时候，直接分组后再where，这时候根据constractId分组，根据实际数据分析，如果有不同的zrztName对应相同的constractId，正常来说，分组后会整合到一起，而且按照字符串顺序去整合，整合后的zrztName可能就不是where条件后面的这个，就导致觉得突然缺失数据的奇葩操作！

为啥放到里面。。数据又对了呢。。。因为先遍历出where条件后的数据，再去分组，不会出现上面情况。。所以百分百是对的。。。。。。。

没脑子。。所以正确的方式，应该在GroupBy 后面再加上where的条件，也就是zrztName，根据两个字段去分组，这时候就不会出现上述情况了！ 脑子抽抽 谨记！