求教一条SQL语句,我绕不过这个弯了

[iori2882]

麻烦大家帮我看看,这个sql怎么写,首先需要分页查询,这个sql我完成了,也好用,下一步是三表连接查询,把name也加入到查询结果,并且delivery字段和depositor字段分别对应一个姓名,这里我就绕不过来了!!!!

一:表结构



二:查询结果需求



三:分页加三表连接查询一个name会写,如下:

SELECT
  sys_box_items_delivery.items_delivery_depositor,
  sys_box_items_delivery.items_delivery_picker,
	sys_org_user.user_phone,
	sys_org_basic.`name` AS depositorName
FROM
	sys_box_items_delivery
INNER JOIN sys_org_user ON sys_box_items_delivery.items_delivery_depositor = sys_org_user.user_id
INNER JOIN sys_org_basic ON sys_org_user.user_pid = sys_org_basic.basic_id
JOIN (
	SELECT
		items_delivery_id
	FROM
		sys_box_items_delivery
	WHERE
		items_delivery_depositor = 'E946DE0B18D31CE797CBA8AA4CF1F3DE'
	OR 
	  items_delivery_picker = 'E946DE0B18D31CE797CBA8AA4CF1F3DE'
	ORDER BY
		items_delivery_status,
		items_delivery_id
	LIMIT 0,
	10
) b ON sys_box_items_delivery.items_delivery_id = b.items_delivery_id

但是,查询出来2个name,我就不会写了,是不是这个需求实现不了啊?

[AHUA1001]

是这个意思不

SELECT
sys_box_items_delivery.items_delivery_depositor,
sys_box_items_delivery.items_delivery_picker,
u1.user_phone user_phone1,
b1.`name` AS depositorName1,
u2.user_phone user_phone2,
b2.`name` AS depositorName2
FROM
sys_box_items_delivery
INNER JOIN sys_org_user u1
ON sys_box_items_delivery.items_delivery_depositor = u1.user_id
INNER JOIN sys_org_basic b1
ON u1.user_pid = b1.basic_id
INNER JOIN sys_org_user u2
ON sys_box_items_delivery.items_delivery_picker = u2.user_id
INNER JOIN sys_org_basic b2
ON u2.user_pid = b2.basic_id
JOIN
(SELECT
items_delivery_id
FROM
sys_box_items_delivery
WHERE items_delivery_depositor = 'E946DE0B18D31CE797CBA8AA4CF1F3DE'
OR items_delivery_picker = 'E946DE0B18D31CE797CBA8AA4CF1F3DE'
ORDER BY items_delivery_status,
items_delivery_id
LIMIT 0, 10) b
ON sys_box_items_delivery.items_delivery_id = b.items_delivery_id;

[iori2882]

引用 1 楼 AHUA1001 的回复:

是这个意思不

是的 是这个意思 谢谢你,结贴给分了