Score
0
Watch 1.1K Star 4.4K Fork 2.2K

季圣华 / 华夏ERPJavaGPL-3.0

采购订单的sql有问题

Open
林叮当  Opened this issue

正常的构造应该下面这个逻辑, 应该去掉distinct

SELECT 
	s.supplier supplier_name, 
	dh.number as check_number,
	(
	SELECT	GROUP_CONCAT(concat( jsh_material.`Name`, ' ', jsh_material.Model )) AS mName 
    FROM jsh_depot_item
	 INNER JOIN jsh_material ON jsh_material.tenant_id = 63 
	                        AND jsh_depot_item.material_id = jsh_material.Id 
													AND ifnull( jsh_material.delete_Flag, '0' ) != '1' 
	WHERE	jsh_depot_item.tenant_id = 63 
		AND jsh_depot_item.header_id = dh.id 
		AND ifnull( jsh_depot_item.delete_flag, '0' ) != '1'
	) as name,
	dh.create_time,
	u.username userName, 
	dh.total_price,
	a.name AccountName
FROM jsh_depot_head dh
	LEFT JOIN jsh_supplier s ON s.tenant_id = 63 AND dh.organ_id = s.id AND ifnull( s.delete_Flag, '0' ) != '1'
	LEFT JOIN jsh_user u ON u.tenant_id = 63 	AND dh.creator = u.id AND ifnull( u.STATUS, '0' ) = '0'
	LEFT JOIN jsh_account a ON a.tenant_id = 63 AND dh.account_id = a.id 	AND ifnull( a.delete_Flag, '0' ) != '1'
-- 	LEFT JOIN jsh_depot_item di ON di.tenant_id = 63 	AND dh.id = di.header_id 	AND ifnull( di.delete_flag, '0' ) != '1'
-- 	LEFT JOIN jsh_material m ON m.tenant_id = 63 	AND di.material_id = m.id AND ifnull( m.delete_flag, '0' ) != '1' 
WHERE	dh.tenant_id = 63 
	AND dh.type = '其它' 
	AND dh.sub_type = '采购订单' 
	AND ifnull( dh.delete_flag, '0' ) != '1'```
total 1 participants

Comments (0)

Sign in to comment

Assignees
Labels
Not set
Projects
Milestones
Branches
Planed to start
Not set
Planed to end
Not set
Top level
Priority