sql这样写会不会很慢。。

极客诗人 2018-06-12 10:38:40

...全文

248 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

flybirding10011 2018-06-13

打赏
举报

过来学习一下

极客诗人 2018-06-12

打赏
举报

引用 9 楼 yenange 的回复:

USE tempdb
GO
IF OBJECT_ID('table_a') IS NOT NULL DROP TABLE table_a
IF OBJECT_ID('table_b') IS NOT NULL DROP TABLE table_b
IF OBJECT_ID('table_c') IS NOT NULL DROP TABLE table_c
IF OBJECT_ID('table_d') IS NOT NULL DROP TABLE table_d
GO
--订单
create table table_a
(
	Id int primary key identity(1,1),
	TypeId int ,
	ServerId int,
	UserId int,
)
--用户
create table table_b
(
	Id int primary key identity (1,1),
	UserName nvarchar(50)
)
--服务类1
create table table_c
(
	Id int primary key identity(1,1),
	ServerName nvarchar(50)
)
--服务类2
create table table_d
(
	Id int primary key identity(1,1),
	ServiceName nvarchar(50)
)
----------------- 预备 ---------------------
--0. 增加连接字段的索引
--a
CREATE INDEX ix_table_a_UserId ON table_a(UserId);
CREATE INDEX ix_table_a_ServerId ON table_a(ServerId,TypeId);
IF OBJECT_ID('tempdb..#tmp_B') IS NOT NULL
	DROP TABLE #tmp_B
----------------- 查询 ---------------------
--1. 
SELECT t2.*
INTO #tmp_B
FROM table_b t2 WITH(NOLOCK)
where t2.UserName like '%2'
--2.
CREATE UNIQUE CLUSTERED INDEX ix_#tmp_B_id ON #tmp_B(Id);
--3.
select t1.Id as ID,t2.UserName as UserName,
case
t1.TypeId 
when 1 THEN t3.ServerName
when 2 THEN t4.ServiceName
ELSE ''
END as ServerName
from table_a t1  WITH(NOLOCK)
left join #tmp_B t2  WITH(NOLOCK) on t1.UserId=t2.Id
left join table_c t3 WITH(NOLOCK) on  (t1.TypeId=1 and t1.ServerId=t3.Id)
left join table_d t4 WITH(NOLOCK) on (t1.ServerId=t4.Id and t1.TypeId=2)
--4. 删除临时表
DROP TABLE #tmp_B

过程麻烦了一点，但思路很简单，就是分了几步而已。服务器稍好一点，应付几百万的数据没什么问题， 1秒出数据。

好的谢谢了我去研究研究

吉普赛的歌 2018-06-12

打赏
举报

USE tempdb
GO
IF OBJECT_ID('table_a') IS NOT NULL DROP TABLE table_a
IF OBJECT_ID('table_b') IS NOT NULL DROP TABLE table_b
IF OBJECT_ID('table_c') IS NOT NULL DROP TABLE table_c
IF OBJECT_ID('table_d') IS NOT NULL DROP TABLE table_d
GO
--订单
create table table_a
(
	Id int primary key identity(1,1),
	TypeId int ,
	ServerId int,
	UserId int,
)
--用户
create table table_b
(
	Id int primary key identity (1,1),
	UserName nvarchar(50)
)
--服务类1
create table table_c
(
	Id int primary key identity(1,1),
	ServerName nvarchar(50)
)
--服务类2
create table table_d
(
	Id int primary key identity(1,1),
	ServiceName nvarchar(50)
)
----------------- 预备 ---------------------
--0. 增加连接字段的索引
--a
CREATE INDEX ix_table_a_UserId ON table_a(UserId);
CREATE INDEX ix_table_a_ServerId ON table_a(ServerId,TypeId);
IF OBJECT_ID('tempdb..#tmp_B') IS NOT NULL
	DROP TABLE #tmp_B
----------------- 查询 ---------------------
--1. 
SELECT t2.*
INTO #tmp_B
FROM table_b t2 WITH(NOLOCK)
where t2.UserName like '%2'
--2.
CREATE UNIQUE CLUSTERED INDEX ix_#tmp_B_id ON #tmp_B(Id);
--3.
select t1.Id as ID,t2.UserName as UserName,
case
t1.TypeId 
when 1 THEN t3.ServerName
when 2 THEN t4.ServiceName
ELSE ''
END as ServerName
from table_a t1  WITH(NOLOCK)
left join #tmp_B t2  WITH(NOLOCK) on t1.UserId=t2.Id
left join table_c t3 WITH(NOLOCK) on  (t1.TypeId=1 and t1.ServerId=t3.Id)
left join table_d t4 WITH(NOLOCK) on (t1.ServerId=t4.Id and t1.TypeId=2)
--4. 删除临时表
DROP TABLE #tmp_B

过程麻烦了一点，但思路很简单，就是分了几步而已。服务器稍好一点，应付几百万的数据没什么问题， 1秒出数据。

极客诗人 2018-06-12

打赏
举报


--订单
create table table_a
(
	Id int primary key identity(1,1),
	TypeId int ,
	ServerId int,
	UserId int,
)
--用户
create table table_b
(
	Id int primary key identity (1,1),
	UserName nvarchar(50)
)
--服务类1
create table table_c
(
	Id int primary key identity(1,1),
	ServerName nvarchar(50)
)
--服务类2
create table table_d
(
	Id int primary key identity(1,1),
	ServiceName nvarchar(50)
)
select t1.Id as ID,t2.UserName as UserName,
case
t1.TypeId 
when 1 THEN t3.ServerName
when 2 THEN t4.ServiceName
ELSE ''
END as ServerName
from table_a t1
left join table_b t2 on t1.UserId=t2.Id
left join table_c t3 on  (t1.TypeId=1 and t1.ServerId=t3.Id)
left join table_d t4 on (t1.ServerId=t4.Id and t1.TypeId=2)
where UserName like '%2'

极客诗人 2018-06-12