假设一间工厂装配设备的数据库,数据表是两个,设计如下表一:TB_Device (设备表)
字段:
DeviceID (设备编号)
DeviceName (设备名称)
WorkerID (工人编号)表二:TB_Worker (工人表)
字段:
WorkerID (工人编号)
WorkerName (工人姓名)现在如果要查询所有设备的并且显示谁负责装配可以这样写
SELECT
DeviceID
,DeviceName
,TB_Worker.WorkerName
FROM TB_Device LEFT JOIN TB_Worker
ON TB_Device.WorkerID=TB_Worker.WorkerID
ORDER BY DeviceID ASC但如果设备的装配是分2步的,分别由不同的工人装配,在设备表加多一个WorkerID2的字段,即DeviceID (设备编号)
DeviceName (设备名称)
WorkerID (工人编号)
WorkerID2 (工人编号)这时用join的方式怎么实现??本人初学,只想到用嵌套select字句的方式:SELECT
DeviceID
,DeviceName
,(SELECT WorkerName FROM TB_Worker WHERE TB_Worker.WorkerID=TB_Device.WorkerID)
,(SELECT WorkerName FROM TB_Worker WHERE TB_Worker.WorkerID=TB_Device.WorkerID2)
FROM TB_Device
ORDER BY DeviceID ASC但如果装配要分十几步或更多的时候,嵌套的select语句就要写很多个了,所以觉得这种方式不太可行新手求解惑!!
字段:
DeviceID (设备编号)
DeviceName (设备名称)
WorkerID (工人编号)表二:TB_Worker (工人表)
字段:
WorkerID (工人编号)
WorkerName (工人姓名)现在如果要查询所有设备的并且显示谁负责装配可以这样写
SELECT
DeviceID
,DeviceName
,TB_Worker.WorkerName
FROM TB_Device LEFT JOIN TB_Worker
ON TB_Device.WorkerID=TB_Worker.WorkerID
ORDER BY DeviceID ASC但如果设备的装配是分2步的,分别由不同的工人装配,在设备表加多一个WorkerID2的字段,即DeviceID (设备编号)
DeviceName (设备名称)
WorkerID (工人编号)
WorkerID2 (工人编号)这时用join的方式怎么实现??本人初学,只想到用嵌套select字句的方式:SELECT
DeviceID
,DeviceName
,(SELECT WorkerName FROM TB_Worker WHERE TB_Worker.WorkerID=TB_Device.WorkerID)
,(SELECT WorkerName FROM TB_Worker WHERE TB_Worker.WorkerID=TB_Device.WorkerID2)
FROM TB_Device
ORDER BY DeviceID ASC但如果装配要分十几步或更多的时候,嵌套的select语句就要写很多个了,所以觉得这种方式不太可行新手求解惑!!
select DeviceID,DeviceName,WorkerName,WorkerName2 from(
select DeviceID,DeviceName,WorkerName,WorkerID2 from TB_Device a
left join TB_Worker b on a.WorkerID=b.WorkerID)t
left join TB_Worker s on t.WorkerID2=s.WorkerID
意思就是TB A join TB_Worker B ON A.XX=B.XX JOIN TB_Worker C ON A.XX=C.XX
这样。
DeviceID
,DeviceName
,tbwk1.WorkerName
,tbwk2.WorkerName
FROM TB_Device
LEFT JOIN TB_Worker as tbwk1
ON TB_Device.WorkerID=tbwk1.WorkerID
LEFT JOIN TB_Worker as tbwk2
ON TB_Device.WorkerID=tbwk2.WorkerID
ORDER BY DeviceID ASC
其实,没有别要用左外连接的,直接用内连接也可以SELECT
t1.DeviceID
,t1.DeviceName
,t2.WorkerName
,t3.WorkerName
FROM TB_Device AS t1
INNER JOIN TB_Worker as t2 ON t1.WorkerID=t2.WorkerID
INNER JOIN TB_Worker as t3
ON t1.WorkerID=t3.WorkerID
ORDER BY t1.DeviceID ASC
放到前台代码里实现,方便又灵活。WorkerID 改成Varchar,存工人ID用“,”隔开,在代码层写一个方法,根据工人ID字符串生成工人名字的字符串,就可以了。