SELECT A.[NAME],B.RELATION,C.COLOR
FROM A,B,C
WHERE A.[NAME] = B.[NAME] AND B.RELATION=C.RELATION
ORDER BY A.[NAME],B.RELATION
FROM A,B,C
WHERE A.[NAME] = B.[NAME] AND B.RELATION=C.RELATION
ORDER BY A.[NAME],B.RELATION
-- Author : HappyFlyStone
-- Date :
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([ID] INT,[Name] NVARCHAR(1))
Go
INSERT INTO ta
SELECT 1,'a' UNION ALL
SELECT 2,'b'
GO
-- Test Data: tb
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
Go
CREATE TABLE tb([Name] NVARCHAR(1),[Relation] NVARCHAR(2))
Go
INSERT INTO tb
SELECT 'a','T1' UNION ALL
SELECT 'a','T2' UNION ALL
SELECT 'b','V1'
GO
-- Test Data: tc
IF OBJECT_ID('[tc]') IS NOT NULL
DROP TABLE [tc]
Go
CREATE TABLE tc([Relation] NVARCHAR(2),[color] NVARCHAR(5))
Go
INSERT INTO tc
SELECT 'T1','red' UNION ALL
SELECT 'T1','green' UNION ALL
SELECT 'T2','black' UNION ALL
SELECT 'V1','white'
GO
--Start
;WITH CTE
AS
(
SELECT top 100 percent A.[NAME],B.RELATION,C.COLOR ,
rid = row_number() over (partition by a.name order by a.name),
sid = row_number() over (partition by a.name,b.relation order by a.name,b.relation)
FROM tA a,tB b,tC c
WHERE A.[NAME] = B.[NAME] AND B.RELATION=C.RELATION
ORDER BY A.[NAME],B.RELATION
)
select
case when rid = 1 then name else '' end as name,
case when sid = 1 then relation else '' end as relation,
COLOR
from cte
--Result:
/*
name relation COLOR
---- -------- -----
a T1 red
green
T2 black
b V1 white(4 行受影响)*/
--End
-- Author : liangCK 小梁 & angellan 兰儿
-- Comment: 小梁 爱 兰儿
-- Date : 2008-10-05 21:07:00
------------------------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (ID INT,Name VARCHAR(1))
INSERT INTO @tb1
SELECT 1,'a' UNION ALL
SELECT 2,'b'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (Name VARCHAR(1),Relation VARCHAR(2))
INSERT INTO @tb2
SELECT 'a','T1' UNION ALL
SELECT 'a','T2' UNION ALL
SELECT 'b','V1'
--> 生成测试数据: @tb3
DECLARE @tb3 TABLE (Relation VARCHAR(2),color VARCHAR(5))
INSERT INTO @tb3
SELECT 'T1','red' UNION ALL
SELECT 'T1','green' UNION ALL
SELECT 'T2','black' UNION ALL
SELECT 'V1','white'--SQL查询如下:
;WITH LiangLoveLan AS
(
SELECT
A.name,B.Relation,C.color,
n_rowid=ROW_NUMBER() OVER(PARTITION BY A.name ORDER BY A.name),
r_rowid=ROW_NUMBER() OVER(PARTITION BY A.name,B.Relation ORDER BY A.name)
FROM @tb1 AS A
JOIN @tb2 AS B
ON A.Name = B.Name
JOIN @tb3 AS C
ON B.Relation = C.Relation
)
SELECT
CASE WHEN n_rowid = 1 THEN name ELSE '' END AS name,
CASE WHEN r_rowid = 1 THEN Relation ELSE '' END AS Relation,
color
FROM LiangLoveLan
/*
name Relation color
---- -------- -----
a T1 red
green
T2 black
b V1 white(4 行受影响)
*/