一个看似很简单的查询,可怎么写都不对,求助,在线等..................................

Question

建立测试环境的SQL语句;
CREATE TABLE [BA_DistrictClass] (
[iCode] [char]  (10) NOT NULL,
[iValue] [char]  (100) NULL)ALTER TABLE [BA_DistrictClass] WITH NOCHECK ADD  CONSTRAINT [PK_BA_DistrictClass] PRIMARY KEY  NONCLUSTERED ( [iCode] )
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '01','湖北')
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '0101','武汉')
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '010101','汉口')
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '01010101','汉口青年路')
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '02','山东')
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '0201','济南')CREATE TABLE [BA_DistrictAccredit] (
[ZNO] [int]  IDENTITY (1, 1)  NOT NULL,
[pCode] [char]  (10) NULL,
[dCode] [char]  (10) NULL)ALTER TABLE [BA_DistrictAccredit] WITH NOCHECK ADD  CONSTRAINT [PK_BA_DistrictAccredit] PRIMARY KEY  NONCLUSTERED ( [ZNO] )SET IDENTITY_INSERT [BA_DistrictAccredit] ONINSERT [BA_DistrictAccredit] ([pCode],[dCode]) VALUES ('001','01')
INSERT [BA_DistrictAccredit] ([pCode],[dCode]) VALUES ('001','02')=======================问题================================
我的SQL语句select da.ZNO,da.pCode,da.dCode,dc.iValue 
from BA_DistrictAccredit da,BA_DistrictClass dc 
WHERE da.dCode=dc.iCode and  da.pCode='001'                   
order by  da.dCode ASC得到的结果;001  01 湖北
001  02 山东可我想同时得到湖北和山东下面的地区,SQL语句怎么写?

Answer 1

CREATE TABLE [BA_DistrictClass] ( 
[iCode] [char] (10) NOT NULL, 
[iValue] [char] (100) NULL) 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '01','湖北') 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '0101','武汉') 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '010101','汉口') 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '01010101','汉口青年路') 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '02','山东') 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '0201','济南') 
CREATE TABLE [BA_DistrictAccredit] ( 
[ZNO] [int] IDENTITY (1, 1) NOT NULL, 
[pCode] [char] (10) NULL, 
[dCode] [char] (10) NULL) 
goINSERT [BA_DistrictAccredit] ([pCode],[dCode]) VALUES ('001','01') 
INSERT [BA_DistrictAccredit] ([pCode],[dCode]) VALUES ('001','02') select * from BA_DistrictAccredit a,BA_DistrictClass b where a.dcode = left(b.icode,2) order by left(b.icode,2)drop table BA_DistrictAccredit,BA_DistrictClass/*
ZNO         pCode      dCode      iCode      iValue
----------- ---------- ---------- ---------- ----------
1           001        01         01         湖北                                                                                                
1           001        01         0101       武汉                                                                                                
1           001        01         010101     汉口                                                                                                
1           001        01         01010101   汉口青年路                                                                                          
2           001        02         02         山东                                                                                                
2           001        02         0201       济南                                                                                                (6 行受影响)
*/

Answer 2

--注意改动过的地方
CREATE TABLE [BA_DistrictClass] ( 
[iCode] [char] (10) NOT NULL, 
[iValue] [char] (100) NULL) 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '01','湖北') 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '0101','武汉') 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '010101','汉口') 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '01010101','汉口青年路') 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '02','山东') 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '0201','济南') 
CREATE TABLE [BA_DistrictAccredit] ( 
[ZNO] [int] IDENTITY (1, 1) NOT NULL, 
[pCode] [char] (10) NULL, 
[dCode] [char] (10) NULL) 
goINSERT [BA_DistrictAccredit] ([pCode],[dCode]) VALUES ('001','0101') 
INSERT [BA_DistrictAccredit] ([pCode],[dCode]) VALUES ('001','02') select * from BA_DistrictAccredit a,BA_DistrictClass b where b.icode like rtrim(a.dcode)+'%' order by b.icodedrop table BA_DistrictAccredit,BA_DistrictClass/*
ZNO         pCode      dCode      iCode      iValue                                                                                               
----------- ---------- ---------- ---------- ---------------------------------------------------------------------------------------------------- 
1           001        0101       0101       武汉                                                                                                
1           001        0101       010101     汉口                                                                                                
1           001        0101       01010101   汉口青年路                                                                                          
2           001        02         02         山东                                                                                                
2           001        02         0201       济南                                                                                                （所影响的行数为 5 行）
*/

Answer 3

CREATE TABLE [BA_DistrictClass] ( 
[iCode] [char] (10) NOT NULL, 
[iValue] [char] (100) NULL) 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '01','湖北') 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '0101','武汉') 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '010101','汉口') 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '01010101','汉口青年路') 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '02','山东') 
INSERT [BA_DistrictClass] ([iCode],[iValue]) VALUES ( '0201','济南') 
CREATE TABLE [BA_DistrictAccredit] ( 
[ZNO] [int] IDENTITY (1, 1) NOT NULL, 
[pCode] [char] (10) NULL, 
[dCode] [char] (10) NULL) 
goINSERT [BA_DistrictAccredit] ([pCode],[dCode]) VALUES ('001','01') 
select * from BA_DistrictAccredit a,BA_DistrictClass b where a.dcode = left(b.icode,2) order by left(b.icode,2)drop table BA_DistrictAccredit,BA_DistrictClass=================还有问题===================
假如BA_DistrictAccredit删除一条数据，现在的查询结果还是全部的？而不是只有01下面的

Answer 4

select da.ZNO,da.pCode,da.dCode,dc.iValue   
from   BA_DistrictClass dc left join BA_DistrictAccredit da on da.dCode=left(dc.iCode,len(da.dCode))
WHERE   da.pCode='001'                                       
order   by  da.dCode   ASC 

Answer 5

----建立测试环境的SQL语句; 
CREATE   TABLE   [BA_DistrictClass]   ( 
[iCode]   [char]     (10)   NOT   NULL, 
[iValue]   [char]     (100)   NULL) 
go
INSERT   [BA_DistrictClass]   ([iCode],[iValue])   VALUES   (   '01','湖北') 
INSERT   [BA_DistrictClass]   ([iCode],[iValue])   VALUES   (   '0101','武汉') 
INSERT   [BA_DistrictClass]   ([iCode],[iValue])   VALUES   (   '010101','汉口') 
INSERT   [BA_DistrictClass]   ([iCode],[iValue])   VALUES   (   '01010101','汉口青年路') 
INSERT   [BA_DistrictClass]   ([iCode],[iValue])   VALUES   (   '02','山东') 
INSERT   [BA_DistrictClass]   ([iCode],[iValue])   VALUES   (   '0201','济南') 
go
CREATE   TABLE   [BA_DistrictAccredit]   ( 
[ZNO]   [int]     IDENTITY   (1,   1)     NOT   NULL, 
[pCode]   [char]     (10)   NULL, 
[dCode]   [char]     (10)   NULL) 
go
INSERT   [BA_DistrictAccredit]   ([pCode],[dCode])   VALUES   ('001','01') 
INSERT   [BA_DistrictAccredit]   ([pCode],[dCode])   VALUES   ('001','02') 
INSERT   [BA_DistrictAccredit]   ([pCode],[dCode])   VALUES   ('002','0101') 
INSERT   [BA_DistrictAccredit]   ([pCode],[dCode])   VALUES   ('002','0201') go
select da.ZNO,da.pCode,da.dCode,dc.iValue   
from   BA_DistrictClass dc left join BA_DistrictAccredit da on da.dCode=left(dc.iCode,len(da.dCode))
WHERE   da.pCode='001'                                       
order   by  da.dCode   ASC 
drop table BA_DistrictAccredit ,BA_DistrictClass/*  结果
ZNO         pCode      dCode      iValue                                                                                               
----------- ---------- ---------- ---------------------------------------------------------------------------------------------------- 
1           001        01         湖北                                                                                                
1           001        01         武汉                                                                                                
1           001        01         汉口                                                                                                
1           001        01         汉口青年路                                                                                          
2           001        02         山东                                                                                                
2           001        02         济南                                                                                                （所影响的行数为 6 行）*/