原表Test:
ID XID Title
1 0 A
2 0 B
3 0 C
101 1 A-1
102 1 A-2
201 2 B-1
202 2 B-2
301 3 C-1
302 3 C-2
其中主ID为1,2,3,101,102等分别是主ID的子内容
现在我想查询后的结果为:
ID Title1 XID Title2
1 A 101 A-1
1 A 102 A-2
2 B 201 B-1
2 B 202 B-2
3 C 301 C-1
3 C 302 C-2
请教这样结果的查询语句如何写?谢谢!
from Test a join Test b on a.ID=b.XID
where a.XID=0;
from test A,test B
where A.id = B.xid
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-12 16:07:40
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[XID] int,[Title] varchar(3))
insert [tb]
select 1,0,'A' union all
select 2,0,'B' union all
select 3,0,'C' union all
select 101,1,'A-1' union all
select 102,1,'A-2' union all
select 201,2,'B-1' union all
select 202,2,'B-2' union all
select 301,3,'C-1' union all
select 302,3,'C-2'
--------------开始查询--------------------------
---两层
select
A.id,A.title,B.id,B.title
from
tb A,tb B
where
A.id = B.xid---
----------------结果----------------------------
/* id title id title
----------- ----- ----------- -----
1 A 101 A-1
1 A 102 A-2
2 B 201 B-1
2 B 202 B-2
3 C 301 C-1
3 C 302 C-2(6 行受影响)
*/
参考表结构:SELECT tb1.id,tb1.title,tb2.id,tb2.title FROM tb tb1 JOIN tb tb2 ON tb1.id=tb2.xid
id title id title
----------- ----- ----------- -----
1 A 101 A-1
1 A 102 A-2
2 B 201 B-1
2 B 202 B-2
3 C 301 C-1
3 C 302 C-2(6 行受影响)
from Test a join Test b on a.ID=b.XID
where a.XID=0;