1.查询总的数据 1.1 SQL如下
SELECT tpc.ORDER_ID,tpc.INVAILD_FLAG FROM t_payment_code tpc WHERE tpc.ORDER_ID = '82487056720011566'
#SELECT tpc.ORDER_ID,tpc.INVAILD_FLAG FROM t_payment_code tpc WHERE tpc.ORDER_ID = '82487056720011566' GROUP BY tpc.ORDER_ID
#SELECT DISTINCT tpc.ORDER_ID,tpc.INVAILD_FLAG FROM t_payment_code tpc WHERE tpc.ORDER_ID = '82487056720011566'
1.2 查询结果如下 2.GROUP BY的方式去重,条件是ORDER_ID 2.1SQL如下
#SELECT tpc.ORDER_ID,tpc.INVAILD_FLAG FROM t_payment_code tpc WHERE tpc.ORDER_ID = '82487056720011566'
#GROUP BY的方式去重,条件是ORDER_ID
SELECT tpc.ORDER_ID,tpc.INVAILD_FLAG FROM t_payment_code tpc WHERE tpc.ORDER_ID = '82487056720011566' GROUP BY tpc.ORDER_ID
#SELECT DISTINCT tpc.ORDER_ID,tpc.INVAILD_FLAG FROM t_payment_code tpc WHERE tpc.ORDER_ID = '82487056720011566'
2.2 查询结果如下 很明显,invalid_flag=0的数据被去除掉了 3.DISTINCT方式去重 3.1 SQL如下
#SELECT tpc.ORDER_ID,tpc.INVAILD_FLAG FROM t_payment_code tpc WHERE tpc.ORDER_ID = '82487056720011566'
#GROUP BY的方式去重,条件是ORDER_ID
#SELECT tpc.ORDER_ID,tpc.INVAILD_FLAG FROM t_payment_code tpc WHERE tpc.ORDER_ID = '82487056720011566' GROUP BY tpc.ORDER_ID
#DISTINCT方式去重
SELECT DISTINCT tpc.ORDER_ID,tpc.INVAILD_FLAG FROM t_payment_code tpc WHERE tpc.ORDER_ID = '82487056720011566'
3.2 查询结果如下
4.总结 方法2与方法3去重很明显有区别 方法2采用的是单个条件ORDER_ID去重,而方法3采用的是ORDER_ID与INVAILD_FLAG 以下两种方式去重结果一样
#GROUP BY的方式去重,条件是ORDER_ID、INVAILD_FLAG
SELECT tpc.ORDER_ID,tpc.INVAILD_FLAG FROM t_payment_code tpc WHERE tpc.ORDER_ID = '82487056720011566' GROUP BY tpc.ORDER_ID,tpc.INVAILD_FLAG
#DISTINCT方式去重,条件是其后面所有的字段作为一个整体再去重,而不是以靠近它的第一个字段作为去重的条件
#SELECT DISTINCT tpc.ORDER_ID,tpc.INVAILD_FLAG FROM t_payment_code tpc WHERE tpc.ORDER_ID = '82487056720011566'