distinct left mysql,mysql 语句group by，distinct， FIND_IN_SET， left join

标签：

group by 和 distinct的区别

FIND_IN_SET语法

left join

group by 和 distinct的区别：

共同点： 去重

group by

select cProcedureID, updateTime, Num  from i_plc_procedure_record where updateTime > "2018-09-04 00:00:00" group by cProcedureID

distinct：

distinct 字段需放在字段最前边， 要不然会报错， 去重

错误方法：

select  cProcedureID,distinct updateTime, Num  from i_plc_procedure_record where updateTime > "2018-09-04 00:00:00" group by cProcedureID

select  cProcedureID,distinct updateTime, Num  from i_plc_procedure_record where updateTime > "2018-09-04 00:00:00" group by cProcedureID

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct updateTime, Num  from i_plc_procedure_record where updateTime > "2018-0' at line 3

正确使用方法：

select distinct cProcedureID, updateTime, Num  from i_plc_procedure_record where updateTime > "2018-09-04 00:00:00" group by cProcedureID

不同点： group by 的功能比distinct更加强大

group by 聚合函数,

GROUP BY 语法

SELECT column_name, function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name;

语句使用：

select cProcedureID, max(updateTime) as updateTime, SUM(Num*unitPrice) as Money, Num  from i_plc_procedure_record where updateTime > "2018-09-04 00:00:00" group by cProcedureID

FIND_IN_SET语法:

mysql 语法：

FIND_IN_SET(str,strlist)

str 要查询的字符串

strlist 字段名 参数以”,”分隔 如 (1,2,6,8)

查询字段(strlist)中包含(str)的结果，返回结果为null或记录

FIND_IN_SET和IN 之间的关联

相同点

SELECT accessWebID FROM i_plc_authority where FIND_IN_SET(id,(select authorityID from i_plc_users where id =UserID))

SELECT accessWebID FROM i_plc_authority where id in (select authorityID from i_plc_users where id =UserID)

区别点

select * from i_plc_product where FIND_IN_SET("1", cInvName) and operationFlag=9  正常运行

select * from i_plc_product where  "1" in cInvName and operationFlag=9  报错

[SQL] -- select * from i_plc_sfc_operation where operationFlag=9 order by opCode + 0

select * from i_plc_product where  "1" in cInvName and operationFlag=9

[Err] 1064- You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cInvName and operationFlag=9' at line 2

left join：

left join 和 where的区别

select w.OpSeq,w.WcCode,w.id,c.WcName from technic1workcenter as w left join (select WcName,WcCode from i_plc_workcenter where operationFlag=9) c on c.WcCode=w.WcCode where w.operationFlag=9

select w.OpSeq,w.WcCode,w.id,c.WcName from technic1workcenter as w left join i_plc_workcenter c on c.WcCode=w.WcCode where w.operationFlag=9 and c.operationFlag=9

(一)查询的数据为什么比(二)多？  是因为technic1workcenter的数据没有与i_plc_workcenter关联， 但是在(二)中存在 where c.operationFlag=9， 导致直接过滤掉了

select w.OpSeq,w.WcCode,w.id,c.WcName from technic1workcenter w, i_plc_workcenter c where c.WcCode=w.WcCode and w.operationFlag=9 and c.operationFlag=9

where类似于inner join。

标签：

来源： https://blog.csdn.net/u012447842/article/details/82628914