mssql sql语优化实例不使用insert语句

  • 栏目:数据 时间:2020-05-02 12:13
<返回列表

mssql sql语优化实例不使用insert语句

/*
a表 a.user_id  a.name
b表 b.user_id  b.name
c表 c.user_id  c.user_type

//*

当c表的 c.user_type = "a" 时 它显示 a表的 a.name
当c表的 c.user_type = "b" 时 它显示 b表的 b.name
a,b,c表中的 a.user_id = c.user_id,b.user_id = c.user_id

create table tb_a ( owner char(2) null, skuid varchar(10) null, lot varchar(50) null, quality varchar(2) null, vol decimal(18,2) null, vol_exchange decimal(18,2) null, mysort int )

create table tb_b ( owner char(2) null, skuid varchar(10) null, lot varchar(50) null, quality varchar(2) null, vol decimal(18,2) null, vol_exchange decimal(18,2) null, mysort int )

INSERT INTO tb_a(owner,skuid,lot,quality,vol,vol_exchange,mysort) VALUES ( '78 ', '1000164481 ', '071203 ', '0 ',80.00,70.00,1) INSERT INTO tb_a(owner,skuid,lot,quality,vol,vol_exchange,mysort) VALUES ( '78 ', '1000164481 ', '071202 ', '0 ',60.00,0.00,2) INSERT INTO tb_a(owner,skuid,lot,quality,vol,vol_exchange,mysort) VALUES ( '78 ', '1000164481 ', '081203 ', '0 ',30.00,30.00,3) INSERT INTO tb_a(owner,skuid,lot,quality,vol,vol_exchange,mysort) VALUES ( '78 ', '1000164481 ', '081202 ', '0 ',20.00,20.00,4) INSERT INTO tb_a(owner,skuid,lot,quality,vol,vol_exchange,mysort) VALUES ( '78 ', '1000164481 ', '081201 ', '0 ',10.00,10.00,5)

INSERT INTO tb_b(owner,skuid,lot,quality,vol,vol_exchange,mysort) VALUES ( '01 ', '1000164479 ', '091201 ', '0 ',18.00,6.00,6) INSERT INTO tb_b(owner,skuid,lot,quality,vol,vol_exchange,mysort) VALUES ( '04 ', '1000164481 ', '091201 ', '0 ',20.00,0.00,5) INSERT INTO tb_b(owner,skuid,lot,quality,vol,vol_exchange,mysort) VALUES ( '03 ', '1000164479 ', '091201 ', '0 ',26.00,26.00,4) INSERT INTO tb_b(owner,skuid,lot,quality,vol,vol_exchange,mysort) VALUES ( '02 ', '1000164479 ', '091201 ', '0 ',28.00,28.00,3) INSERT INTO tb_b(owner,skuid,lot,quality,vol,vol_exchange,mysort) VALUES ( '08 ', '1000164481 ', '091201 ', '0 ',60.00,0.00,2) INSERT INTO tb_b(owner,skuid,lot,quality,vol,vol_exchange,mysort) VALUES ( '06 ', '1000164481 ', '091201 ', '0 ',80.00,70.00,1)

*/

GO

if object_id('ta')is not null drop table ta
go
create TABLE ta([user_ID] int,name varchar(50))
INSERT INTO ta select
1,'a1' union all select
2,'a2'

首页 1 2 末页

if object_id('tb')is not null drop table tb
go
create TABLE tb([user_ID] int,name varchar(50))
INSERT INTO tb select
1,'b1' union all select
2,'b2'

if object_id('tc')is not null drop table tc
go
create TABLE tc([user_ID] int,user_type varchar(50))
INSERT INTO tc select
1,'a' union all select
2,'b'

select * from ta
select * from tb
select * from tc

--方法一

select c.[user_id],name=case user_type when 'a' then a.name  when 'b' then b.name end
from tc c,ta a,tb b
where a.[user_id]=c.[user_id] and b.[user_id]=c.[user_id] 

--方法二

SELECT     tc.user_ID, CASE user_type WHEN 'a' THEN ta.name WHEN 'b' THEN tb.name END AS 输出
FROM         tc INNER JOIN
                      ta ON tc.user_ID = ta.user_ID INNER JOIN
                      tb ON tc.user_ID = tb.user_ID

/*

user_ID     name


1           a1
2           a2

user_ID     name


1           b1
2           b2

user_ID     user_type


1           a
2           b

user_id     输出


1           a1
2           b2

*/

上一篇:代码如下复制代码 select a. 下一篇:没有了

更多阅读

代码如下复制代码 select a.

数据 2020-05-02
本文章提供了大量的sql删除重复记录语句,如果你正在愁如何把数据库重复的数据给删除的话...
查看全文

mssql sql语优化实例不使用insert语句

数据 2020-05-02
mssql sql语优化实例不使用insert语句 /* a表 a.user_id  a.name b表 b.user_id  b.name c表 c.user_id  c.user_...
查看全文

sql语句截取字符串函数,计算字符串长度

数据 2020-05-01
sql截取一段字符串并对该字符串进行替换的方法。 使用sql replace REPLACE用第三个表达式替换第一...
查看全文

友情链接: 网站地图

Copyright © 2015-2019 http://www.koi-bumi.com. 韦德体育有限公司 版权所有