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 江苏 南京 沭阳
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 江苏 南京 沭阳
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 atry 我想LZ是想这样的
(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不好意思~点快了
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
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
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))
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
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第一个回答别人的语句自己也写成了!!
特感谢
支持