实现如图的sql语句写法 - 编程论坛

#2

静水且流深2017-07-12 11:29

表T1和表T2的关联关系是怎样的呢？不妨将表结构贴一下

#3

mywisdom882017-07-12 18:05

if object_id('tempdb..#t1') is not null drop table #t1
go
if object_id('tempdb..#t2') is not null drop table #t2
go
create table #t1([type] varchar(10))
create table #t2([code] int,name varchar(10))

insert into #t1(type)
select 'a' union all
select 'b' union all
select 'd' union all
select 'c' union all
select 'e'

insert into #t2(code,name)
select 1,'张1' union all
select 2,'张2' union all
select 3,'张3' union all
select 4,'张4' union all
select 2,'张22' union all
select 1,'张11'

declare @type varchar(10)

set @type ='a'

select t2.* from (
select type,(case type when 'A' then 1
        else case type when 'B' then 2
            else case type when 'C' then 3 else 4 end
            end
        end) as code from #t1 where type =@type )t1,#t2 t2
where t1.code=t2.code

#4

pzywillpan2017-08-04 12:08

if object_id('tempdb..t1') is not null drop table t1

CREATE TABLE T1 (ID INT,TYPE VARCHAR(50))

INSERT INTO T1 (ID,TYPE)
VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D')

if object_id('tempdb..t2') is not null drop table t2

CREATE TABLE T2 (ID INT,CODE VARCHAR(50),ID2 INT)

INSERT INTO T2 (ID,CODE,ID2)
VALUES (1,1,1),(2,2,2),(3,1,3),(4,2,3),(5,1,4),(6,2,4)

SELECT CODE FROM T1 INNER JOIN T2 ON T1.ID = T2.ID2
WHERE TYPE = 'A'

SELECT CODE FROM T1 INNER JOIN T2 ON T1.ID = T2.ID2
WHERE TYPE = 'B'

SELECT CODE FROM T1 INNER JOIN T2 ON T1.ID = T2.ID2
WHERE TYPE = 'C'

SELECT CODE FROM T1 INNER JOIN T2 ON T1.ID = T2.ID2
WHERE TYPE = 'D'

不知道是不是你要的结果，可以参考一下