同一时间同一地点同一车牌查询不同违章行为

分手后的思念是犯贱 2023-06-27 12:57 14阅读 0赞

表结构：

<table> 
 <tbody> 
 <tr> 
 <td>Create Table</td> 
 </tr> 
 <tr> 
 <td> CREATE&nbsp;TABLE&nbsp;`123_csv`&nbsp;( &nbsp;&nbsp;`id`&nbsp;int(10)&nbsp;unsigned&nbsp;NOT&nbsp;NULL&nbsp;AUTO_INCREMENT, &nbsp;&nbsp;`md`&nbsp;char(32)&nbsp;DEFAULT&nbsp;'', &nbsp;&nbsp;`time`&nbsp;timestamp&nbsp;NULL&nbsp;DEFAULT&nbsp;NULL, &nbsp;&nbsp;`fine`&nbsp;int(10)&nbsp;DEFAULT&nbsp;NULL, &nbsp;&nbsp;`carNo`&nbsp;varchar(255)&nbsp;DEFAULT&nbsp;NULL, &nbsp;&nbsp;`cityName`&nbsp;varchar(255)&nbsp;DEFAULT&nbsp;NULL, &nbsp;&nbsp;`recordId`&nbsp;int(10)&nbsp;DEFAULT&nbsp;NULL, &nbsp;&nbsp;`zhinajin`&nbsp;int(10)&nbsp;DEFAULT&nbsp;NULL, &nbsp;&nbsp;`address`&nbsp;varchar(255)&nbsp;DEFAULT&nbsp;NULL, &nbsp;&nbsp;`code`&nbsp;int(10)&nbsp;DEFAULT&nbsp;NULL, &nbsp;&nbsp;`deductPoint`&nbsp;int(10)&nbsp;DEFAULT&nbsp;NULL, &nbsp;&nbsp;`deductPointType`&nbsp;int(10)&nbsp;DEFAULT&nbsp;NULL, &nbsp;&nbsp;`behavior`&nbsp;varchar(255)&nbsp;DEFAULT&nbsp;NULL, &nbsp;&nbsp;`canHandle`&nbsp;varchar(255)&nbsp;DEFAULT&nbsp;NULL, &nbsp;&nbsp;`serviceFee`&nbsp;int(10)&nbsp;DEFAULT&nbsp;NULL, &nbsp;&nbsp;`note`&nbsp;varchar(255)&nbsp;DEFAULT&nbsp;NULL, &nbsp;&nbsp;PRIMARY&nbsp;KEY&nbsp;(`id`), &nbsp;&nbsp;KEY&nbsp;`time`&nbsp;(`time`,`carNo`,`address`,`behavior`), &nbsp;&nbsp;KEY&nbsp;`md`&nbsp;(`md`), &nbsp;&nbsp;KEY&nbsp;`deductPointType`&nbsp;(`behavior`) )&nbsp;ENGINE=InnoDB&nbsp;AUTO_INCREMENT=396841&nbsp;DEFAULT&nbsp;CHARSET=utf8</td> 
 </tr> 
 </tbody> 
</table>

1.将车牌时间地点md5处理

UPDATE \`123\_csv\` SET md=MD5(CONCAT(carNo,TIME,address))

2.内联查询

SELECT a.md,a.\`behavior\` FROM \`123\_csv\` a LEFT JOIN 123\_csv b ON a.\`md\`=b.\`md\` WHERE a.\`behavior\`!=b.\`behavior\` GROUP BY a.\`md\`  
3.结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbGtfamF2YQ_size_16_color_FFFFFF_t_70][]

4.根据md查询的结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbGtfamF2YQ_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbGtfamF2YQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200107102413259.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbGtfamF2YQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbGtfamF2YQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200107102546364.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbGtfamF2YQ==,size_16,color_FFFFFF,t_70