一条sql语句怎么写 急急 在线等啊!!

[furuxiang]

id sortid typeid typeid_2
347 1 0 0
348 2 6 0
349 3 7 8

有四张表
第一张表是tn8cClass_1(sortid,sort(省))
第二张表是tn8cClass_2(typeid,typename(市))
第三张表是tn8cClass_3(typeid_2,typename_2(县))
第四张表是advtst(id,sortid,typeid,typeid_2)
写什么样的sql语句可以实现以下效果呢?

id sort typename typename_2
347 广西 无 无
348 浙江 宁波 无
349 江苏 南京 沭阳

[akuan956]

路过学习

[肥胖的柠檬]

select id, (select sort from where a.sortid= sortid) sort,
(select typename from where a.typeid= typeid) sort,
(select typename_2 from where a.typeid_2= typeid_2) sort
from advtst a

try 我想LZ是想这样的

[肥胖的柠檬]

select id, (select sort from tn8cClass_1 where a.sortid= sortid) sort,
(select typename from tn8cClass_2 where a.typeid= typeid) typename,
(select typename_2 from tn8cClass_3 where a.typeid_2= typeid_2) typename_2
from advtst a

不好意思~点快了

[shuai45]

select id sort typename typename_2 from tn8cClass_1 A,tn8cClass_2 B,tn8cClass_3 C,advtst D where D.sortid =A.sortid and D.typeid=B.typeid and D.typeid_2=C.typeid_2

看看行否

[wgsasd311]

SELECT ID,
SORT=(SELECT SORT FROM T1 WHERE SORTID=T4.SORTID) ,
TYPENAME=CASE TYPEID WHEN 0 THEN (SELECT TYPENAME FROM T2 WHERE TYPEID=T4.TYPEID) ELSE '无' END,
TYPENAME_2=CASE TYPEID_2 WHEN 0 THEN (SELECT TYPENAME_2 FROM T3 WHERE TYPEID=T4.TYPEID_2) ELSE '无' END
FROM T4

[wgsasd311]

--TRY
SELECT T4.ID,
SORT=(SELECT SORT FROM tn8cClass_1 WHERE SORTID=T4.SORTID) ,
TYPENAME=CASE TYPEID WHEN 0 THEN (SELECT TYPENAME FROM tn8cClass_2 WHERE TYPEID=T4.TYPEID) ELSE '无' END,
TYPENAME_2=CASE TYPEID_2 WHEN 0 THEN (SELECT TYPENAME_2 FROM tn8cClass_3 WHERE TYPEID=T4.TYPEID_2) ELSE '无' END
FROM advtst T4

[-狙击手-]

支持楼上

[babu2]

楼主试一下这样是否可以
select advtst.id as ID,
tn8cClass_1.sort as Sort,
tn8cClass_2.typename as Typename,
tn8cClass_3.typename_2 as Typename_2
from advtst
inner join tn8cClass_1 on advtst.sortid = tn8cClass_1.sortid
(inner join tn8cClass_2 on advtst.typeid = tn8cClass_2.typeid
(Inner join tn8cClass_3 on advtst.typeid_2 = tn8cClass_3.typeid_2))

[wgsasd311]

--我上面条件正好写反了,小改如下：
SELECT T4.ID,
SORT=(SELECT SORT FROM tn8cClass_1 WHERE SORTID=T4.SORTID) ,
TYPENAME=CASE TYPEID WHEN 0 THEN '无' ELSE (SELECT TYPENAME FROM tn8cClass_2 WHERE TYPEID=T4.TYPEID) END,
TYPENAME_2=CASE TYPEID_2 WHEN 0 THEN '无' ELSE (SELECT TYPENAME_2 FROM tn8cClass_3 WHERE TYPEID=T4.TYPEID_2) END
FROM advtst T4

[z_jiawei]

select a.id,sort=(select sort from tn8cCLass_1 where sortid=a.sortid),
typename=isnull((select typename from tn8cCLass_2 where typeid=a.typeid),'无'),
typename_2=isnull((select typename_2 from tn8cCLass_3 where typeid_2=a.typeid_2),'无') from advtst a
经过楼上师傅的指点，CSDN第一个回答别人的语句自己也写成了！！

[furuxiang]

wgsasd311(自强不息)
特感谢
支持