sqlite 建索引，建临时表

SQLite临时表与索引创建

最新推荐文章于 2025-11-13 07:43:10 发布

原创最新推荐文章于 2025-11-13 07:43:10 发布 · 4.9k 阅读

0 ·

CC 4.0 BY-SA版权

Sqlite 专栏收录该内容

17 篇文章

订阅专栏

本文介绍如何在SQLite中使用SQL语句创建临时表并为这些表建立索引，以提高特定查询效率。文中详细展示了创建临时表、插入数据及创建索引的具体步骤。

--SELECT * FROM sqlite_temp_master WHERE type='table' AND name='tmpCurrentCityLine';

drop table tmpCurrentCityLine;
create temporary table tmpCurrentCityLine(ID,Name);
insert into tmpCurrentCityLine(ID,Name)
select ID,Name
from tblLine where CityID=2;

drop table tmpCurrentCityPointName;
create temporary table tmpCurrentCityPointName(ID,LineID,Name,RowIndex);
insert into tmpCurrentCityPointName(ID,LineID,Name,RowIndex)
select ID,LineID,Name,RowIndex
from tblLinePoint
where LineID in(select LineID from tmpCurrentCityLine);

--drop index ixTmpCurrentCityLine;
create index ixTmpCurrentCityLine on tmpCurrentCityLine(ID);
--drop index ixTmpCurrentCityPointName;
create index ixTmpPoint_LineID on tmpCurrentCityPointName(LineID);
create index ixTmpPoint_Name on tmpCurrentCityPointName(Name);

--drop index ixLine;
--drop index ixLinePoint_LineID;
--drop index ixLinePoint_Name;

create index ixLine on tblLine(ID);
create index ixLinePoint_LineID on tblLinePoint(LineID);
create index ixLinePoint_Name on tblLinePoint(Name);