我有一个新闻表,news,有一个城市表city,
news表中有字段:id,titel,cityid,adddate
city表中有字段:id,name,sidsid中是城市父ID如:
city表:
1,广东,0
2,广州,1
3,江苏,0
4,苏州,3现在我想取广东的新闻(同时也要把它下面的所有城市的新闻也取出来),应该怎么取?
news表中有字段:id,titel,cityid,adddate
city表中有字段:id,name,sidsid中是城市父ID如:
city表:
1,广东,0
2,广州,1
3,江苏,0
4,苏州,3现在我想取广东的新闻(同时也要把它下面的所有城市的新闻也取出来),应该怎么取?
declare @id int
select @id=1
with cte as
(select id,id as cid from city where @id=id)
union all
(select a.id,b.id as cid
from cte a join city b on a.id=b.sid)select * from news where cityid in (select cid from cte)
cityid in (select id from city where sid=1) --广东的ID为1
or cityid=1
lz试下这个语句
不知道你要的是不是这个效果
select title,addDate,name from @news n
inner join @city c on n.cityid=c.id where c.id=1 or c.sid=1
create table city(id int , name varchar(50) , parentid int)
insert into city
select 1 , '江苏' , 0 union all
select 2 , '山东' , 0 union all
select 3 , '南京' , 1 union all
select 4 , '镇江' , 1 union all
select 5 , '泰州' , 1 union all
select 6 , '兴化' , 5 union all
select 7 , '济南' , 2with s as
(
select id , name , parentid
from city where id = 1
union all
select a.id , a.name , a.parentid
from city a inner join s b
on a.parentid = b.id
)
select * from s查询的结果是
========================================================
1 江苏 0
3 南京 1
4 镇江 1
5 泰州 1
6 兴化 5
from news
where id in (select id
from city
where sid=(select id from city where [name]='广东')
OR [Name]='广东')
select titel from news where cityid in
(
select id from city where id=1 and sid=1
)
create function getT(@id int) returns @TABLE table(id int)
as
begin
insert into @TABLE select @id
declare @id1 int
declare mycursor cursor for select b.id from city as a, city as b where a.id=@id and a.id=b.sid
open mycursor
FETCH NEXT FROM mycursor into @id1
while @@fetch_status=0
begin
begin
insert into @TABLE select *from dbo.getT(@id1)
end
FETCH NEXT FROM mycursor into @id1
end
CLOSE mycursor
deallocate mycursor
return
endselect *
from news
join (select* from dbo.getT(1)) as d //城市ID
on news.cityid=d.id