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

-- 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 ;








评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值