把下面sql语句分成两个sql查询: select t1.id, t1.md, t2.mm from t1 left join t2 on t2.child = t1.child where t2.nn like 'hh';
时间: 2024-05-20 14:17:41 浏览: 7
第一个查询:
SELECT t1.id, t1.md
FROM t1
WHERE t1.child NOT IN (SELECT t2.child FROM t2 WHERE t2.nn NOT LIKE 'hh');
第二个查询:
SELECT t1.id, t2.mm
FROM t1
LEFT JOIN t2 ON t2.child = t1.child
WHERE t2.nn LIKE 'hh';
相关问题
代码如何优化 :select t1.* from t1 where NOT EXISTS ( select 1 from t2 where t1.ip=t2.id );
可以考虑以下优化措施:
1. 添加索引:对t1表中的ip列和t2表中的id列添加索引,可以大大提高查询效率。
2. 改写成LEFT JOIN语句:将NOT EXISTS改写成LEFT JOIN,这样可以避免子查询的多次执行,从而提高查询效率。
优化后的SQL语句如下:
SELECT t1.*
FROM t1
LEFT JOIN t2 ON t1.ip = t2.id
WHERE t2.id IS NULL;
将SQL语句转换成多表联查形式的SQL:SELECT t1.*, ( SELECT count( 1 ) FROM t_spjk_jkgcjcxx t2 WHERE TGSH >= '2023-07-13 00:00:00' AND t2.JKDSBBH = t1.JKDSBBH AND t2.tenant_id = 1667005694987583489 ) AS countNum FROM t_spjk_jkdwxx t1 WHERE t1.tenant_id = 1667005694987583489
你可以将这个SQL语句转换成多表联查形式的SQL如下:
SELECT
t1.*,
countNum.count AS countNum
FROM
t_spjk_jkdwxx t1
LEFT JOIN
(SELECT
JKDSBBH,
COUNT(1) AS count
FROM
t_spjk_jkgcjcxx
WHERE
TGSH >= '2023-07-13 00:00:00'
AND tenant_id = 1667005694987583489
GROUP BY
JKDSBBH) AS countNum
ON
countNum.JKDSBBH = t1.JKDSBBH
WHERE
t1.tenant_id = 1667005694987583489
这样就把原来的子查询转换成了一个联查,通过连接条件将两个表关联起来。同时,使用了一个内部查询来计算 countNum 的值,并将其作为一个别名表连接到原始表 t_spjk_jkdwxx 上。