一.问题描述
1.已知条件
2.关联条件
现在需要将 table_1 和 table_2 进行关联,关联条件是 order_no,community_id,post_id 这 3 个字段,但是 order_no 不为 null,不过 community_id,post_id 是可能为 null,也可能不为 null
3.初步解法
如以下 SQL 所示,发现结果为空,没有查询到数据,实际是有数据,问题在于 community_id 和 post_id 对于空值的处理。
<select id="totalPageInfo" resultType="com.kwan.springbootkwan.entity.dto.CsdnTotalIncomeDTO">
SELECT t1.receiver_nick_name AS nickName
, SUM(t1.received_money) AS amount
FROM table_1 t1 left join table_2 t2
on t1.order_no = t2.order_no
AND t1.community_id=t2.community_id
AND t1.post_id=t2.post_id
WHERE 1 = 1
<if test="query.startDate != null">
AND t1.receive_time <![CDATA[>= #{query.startDate}]]>
AND t2.create_time <![CDATA[>= #{query.startDate}]]>
</if>
<if test="query.endDate != null">
AND t1.receive_time <![CDATA[<= #{query.endDate}]]>
AND t2.create_time <![CDATA[<= #{query.endDate}]]>
</if>
GROUP BY nickName
ORDER BY amount DESC
</select>
二.解决方案
1.SQL 如下
<select id="totalPageInfo" resultType="com.kwan.springbootkwan.entity.dto.CsdnTotalIncomeDTO">
SELECT t1.receiver_nick_name AS nickName
, SUM(t1.received_money) AS amount
FROM table_1 t1 left join table_2 t2
on t1.order_no = t2.order_no
<![CDATA[
AND t1.community_id <=> t2.community_id
AND t1.post_id<=> t2.post_id
]]>
WHERE 1 = 1
<if test="query.startDate != null">
AND t1.receive_time <![CDATA[>= #{query.startDate}]]>
AND t2.create_time <![CDATA[>= #{query.startDate}]]>
</if>
<if test="query.endDate != null">
AND t1.receive_time <![CDATA[<= #{query.endDate}]]>
AND t2.create_time <![CDATA[<= #{query.endDate}]]>
</if>
GROUP BY nickName
ORDER BY amount DESC
</select>
2.解释说明
在这个 SQL 查询中,由于 community_id
和 post_id
可能为空,你可以通过使用 COALESCE
函数或 IFNULL
函数(具体取决于你使用的数据库系统)来处理可能的空值情况。
下面是一种修改方式,假设你使用的是 MySQL 数据库:
SELECT
t1.receiver_nick_name AS nickName,
SUM(t1.received_money) AS amount
FROM
table_1 t1
LEFT JOIN
table_2 t2 ON t1.order_no = t2.order_no
AND t1.community_id <=> t2.community_id
AND t1.post_id <=> t2.post_id
WHERE
1 = 1
<if test="query.startDate != null">
AND t1.receive_time <![CDATA[>= #{query.startDate}]]>
AND t2.create_time <![CDATA[>= #{query.startDate}]]>
</if>
<if test="query.endDate != null">
AND t1.receive_time <![CDATA[<= #{query.endDate}]]>
AND t2.create_time <![CDATA[<= #{query.endDate}]]>
</if>
GROUP BY
nickName
ORDER BY
amount DESC
在这里,使用了 <=>
操作符,它在 MySQL 中用于处理 NULL 值的比较。如果 community_id
或 post_id
的其中一个是 NULL,那么 <=>
操作符会返回 true。
请根据你使用的数据库类型来调整语法。如果是其他数据库,可能会使用 COALESCE
或 IS NULL
等不同的语法。