查询语句的连接
如题：现有A/B两表做连接

假设zone表有字段 int 型 developerID（值为0967），department表有String型 字段 dept_id （值为k0967）

现在查询语句中判断 假设： 0967 = k0967 ，就是如何加 那个K

java语句中有 + ，数据库里面可以 实现么？？



SELECT TOP 4 pi.companyName, z.ID, z.name AS zname, z.broough, z.street,
z.buildCount1, z.buildCount2, z.houseCount1, z.houseCount2, z.totalArea1,
z.totalArea2, z.occupyArea, z.businessArea, z.publicArea, z.rcommitteID,
z.developerID, dm.dept_name AS pname, z.completeTime, z.bankID,
di.name AS dname, z.address, z.publicAddress, z.businessAddress, z.Remark,
z.spare1, di2.name AS mname, z.spare2, z.spare3, z.spare4, z.spare5, z.bankName,
z.bankAccountNum
FROM [zone] z LEFT OUTER JOIN
property_info pi ON z.spare2 = pi.ID LEFT OUTER JOIN
disc_info di2 ON z.spare1 = di2.id LEFT OUTER JOIN
disc_info di ON z.bankID = di.id LEFT OUTER JOIN
department dm ON dm.dept_id = 'k ' + z.developerID AND
dm.parentdept = 'KFS'
WHERE (1 = 1) AND (z.ID NOT IN
(SELECT TOP 0 z.id
FROM zone z LEFT JOIN
property_info pi ON (z.spare2 = pi.ID) LEFT JOIN
disc_info di2 ON (z.spare1 = di2.ID) LEFT JOIN
disc_info di ON (z.bankID = di.ID) LEFT JOIN
department dm ON ('k' + z.developerID = dm.dept_id AND parentdept = 'KFS')
WHERE 1 = 1
ORDER BY z.id))
ORDER BY z.ID

[解决办法]

SQL code

SELECT TOP 4 pi.companyName, z.ID, z.name AS zname, z.broough, z.street,    z.buildCount1, z.buildCount2, z.houseCount1, z.houseCount2, z.totalArea1,    z.totalArea2, z.occupyArea, z.businessArea, z.publicArea, z.rcommitteID,    z.developerID, dm.dept_name AS pname, z.completeTime, z.bankID,    di.name AS dname, z.address, z.publicAddress, z.businessAddress, z.Remark,    z.spare1, di2.name AS mname, z.spare2, z.spare3, z.spare4, z.spare5, z.bankName,    z.bankAccountNumFROM [zone] z LEFT OUTER JOIN  property_info pi ON z.spare2 = pi.ID LEFT OUTER JOIN  disc_info di2 ON z.spare1 = di2.id LEFT OUTER JOIN  disc_info di ON z.bankID = di.id LEFT OUTER JOIN  department dm ON convert(int,replace(dm.dept_id,'k','')) = convert(int,z.developerID) /*0967应该不是int*/   AND  dm.parentdept = 'KFS'WHERE (1 = 1) AND (z.ID NOT IN  (SELECT TOP 0 z.id  FROM zone z LEFT JOIN  property_info pi ON (z.spare2 = pi.ID) LEFT JOIN  disc_info di2 ON (z.spare1 = di2.ID) LEFT JOIN  disc_info di ON (z.bankID = di.ID) LEFT JOIN  department dm ON (convert(int,z.developerID) = convert(int,replace(dm.dept_id,'k','')) AND parentdept = 'KFS')  WHERE 1 = 1  ORDER BY z.id))ORDER BY z.ID