兩表關聯查詢：sql、mybatis

快乐的凡人721發表於2024-04-12

SQLMyBatis

MySQL 8.0.33

mybatis 3.5.15

mybatis-plus 3.5.5

---

序章

功能：給文字內容打標籤。

文字表：text，主鍵 uuid，還有 content 欄位。

文字標籤表：text_tag，主鍵uuid，欄位text_uuid 為 text表的主鍵，還有 tag_name 欄位——標籤名。

ben釋出於部落格園

需求：

查詢包含所有標籤名的文字。

資料表準備

text

CREATE TABLE `text` (
  `uuid` varchar(100) NOT NULL COMMENT '主鍵',
  `user_uuid` varchar(100) NOT NULL COMMENT '使用者主鍵',
  `content` varchar(1024) NOT NULL COMMENT '內容。原始文字，包含 空格、換行符、連結 等。',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '已刪除？',
  `delete_time` datetime DEFAULT NULL COMMENT '刪除時間',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='文字';

text_tag

CREATE TABLE `text_tag` (
  `uuid` varchar(100) NOT NULL COMMENT '主鍵',
  `user_uuid` varchar(100) NOT NULL COMMENT '使用者主鍵',
  `text_uuid` varchar(100) NOT NULL COMMENT '文字主鍵',
  `tag_name` varchar(50) NOT NULL COMMENT '標籤名稱',
  `tag_order` int DEFAULT '0',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (`uuid`),
  UNIQUE KEY `uq_idx_text_tag` (`text_uuid`,`tag_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='文字標籤';

SQL 部分

1個標籤名

1個標籤名：a

select * from text where uuid in(
	select t1.text_uuid
	from text_tag t1
	where t1.tag_name = 'a'
)
order by create_time desc
;

ben釋出於部落格園

2個標籤名

2個標籤名：a, b

要點：

用到了 join；

join 表自己；

on 後面有多個條件。

select * from text where uuid in(
	select t1.text_uuid
	from text_tag t1
	join text_tag t2 on t1.text_uuid = t2.text_uuid and t1.tag_name = 'a' and t2.tag_name = 'b'
)
order by create_time desc
;

多於 2個標籤名

4個標籤名：a, b, c, d

要點：

多次 join 表自己。

select * from text where uuid in(
	select t1.text_uuid
	from text_tag t1
	join text_tag t2 on t1.text_uuid = t2.text_uuid and t1.tag_name = 'a' and t2.tag_name = 'b'
	join text_tag t3 on t1.text_uuid = t3.text_uuid and t3.tag_name = 'c'
	join text_tag t4 on t1.text_uuid = t4.text_uuid and t4.tag_name = 'd'
)
order by create_time desc
;

通義千問的建議

最初，通義千問建議使用 INTERSECT（求交集）的：

select uuid from text where uuid in (
select  text_uuid from text_tag where tag_name = '1'
INTERSECT
select  text_uuid from text_tag where tag_name = 'a'
)

可是，自己的MySQL不支援：可千問說 mysql 8.0.31 就支援了。

SELECT VERSION(); -- 8.0.33
SHOW VARIABLES LIKE 'sql_mode'; -- ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

參考資料：

1. SQL INTERSECT運算子簡介

https://www.yiibai.com/mysql/sql-union-mysql.html

未讀 TODO。

還需調查。

ben釋出於部落格園

mybatis 部分

java

定義了分頁查詢介面：

pageFindCount 查總數；

pageFindData 查資料。

public interface AppTextMapper extends BaseMapper<AppText> {

    /**
     * 分頁查詢：返回總數
     * @param dto 入參
     * @param tag1 可以為null，來自 dto tagNameList#1
     * @param tag2 可以為null，來自 dto tagNameList#2
     * @param tag3 可以為null，來自 dto tagNameList#3
     * @param tag4 可以為null，來自 dto tagNameList#4
     * @param tag5 可以為null，來自 dto tagNameList#5
     * @return
     */
    long pageFindCount(PageFindDTO dto,
                       String tag1,
                       String tag2,
                       String tag3,
                       String tag4,
                       String tag5);

    /**
     * 分頁查詢：返回 記錄列表
     * @param dto 入參
     * @param tag1 可以為null，來自 dto tagNameList#1
     * @param tag2 可以為null，來自 dto tagNameList#2
     * @param tag3 可以為null，來自 dto tagNameList#3
     * @param tag4 可以為null，來自 dto tagNameList#4
     * @param tag5 可以為null，來自 dto tagNameList#5
     * @return
     */
    List<AppText> pageFindData(PageFindDTO dto,
                               String tag1,
                               String tag2,
                               String tag3,
                               String tag4,
                               String tag5);

}

ben釋出於部落格園

呼叫 mapper 的函式（有些搞笑）：

@Override
public long pageFindCount(PageFindDTO dto) {
	if (Objects.isNull(dto)) {
		// 可改為 丟擲異常
		return 0;
	}

	System.out.println("dto=" + dto);
	List<String> tags = dto.getTagNameList();
	if (CollectionUtils.isEmpty(tags)) {
		return this.baseMapper.pageFindCount(dto, null, null, null, null, null);
	}
	// 搞笑...TODO
	int size = tags.size();
	switch (size) {
		case 1:
			return this.baseMapper.pageFindCount(dto, tags.get(0), null, null, null, null);
		case 2:
			return this.baseMapper.pageFindCount(dto, tags.get(0), tags.get(1), null, null, null);
		case 3:
			return this.baseMapper.pageFindCount(dto, tags.get(0), tags.get(1), tags.get(2), null, null);
		case 4:
			return this.baseMapper.pageFindCount(dto, tags.get(0), tags.get(1), tags.get(2), tags.get(3), null);
		case 5:
			return this.baseMapper.pageFindCount(dto, tags.get(0), tags.get(1), tags.get(2), tags.get(3), tags.get(4));
			default:
				return 0;
	}
}

這麼些的原因：

在 mybatis xml 中使用下面的方式獲取資料失敗：

tagNameList[0]
tagNameList.get(0)

疑問：

難道就沒有其它方式了嗎？

比如，mybatis xml 的 <bind> 標籤？晚點問問通義靈碼。

兩表關聯查詢：sql、mybatis

mybatis xml

pageFindCount 的：

要點：

<where> 的使用；

判斷 dto 中列表大小：size() == 1，兩個等號。

<select id="pageFindCount" resultType="java.lang.Long">
	select count(1) from text
	<where>
		<if test="dto.tagNameList != null and dto.tagNameList.size() > 0">
			uuid in (
			select t1.text_uuid from text_tag t1
			<if test="dto.tagNameList.size() == 1">
				WHERE t1.tag_name = #{tag1}
			</if>
			<if test="dto.tagNameList.size() > 1">
				JOIN text_tag t2 on t1.text_uuid = t2.text_uuid
				AND t1.tag_name = #{tag1}
				AND t2.tag_name = #{tag2}
			</if>
			<if test="dto.tagNameList.size() > 2">
				join text_tag t3 on t1.text_uuid = t3.text_uuid
				AND t3.tag_name = #{tag3}
			</if>
			<if test="dto.tagNameList.size() > 3">
				join text_tag t4 on t1.text_uuid = t4.text_uuid
				AND t4.tag_name = #{tag4}
			</if>
			<if test="dto.tagNameList.size() > 4">
				join text_tag t5 on t1.text_uuid = t5.text_uuid
				AND t4.tag_name =#{tag5}
			</if>
			)
		</if>

		<if test="dto.startTime != null">
			and create_time $gt;= dto.startTime
		</if>

		<if test="dto.endTime != null">
			and create_time $lt; dto.endTime
		</if>

		<if test="dto.keywords != null and dto.keywords.size() > 0">
			<foreach collection="dto.keywords" item="kw" open="" close="" separator="">
				and content like concat('%', #{kw} , '%')
			</foreach>
		</if>
	</where>

	;
</select>

自測：

dto.tagNameList 為null；
dto.tagNameList 為空；
dto.tagNameList 有 1個值；
dto.tagNameList 有 2個值；
dto.tagNameList 有 5個值；

致謝

通義千問

通義靈碼

各位博主

---END---

本文連結：

https://www.cnblogs.com/luo630/p/18130381

ben釋出於部落格園

參考資料

1、left join on多條件深度理解
perfect-ws
已於 2022-06-17 17:34:10 修改
原文連結：
https://blog.csdn.net/cxywangshun/article/details/124472945

2、

ben釋出於部落格園

SQL 三表聯合查詢
2019-09-26
SQL
SQL 兩個表組合查詢
2024-03-06
SQL
MyBatis(六) sql片段定義、級聯查詢、巢狀查詢
2018-07-09
MyBatisSQL巢狀
Mongodb 關聯表查詢
2021-09-26
MongoDB
mysql三表關聯查詢
2019-03-21
MySql
mysql 三表關聯查詢
2020-06-08
MySql
Mybatis【15】-- Mybatis一對一多表關聯查詢
2021-01-26
MyBatis
巢狀關聯會查詢兩次
2022-09-02
巢狀
mysql三張表關聯查詢
2018-10-12
MySql
django 兩個表或多個表聯合查詢
2024-09-20
Django
MySQL三表聯查及兩表聯查
2019-05-10
MySql
mysql三表關聯查詢練習
2019-03-20
MySql
MYSQL A、B表陣列關聯查詢
2019-07-29
MySql陣列
SQL優化之多表關聯查詢-案例一
2019-08-19
SQL優化
flink維表關聯絡列之Redis維表關聯：實時查詢
2019-12-29
Redis
區分關聯子查詢和非關聯子查詢
2019-05-21
01-sql-聯合查詢
2020-10-18
SQL
MyBatis初級實戰之六：一對多關聯查詢
2021-01-22
MyBatis
MyBatis初級實戰之五：一對一關聯查詢
2021-01-21
MyBatis
Spring JPA 聯表查詢
2019-03-06
Spring
mybatis多表聯合查詢的寫法
2024-04-19
MyBatis
查詢皮膚中如何實現兩個 select 下拉框的關聯查詢？
2020-06-18
JPA多表關聯查詢
2018-05-15
Mybatis-Plus-Join（MPJ連表查詢）
2024-06-17
MyBatis
Mybatis查詢
2023-05-19
MyBatis
按月分表聯合查詢
2019-09-11
Mybatis 中將 SQL 查詢記錄到控制檯
2024-05-17
MyBatisSQL
mybatis之sql查詢配置檔案resultType和resultMap
2024-04-25
MyBatisSQL
20240719資料庫關聯查詢、條件查詢
2024-07-19
資料庫
兩表聯查修改的sql語句
2024-03-14
SQL
sql-server不相關子查詢
2020-12-29
SQLServer
sql-server相關子查詢
2020-12-29
SQLServer
SQL server根據表名查詢表主鍵
2021-10-13
SQLServer
MySQL exists關聯子查詢SQL效能及其低下最佳化之等值子查詢轉換
2018-04-20
MySql
springDataJpa聯表查詢之多對多
2019-08-21
Spring
20、聯表查詢JoinON詳解
2020-11-21
SQL單表查詢語句總結
2020-11-10
SQL
關於關聯查詢sql的一次最佳化過程及其他
2021-09-09
SQL

兩表關聯查詢：sql、mybatis

序章

資料表準備

SQL 部分

1個 標籤名

2個 標籤名

多於 2個 標籤名

通義千問 的建議

mybatis 部分

java

mybatis xml

致謝

參考資料

相關文章

1個標籤名

2個標籤名

多於 2個標籤名

通義千問的建議