SQLite 3 特定需求语句

1.以某个字段结果的长度大小来排序,示例:用于模糊匹配关键字,优先匹配吻合度高的

SELECT * FROM "REQ_ANS_DATA" WHERE name like '%.spec' ORDER BY length(CONDITION) DESC;

2.某列结果去重复-DISTINCT

SELECT DISTINCT ANSWER FROM "REQ_ANS_DATA" WHERE name like '2.spec';

3.查询某个表的是否存在,查询某个表的建表语句

SELECT
	*
FROM
	sqlite_master
WHERE
	type = 'table'
AND name = 'CMP_NO';

SELECT
	sql
FROM
	sqlite_master
WHERE
	tbl_name = 'CMP_NO'
AND type = 'table';

4.模糊查询多个表多个字段,以及隐藏部分字段

SELECT
	*
FROM
	(
		SELECT
			ID,
			NAME,
			MOBILEPHONE,
			FIRSTSPELL,
			'号码隐藏' AS PHONE1,
			0 AS userType,
			'该表没有这个字段' AS COMPANY
		FROM
			TABLE_1
		UNION
			SELECT
				ID,
				NAME,
				MOBILEPHONE,
				FIRSTSPELL,
				'号码隐藏' AS PHONE1,
				2 AS userType,
				COMPANY
			FROM
				TABLE_2
			UNION
				SELECT
					ID,
					NAME,
					MOBILEPHONE,
					FIRSTSPELL,
					'号码隐藏' AS PHONE1,
					1 AS userType,
					COMPANY
				FROM
					TABLE_3
	) r
WHERE
	r.NAME LIKE '%123%'
OR r.FIRSTSPELL LIKE '%123%'
OR r.MOBILEPHONE LIKE '%123%'
OR r.PHONE1 LIKE '%123%'
OR r.COMPANY LIKE '%123%'
ORDER BY
	userType ASC

5.嵌套查询,查询某条数据的下一条数据,使用limit,

SELECT * FROM REQ_ANS_FLOW ORDER BY ID ASC LIMIT (SELECT ID FROM REQ_ANS_FLOW WHERE name = '3'), 1;

6.查询(筛选)出一个表中某些字段内容重复的数据

SELECT * FROM SOUND_WORD GROUP BY ANSWER HAVING count(*) >= 2;

7.查询多个字段,并为某个字段去重

//方法1,会保留后面的重复数据
select * from SOUND_WORD group by ANSWER ORDER BY id ASC;
//方法2,会保留前面的重复数据
SELECT * FROM SOUND_WORD A WHERE NOT EXISTS(SELECT 1 FROM SOUND_WORD WHERE A.ANSWER=ANSWER AND A.ID>ID)

8.使用replace替换字段中的字符

如：替换production表中的 specification 字段中的两个空格为一个空格：

update production set  specification =replace(specification,'  ',' ')