4-5个表有关联数据,数据量小的时候性能应该不能损失多少而且很快。
数据量多的时候。直接关联查询,会很占服务器资源(速度快慢我不知道,但资源用光了速度不可能快)。子查询似乎和关联查询只是写法不同,实际执行差不多。一条SQL语句肯定不行我想,先查出 A表的数据,再查出B表的数据。然后再查出C表的。所有单表查询后的数据,一起关联。请问用 with 这种写法行吗? 是先执行查询1放入结果集或者临时表。再执行查询2.。然后最后关联。with
cte1 as
(
select * from table1 where name like 'abc%'
),
cte2 as
(
select * from table2 where id > 20
),
cte3 as
(
select * from table3 where price < 100
)
select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id
数据量多的时候。直接关联查询,会很占服务器资源(速度快慢我不知道,但资源用光了速度不可能快)。子查询似乎和关联查询只是写法不同,实际执行差不多。一条SQL语句肯定不行我想,先查出 A表的数据,再查出B表的数据。然后再查出C表的。所有单表查询后的数据,一起关联。请问用 with 这种写法行吗? 是先执行查询1放入结果集或者临时表。再执行查询2.。然后最后关联。with
cte1 as
(
select * from table1 where name like 'abc%'
),
cte2 as
(
select * from table2 where id > 20
),
cte3 as
(
select * from table3 where price < 100
)
select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id
你用 cte,恐怕还不如用视图.
或许,把表记录查出来放到临时表里,就是你想像的那种方式了.
临时表:
select * into #t from tb where ...
select * into #1 from table1 where name like 'abc%'
select * into #2 from table2 where id > 20
select * into #3 from table3 where price < 100select a.* from #1 a, #2 b, #3 c where a.id = b.id and a.id = c.id用完后不用释放,除非在同一会话中还要用到同名临时表.
insert into #t1
select * from table1 where name like 'abc%'
create table #t2(...)
insert into #t2
select * from table2 where id > 20
create table #t3(....)
insert into #t3
select * from table3 where price < 100select a.* from #t1 a, #t2 b, #t3 c where a.id = b.id and a.id = c.id
两个人同时查询,已经有了#1。时间差,并发问题!