sql 遍历结果print和表格形式

select * from tb_province --省

pID pName
1 北京市
2 天津市
3 上海市
4 重庆市
5 河北省
6 山西省
.......

select * from tb_city --城市

cID cName pId
19 阿坝藏族羌族自治州 22
8 阿克苏市 31
4 阿拉尔市 31
12 阿拉善盟 32
22 阿勒泰市 31
6 阿里地区 29
12 阿图什市 31
9 安康市 27
8 安庆市 13
......

select * from tb_city where pid=(select pid from tb_province where pname like '%台湾%') order by cid

 --遍历查询结果print

declare @Index int
DECLARE @ccName varchar(50)
DECLARE @Str varchar(1000)
DECLARE @cName varchar(50)
set @cName='台湾'
set @Index = 1
set @Index = (select count(*) from tb_city where pid=(select pid from tb_province where pname like '%'+@cName+'%'))
declare @BId int 
set @BId=1
set @Str=''
while @BId <= @Index
begin
set @ccName=(select cName from tb_city where cID=@BId and pid=(select pid from tb_province where pname like  '%'+@cName+'%'))
 if(@Str='')
 begin
 set @Str='"'+@ccName+'"'
 end
 else
 begin
  set @Str=@Str+',"'+@ccName+'"'
 end
set @BId=@BId+1;
end
  print(REPLACE(@Str,'市',''))

 

--结果

"台北","高雄","基隆","台中","台南","新竹","嘉义","台北县","宜兰县","桃园县","新竹县","苗栗县","台中县","彰化县","南投县","云林县","嘉义县","台南县","高雄县","屏东县","澎湖县","台东县","花莲县"
    

  select pId,  
Name =  
( 
    stuff 
    ( 
        ( 
            select ',' +  
            cName from tb_city where pId = 
            a.pId    for xml path('') 
        ),1,1,''
    ) 
) from tb_city a group by pId 

结果：

pId Name
1 北京市
2 天津市
3 上海市
4 重庆市
5 保定市,沧州市,承德市,邯郸市,衡水市,廊坊市,秦皇岛市,石家庄市,唐山市,邢台市,张家口市
6 长治市,大同市,晋城市,晋中市,临汾市,吕梁市,朔州市,太原市,忻州市,阳泉市,运城市
7 高雄市,高雄县,花莲县,基隆市,嘉义市,嘉义县,苗栗县,南投县,澎湖县,屏东县,台北市,台北县,台东县,台南市,台南县,台中市,台中县,桃园县,新竹市,新竹县,宜兰县,云林县,彰化县

转载于:https://www.cnblogs.com/goole/p/4460728.html