Mysql 查去重查表里其他字段 ,用 groupby

野性酷女 2022-07-15 01:50 210阅读 0赞

\-- SELECT id, material\_id, wares\_name,supplier\_id,stat FROM t\_pro\_wares WHERE id IN(SELECT MAX(id) FROM t\_pro\_wares a GROUP BY material\_id) AND  supplier\_id = '02963029-4dd0-4327-b87f-4ff7d997a2d7' AND stat =1 ORDER BY create\_time DESC;

\-- SELECT \* FROM t\_pro\_wares WHERE supplier\_id = '02963029-4dd0-4327-b87f-4ff7d997a2d7' AND stat =1;

\-- SELECT MAX(id) FROM t\_pro\_wares GROUP BY material\_id;  
\-- SELECT id, material\_id, wares\_name,supplier\_id,stat FROM t\_pro\_wares WHERE id IN(SELECT MAX(id) FROM t\_pro\_wares a GROUP BY material\_id) AND  supplier\_id = '02963029-4dd0-4327-b87f-4ff7d997a2d7' AND stat =1 ORDER BY create\_time DESC;  
\-- SELECT DISTINCT(material\_id) FROM t\_pro\_wares a WHERE a.supplier\_id = '02963029-4dd0-4327-b87f-4ff7d997a2d7' AND stat =1;  
\-- SELECT DISTINCT(wares\_name) FROM t\_pro\_wares a WHERE a.supplier\_id = '02963029-4dd0-4327-b87f-4ff7d997a2d7' AND stat =1;  
  
\-- SELECT a.id,a.action\_date AS actionDate,a.ware\_batch\_no AS wareBatchNo,a.haul\_status AS haulStatus,a.start\_time AS startTime,a.end\_time AS endTime,b.name,b.supplier\_id AS supplierId,b.supplier\_name AS supplierName,b.wares\_id AS waresId,b.supply\_id AS supplyId,b.supply\_name AS supply\_name FROM t\_pro\_ledger\_master a  
\-- LEFT JOIN t\_pro\_ledger b ON a.id=b.master\_id WHERE a.stat = 1 AND b.stat = 1 AND a.action\_date between '2016-10-01 00:00:00.0' AND '2016-10-31 23:59:59.0' AND b.wares\_id = '44173249-bcb2-453a-83e0-5d5c20ecf4fb' AND b.supply\_id = 'a46c420e-b485-4a7a-a75d-088d61dbfe73' ORDER BY a.create\_time DESC ;  
  
\-- SELECT b.action\_date,a.\`name\`,a.supplier\_id,a.\`supply\_id\`,a.wares\_id, b.receiver\_name,b.ware\_batch\_no,c.material\_id FROM t\_pro\_ledger as a  
\-- LEFT JOIN t\_pro\_ledger\_master as b on a.master\_id = b.id  
\-- LEFT JOIN t\_pro\_wares as c ON c.id = a.wares\_id  
\-- WHERE a.supply\_id='a46c420e-b485-4a7a-a75d-088d61dbfe73' and a.stat=1 AND b.stat=1 AND c.stat=1 and c.material\_id= 8 and b.action\_date BETWEEN '2016-10-01' and '2016-10-31' GROUP BY b.action\_date;  
  
\-- SELECT b.id,b.wares\_name AS waresName,b.\`supplier\_id\` AS supplierId,b.\`wares\_supplier\_id\` AS waresSupplierId,b.\`wares\_supplier\` AS waresSupplier,e.contacts,e.\`contact\_way\` AS contactWay,e.supplier\_name AS supplierName,d.action\_date AS actionDate  
\-- FROM t\_pro\_supplier\_receiver a  
\-- LEFT JOIN t\_pro\_supplier e ON a.supplier\_id = e.id  
\-- LEFT JOIN t\_pro\_wares b ON b.supplier\_id = a.id  
\-- LEFT JOIN t\_pro\_ledger AS c ON b.wares\_supplier\_id = c.supply\_id  
\-- LEFT JOIN t\_pro\_ledger\_master AS d ON c.master\_id= d.id  
\-- WHERE e.\`supplier\_type\`= 2 AND e.\`stat\` =1 AND b.\`stat\` =1 AND c.\`stat\` =1 AND d.\`stat\` =1 AND b.wares\_supplier\_id = ? AND b.id = ? AND DATE\_FORMAT(d.action\_date, '%Y-%m-%d') = ? ORDER BY a.create\_time DESC  
  
\-- SELECT a.action\_date AS actionDate,b.supply\_id as supplyId, b.supply\_name as supplyName,b.supplier\_id AS supplierId, c.contacts,c.\`contact\_way\` AS contactWay,c.supplier\_name AS supplierName    
\-- FROM t\_pro\_ledger\_master a  
\-- LEFT JOIN  t\_pro\_ledger AS b ON a.id= b.master\_id  
\-- LEFT JOIN  t\_pro\_supplier AS c ON a.supplier\_id = c.id  
\-- where a.stat = 1 and b.stat = 1 AND c.stat = 1 AND a.\`action\_date\`= and b.\`wares\_id\`=  and b.\`supplier\_id\`= and c.\`supplier\_type\`= 1  
SELECT MAX(id),a.\* FROM  t\_pro\_ledger a GROUP BY supplier\_id;  
  
SELECT a.id,a.\`ware\_batch\_no\`,b.\`wares\_id\`, a.action\_date AS actionDate,b.supply\_id AS supplyId, b.supply\_name AS supplyName,b.supplier\_id AS supplierId, c.contacts,c.\`contact\_way\` AS contactWay,c.supplier\_name AS supplierName  
FROM t\_pro\_ledger\_master a LEFT JOIN t\_pro\_ledger AS b ON a.id= b.master\_id LEFT JOIN t\_pro\_supplier AS c ON b.supplier\_id = c.id

WHERE a.stat = 1 AND b.stat = 1 AND c.stat = 1 AND c.\`supplier\_type\`= 1 AND b.supply\_id = 'a46c420e-b485-4a7a-a75d-088d61dbfe73' AND b.wares\_id = '8775717a-0a5d-4d82-8bad-54f89f70c374' AND DATE\_FORMAT(a.action\_date, '%Y-%m-%d') = '2016-10-28' ORDER BY a.create\_time DESC ;