SELECT DISTINCT table_code FROM dq_task AS t1, dq_subject AS t2 WHERE t2.project_Id =#{projectId} AND t2.is_deleted=FALSE这种写法和多表连接join有什么不同

这种写法是使用了SQL中的隐式内连接，相当于使用了INNER JOIN语句。具体来说，这个查询语句中dq_task表和dq_subject表会根据t2.project_Id =#{projectId}作为连接条件进行连接，并且只返回dq_task表中唯一的不重复的table_code值。

而多表连接join语句则需要显式地指定连接条件，如下所示：

SELECT DISTINCT table_code
FROM dq_task
JOIN dq_subject ON dq_task.subject_id = dq_subject.subject_id
WHERE dq_subject.project_Id =#{projectId} AND dq_subject.is_deleted = FALSE

这条语句与上面那条语句的结果相同，但是使用了INNER JOIN语句来显式地指定连接条件。需要注意的是，JOIN语句还有其他类型，如LEFT JOIN、RIGHT JOIN和FULL OUTER JOIN等，可以根据实际需要进行选择。

总的来说，这两种写法的本质是相同的，都是用来连接多个表并从中查询数据。不同的是，隐式内连接使用起来更简洁，但是在连接复杂表结构时可能会出现问题。因此，在实际开发中，应该根据实际情况选择适合的连接方式。

相关问题

TRUNCATE TABLE FDM.fdm_jg_fydkjl; INSERT INTO FDM.fdm_jg_fydkjl ( gh, -- 工号 xm, -- 姓名 dwh, -- 单位号 dwmc, -- 单位名称 fbdkrwsj, -- 发布打卡任务时间 wcdksj, -- 完成打卡时间 dkdd, -- 打卡地点 sfqj, -- 是否请假 sfyxdk, -- 是否有效打卡 xb, -- 性别 lxdh, -- 联系电话 hsqksm, -- 核算情况说明 sfzjh, -- 身份证件号 jzdz -- 居住地址 ) SELECT t2.gh, --工号 t11.xm, --姓名 t11.dwh, -- 单位号 t11.dwmc, -- 单位名称 CAST(NULL AS TIMESTAMP) AS fbdkrwsj, -- 发布打卡任务时间 t2.dksj AS wcdksj, -- 完成打卡时间 NULL AS dkdd, -- 打卡地点 NULL AS sfqj, -- 是否请假 '1' AS sfyxdk, -- 是否有效打卡 CAST(t4.xb AS VARCHAR), --性别 CAST(t7.lxdh AS VARCHAR), -- 联系电话 CAST(t8.hsqksm AS VARCHAR) AS hsqksm, -- 核算情况说明 CAST(t9.sfz AS VARCHAR) AS sfzjh, --身份证件号 CAST(t10.jzdz AS VARCHAR) AS jzdz --居住地址 FROM ( SELECT DISTINCT(t1.user_id) AS gh, t1.answer AS bjmc, t1.answer_time AS dksj FROM ODS.ODS_YDXY_LYYD3_B_GRADUATE_ANSWER t1 WHERE t1.type = 'bjmc' )t2 LEFT JOIN ( SELECT DISTINCT(t1.user_id) AS gh, t1.answer AS xm, t1.answer_time AS dksj FROM ODS.ODS_YDXY_LYYD3_B_GRADUATE_ANSWER t1 WHERE t1.type = 'username' ) t3 ON t2.gh = t3.gh AND t2.dksj = t3.dksj LEFT JOIN ( SELECT DISTINCT(t1.user_id) AS gh, t1.answer AS xb, t1.answer_time AS dksj FROM ODS.ODS_YDXY_LYYD3_B_GRADUATE_ANSWER t1 WHERE t1.type = 'xb' ) t4 ON t2.gh = t4.gh AND t2.dksj = t4.dksj LEFT JOIN ( SELECT DISTINCT(t1.user_id) AS gh, t1.answer AS bjmc, t1.answer_time AS dksj FROM ODS.ODS_YDXY_LYYD3_B_GRADUATE_ANSWER t1 WHERE t1.type = 'bjmc' ) t5 ON t2.gh = t5.gh AND t2.dksj = t5.dksj LEFT JOIN ( SELECT DISTINCT(t1.user_id) AS gh, t1.answer AS bmmc, t1.answer_time AS dksj FROM ODS.ODS_YDXY_LYYD3_B_GRADUATE_ANSWER t1 WHERE t1.type = 'bmmc' ) t6 ON t2.gh = t6.gh AND t2.dksj = t6.dksj LEFT JOIN ( SELECT DISTINCT(t1.user_id) AS gh, CAST(t1.answer AS VARCHAR) AS lxdh, t1.answer_time AS dksj FROM ODS.ODS_YDXY_LYYD3_B_GRADUATE_ANSWER t1 WHERE t1.type = 'bdsjh' ) t7 ON t2.gh = t7.gh AND t2.dksj = t7.dksj LEFT JOIN ( SELECT DISTINCT(t1.user_id) AS gh, t1.answer AS hsqksm, t1.answer_time AS dksj FROM ODS.ODS_YDXY_LYYD3_B_GRADUATE_ANSWER t1 WHERE t1.type = 'radio' ) t8 ON t2.gh = t8.gh AND t2.dksj = t8.dksj LEFT JOIN ( SELECT DISTINCT(t1.user_id) AS gh, t1.answer AS sfz, t1.answer_time AS dksj FROM ODS.ODS_YDXY_LYYD3_B_GRADUATE_ANSWER t1 WHERE t1.type = 'sfz' ) t9 ON t2.gh = t9.gh AND t2.dksj = t9.dksj LEFT JOIN ( SELECT t1.user_id AS gh, t1.answer AS jzdz, t1.answer_time AS dksj FROM ODS.ODS_YDXY_LYYD3_B_GRADUATE_ANSWER t1 WHERE t1.type = 'address' ) t10 ON t2.gh = t10.gh AND t2.dksj = t10.dksj LEFT JOIN FDM.fdm_jg_jbxx t11 ON t2.gh = t11.gh WHERE t2.gh = t11.gh

很抱歉，我不太明白您的问题。我只能告诉您，FDM.fdm_jg_fydkjl是一个用于存储用户工号、姓名、单位号、单位名称、发布打卡任务时间、完成打卡时间、打卡地点、是否请假、是否有效打卡、性别、联系电话、核算情况说明、身份证件号和居住地址等信息的表。

帮我优化如下sql：SELECT T1.quyu, T2.lastNum, T3.jdNum, T4.xcNum FROM (SELECT STREET AS quyu FROM STREET_YXW WHERE 1=1 GROUP BY STREET ) T1 LEFT JOIN ( SELECT T2.STREET_NAME AS quyu,COUNT(*) AS lastNum FROM V_FIRESAFETYREGISTER_DYC T1 LEFT JOIN V_HSE_COMB_BUILDING_INFO_PA T2 ON LEFT(T1.HOUSEID,19) = T2.BUILDING_CODE WHERE T1.LASTLOGOUTTIME IS NOT NULL and date_format(T1.LASTLOGOUTTIME,'%Y') = ? GROUP BY T2.STREET_NAME ) T2 ON T1.quyu = T2.quyu LEFT JOIN ( SELECT T3.STREET_NAME AS quyu,COUNT(DISTINCT T1.FIREID) AS jdNum FROM V_SX_FIRESAFETYPLAN T1 LEFT JOIN V_FIRESAFETYREGISTER_DYC T2 ON T1.FIREID = T2.ID LEFT JOIN V_HSE_COMB_BUILDING_INFO_PA T3 ON LEFT(T2.HOUSEID,19) = T3.BUILDING_CODE WHERE T1.DATE_NEW IS NOT NULL and date_format(T1.DATE_NEW,'%Y') = ? GROUP BY T3.STREET_NAME ) T3 ON T1.quyu = T3.quyu LEFT JOIN ( SELECT T3.STREET_NAME AS quyu,COUNT(*) as xcNum FROM V_SX_FIRESAFETYTRAINING T1 LEFT JOIN V_FIRESAFETYREGISTER_DYC T2 ON T1.FIREID = T2.ID LEFT JOIN V_HSE_COMB_BUILDING_INFO_PA T3 ON LEFT(T2.HOUSEID,19) = T3.BUILDING_CODE WHERE T1.TRAININGTIME IS NOT NULL and date_format(T1.TRAININGTIME,'%Y') = ? GROUP BY T3.STREET_NAME ) T4 ON T1.quyu = T4.quyu ORDER BY T2.lastNum limit ?,?

可以尝试以下优化：

1. 对于子查询中的表，添加合适的索引以提高查询效率；

2. 可以考虑将子查询改为临时表，避免多次重复查询；

3. 在需要排序的字段上添加索引；

4. 尽量避免在 JOIN 条件中使用函数，可以将日期比较条件提取到 WHERE 子句中。

优化后的 SQL 如下：

CREATE TEMPORARY TABLE tmp_street AS
SELECT STREET AS quyu FROM STREET_YXW WHERE 1=1 GROUP BY STREET;

CREATE TEMPORARY TABLE tmp_lastNum AS
SELECT T2.STREET_NAME AS quyu, COUNT(*) AS lastNum
FROM V_FIRESAFETYREGISTER_DYC T1
LEFT JOIN V_HSE_COMB_BUILDING_INFO_PA T2 ON LEFT(T1.HOUSEID, 19) = T2.BUILDING_CODE
WHERE T1.LASTLOGOUTTIME IS NOT NULL AND date_format(T1.LASTLOGOUTTIME,'%Y') = ?
GROUP BY T2.STREET_NAME;

CREATE TEMPORARY TABLE tmp_jdNum AS
SELECT T3.STREET_NAME AS quyu, COUNT(DISTINCT T1.FIREID) AS jdNum
FROM V_SX_FIRESAFETYPLAN T1
LEFT JOIN V_FIRESAFETYREGISTER_DYC T2 ON T1.FIREID = T2.ID
LEFT JOIN V_HSE_COMB_BUILDING_INFO_PA T3 ON LEFT(T2.HOUSEID, 19) = T3.BUILDING_CODE
WHERE T1.DATE_NEW IS NOT NULL AND date_format(T1.DATE_NEW,'%Y') = ?
GROUP BY T3.STREET_NAME;

CREATE TEMPORARY TABLE tmp_xcNum AS
SELECT T3.STREET_NAME AS quyu, COUNT(*) AS xcNum
FROM V_SX_FIRESAFETYTRAINING T1
LEFT JOIN V_FIRESAFETYREGISTER_DYC T2 ON T1.FIREID = T2.ID
LEFT JOIN V_HSE_COMB_BUILDING_INFO_PA T3 ON LEFT(T2.HOUSEID, 19) = T3.BUILDING_CODE
WHERE T1.TRAININGTIME IS NOT NULL AND date_format(T1.TRAININGTIME,'%Y') = ?
GROUP BY T3.STREET_NAME;

SELECT T1.quyu, T2.lastNum, T3.jdNum, T4.xcNum
FROM tmp_street T1
LEFT JOIN tmp_lastNum T2 ON T1.quyu = T2.quyu
LEFT JOIN tmp_jdNum T3 ON T1.quyu = T3.quyu
LEFT JOIN tmp_xcNum T4 ON T1.quyu = T4.quyu
ORDER BY T2.lastNum
LIMIT ?,?;

请注意，这只是一种可能的优化方案，具体的优化方法还要根据数据库的实际情况进行调整。