比较复杂的视图^^

[cookies10wen]

Table1
No ColorNo Color Size1 Amount1 Size2 Amount2 Size3 Amount3 Size4 Amount4
1 460 黄色 16 100 18 80 20 100 22 120
2 462 红色 10 100 12 80 14 100
3 464 黑色 16 100 18 80
4 466 白色 13 100 15 80 21 100

Table2
No ColorNo Size
1 460 16
1 460 18
1 460 20
1 460 22
2 462 10
2 462 12
2 462 14
3 464 16
3 464 18
4 466 13
4 466 15
4 466 21

创建一个视图得出:
ColorNo Color Size Amount
460 黄色 16 100
460 黄色 18 80
460 黄色 20 100
460 黄色 22 120
460 黄色 22 120
462 红色 10 100
462 红色 12 80
462 红色 14 100
464 黑色 16 100
464 黑色 18 80
466 白色 13 100
466 白色 15 80
466 白色 21 100

[让你望见影子的墙]

select *from
(
select ColorNo , Color ,Size=size1,amount=amount1
from tb
union all
select ColorNo , Color ,Size=size2,amount=amount2
from tb
union all
select ColorNo , Color ,Size=size3,amount=amount3
from tb
union all
select ColorNo , Color ,Size=size4,amount=amount4
from tb
)K
where size is not null
order by colorno,color

[让你望见影子的墙]

-->> Author:  让你望见影子的墙(HEROWANG)生成测试数据  Date:2009-04-06 09:31:59
IF OBJECT_ID('tb') IS NOT NULL 
    DROP TABLE tb
Go
CREATE TABLE tb(No INT,ColorNo INT,Color NVARCHAR(2),Size1 INT,Amount1 INT,Size2 INT,Amount2 INT,Size3 INT,Amount3 INT,Size4 INT,Amount4 INT)
Go
INSERT INTO tb
SELECT 1,460,'黄色',16,100,18,80,20,100,22,120 UNION ALL
SELECT 2,462,'红色',10,100,12,80,14,100,null,null UNION ALL
SELECT 3,464,'黑色',16,100,18,80,null,null,null,null UNION ALL
SELECT 4,466,'白色',13,100,15,80,21,100,null,null 
GO

SELECT 	* FROM	TB
go
create view v1
as
select * from
(
		select      ColorNo  ,   Color ,Size=size1,amount=amount1
    from tb
    union all
		select      ColorNo  ,   Color ,Size=size2,amount=amount2
    from tb
    union all
		select      ColorNo  ,   Color ,Size=size3,amount=amount3
		from tb
		union all
		select    ColorNo  ,   Color ,Size=size4,amount=amount4
		from tb
)K
where size is not null

go
select * from v1 order by colorno,color

ColorNo     Color Size        amount
----------- ----- ----------- -----------
460         黄色    16          100
460         黄色    18          80
460         黄色    20          100
460         黄色    22          120
462         红色    14          100
462         红色    12          80
462         红色    10          100
464         黑色    16          100
464         黑色    18          80
466         白色    15          80
466         白色    13          100
466         白色    21          100

(12 行受影响)

[wzy_love_sly]

[Quote=引用 1 楼 HEROWANG 的回复:]
select *from
(
select      ColorNo  ,  Color ,Size=size1,amount=amount1
from tb
union all
select      ColorNo  ,  Color ,Size=size2,amount=amount2
from tb
union all
select      ColorNo  ,  Color ,Size=size3,amount=amount3
from tb
union all
select    ColorNo  ,  Color ,Size=size4,amount=amount4
from tb
)K
where size is not null
order by colorno,color

[/Quote]
马客

[rucypli]

select T1.ColorNo,T2.Color,T1.size,T3.amount
from Table2 T1,table1 T2,table1 T3
where T1.colorNo=T2.ColorNo
and T1.size=T3.size1
union all
select T1.ColorNo,T2.Color,T1.size,T3.amount
from Table2 T1,table1 T2,table1 T3
where T1.colorNo=T2.ColorNo
and T1.size=T3.size2
union
select T1.ColorNo,T2.Color,T1.size,T3.amount
from Table2 T1,table1 T2,table1 T3
where T1.colorNo=T2.ColorNo
and T1.size=T3.size3
union all
select T1.ColorNo,T2.Color,T1.size,T3.amount
from Table2 T1,table1 T2,table1 T3
where T1.colorNo=T2.ColorNo
and T1.size=T3.size4

[csdyyr]

DECLARE @TB TABLE(No INT, ColorNo INT, Color NVARCHAR(2), Size1 INT, Amount1 INT, Size2 INT, Amount2 INT, Size3 INT, Amount3 INT, Size4 INT, Amount4 INT)
INSERT @TB
SELECT 1, 460, N'黄色', 16, 100, 18, 80, 20, 100, 22, 120 UNION ALL
SELECT 2, 462, N'红色', 10, 100, 12, 80, 14, 100, null, null UNION ALL
SELECT 3, 464, N'黑色', 16, 100, 18, 80, null, null, null, null UNION ALL
SELECT 4, 466, N'白色', 13, 100, 15, 80, 21, 100, null, null


SELECT *
FROM (
SELECT ColorNo,Color,Size1 AS [Size],Amount1 as Amount FROM @TB
UNION ALL
SELECT ColorNo,Color,Size2,Amount2 FROM @TB
UNION ALL
SELECT ColorNo,Color,Size3,Amount3 FROM @TB
UNION ALL
SELECT ColorNo,Color,Size4,Amount4 FROM @TB
)T
WHERE [Size] IS NOT NULL AND Amount IS NOT NULL
ORDER BY ColorNo,[Size]
/*
ColorNo Color Size Amount
----------- ----- ----------- -----------
460 黄色 16 100
460 黄色 18 80
460 黄色 20 100
460 黄色 22 120
462 红色 10 100
462 红色 12 80
462 红色 14 100
464 黑色 16 100
464 黑色 18 80
466 白色 13 100
466 白色 15 80
466 白色 21 100

(12 row(s) affected)
*/

[cookies10wen]

实在不好意思,小弟搞错了问题..如下:
Table1
No ColorNo Color Size1 Amount1 Size2 Amount2 Size3 Amount3 Size4 Amount4
1 460 黄色 16 100 18 80 20 100 22 120
2 462 红色 10 100 12 80 14 100
3 464 黑色 16 100 18 80
4 466 白色 13 100 15 80 21 100

Table2
No ColorNo Size AtAmount
1 460 16 30
1 460 18 80
1 460 20 20
1 460 22 50
2 462 10 60
2 462 12 40
2 462 14 30
3 464 16 20
3 464 18 20
4 466 13 20
4 466 15 20
4 466 21 20

创建一个视图得出:
ColorNo Color Size Amount AtAmount
460 黄色 16 100 30
460 黄色 18 80 80
460 黄色 20 100 20
460 黄色 22 120 50
462 红色 10 100 60
462 红色 12 80 40
462 红色 14 100 30
464 黑色 16 100 20
464 黑色 18 80 20
466 白色 13 100 20
466 白色 15 80 20
466 白色 21 100 20

看少了一个AtAmount字段...谢谢各位指教...

[csdyyr]

DECLARE @TB TABLE(No INT,  ColorNo INT,  Color NVARCHAR(2),  Size1 INT,  Amount1 INT, Size2 INT, Amount2 INT, Size3 INT,  Amount3 INT,  Size4 INT, Amount4 INT)
INSERT @TB
SELECT 1,  460,  N'黄色',  16,  100,  18,  80,  20,  100,  22,  120 UNION ALL 
SELECT 2,  462,  N'红色',  10,  100,  12,  80,  14,  100,  null,  null UNION ALL 
SELECT 3,  464,  N'黑色',  16,  100,  18,  80,  null,  null,  null,  null UNION ALL 
SELECT 4,  466,  N'白色',  13,  100,  15,  80,  21,  100,  null,  null

DECLARE @TA TABLE(No INT,  ColorNo INT, Size INT, AtAmount INT)
INSERT @TA
SELECT 1,  460,  16,  30 UNION ALL 
SELECT 1,  460,  18,  80 UNION ALL 
SELECT 1,  460,  20,  20 UNION ALL 
SELECT 1,  460,  22,  50 UNION ALL 
SELECT 2,  462,  10,  60 UNION ALL 
SELECT 2,  462,  12,  40 UNION ALL 
SELECT 2,  462,  14,  30 UNION ALL 
SELECT 3,  464,  16,  20 UNION ALL 
SELECT 3,  464,  18,  20 UNION ALL 
SELECT 4,  466,  13,  20 UNION ALL 
SELECT 4,  466,  15,  20 UNION ALL 
SELECT 4,  466,  21,  20


SELECT T.*,AtAmount
FROM (
SELECT ColorNo,Color,Size1 AS [Size],Amount1 as Amount FROM @TB
UNION ALL
SELECT ColorNo,Color,Size2,Amount2 FROM @TB
UNION ALL
SELECT ColorNo,Color,Size3,Amount3 FROM @TB
UNION ALL
SELECT ColorNo,Color,Size4,Amount4 FROM @TB
)T JOIN @TA AS B ON T.ColorNo=B.ColorNo AND T.[Size]=B.[Size]
WHERE T.[Size] IS NOT NULL AND T.Amount  IS NOT NULL
ORDER BY T.ColorNo,T.[Size]
/*
ColorNo     Color Size        Amount      AtAmount    
----------- ----- ----------- ----------- ----------- 
460         黄色    16          100         30
460         黄色    18          80          80
460         黄色    20          100         20
460         黄色    22          120         50
462         红色    10          100         60
462         红色    12          80          40
462         红色    14          100         30
464         黑色    16          100         20
464         黑色    18          80          20
466         白色    13          100         20
466         白色    15          80          20
466         白色    21          100         20

(12 row(s) affected)
*/

[dj3688]

DECLARE @Table1 TABLE(No INT, ColorNo INT, Color NVARCHAR(2), Size1 INT, Amount1 INT, Size2 INT, Amount2 INT, Size3 INT, Amount3 INT, Size4 INT, Amount4 INT)
INSERT @Table1
SELECT 1, 460, N'黄色', 16, 100, 18, 80, 20, 100, 22, 120 UNION ALL
SELECT 2, 462, N'红色', 10, 100, 12, 80, 14, 100, null, null UNION ALL
SELECT 3, 464, N'黑色', 16, 100, 18, 80, null, null, null, null UNION ALL
SELECT 4, 466, N'白色', 13, 100, 15, 80, 21, 100, null, null


DECLARE @Table2 TABLE(No INT, ColorNo INT, [Size] INT, Atmount INT)
INSERT @Table2
select 1, 460, 16, 30 union all
select 1, 460, 18, 80 union all
select 1, 460, 20, 20 union all
select 1, 460, 22, 50 union all
select 2, 462, 10, 60 union all
select 2, 462, 12, 40 union all
select 2, 462, 14, 30 union all
select 3, 464, 16, 20 union all
select 3, 464, 18, 20 union all
select 4, 466, 13, 20 union all
select 4, 466, 15, 20 union all
select 4, 466, 21, 20


SELECT *
FROM (
SELECT a.ColorNo,a.Color,b.Size AS [Size],a.Amount1 as Amount,b.Atmount FROM @Table1 a,@Table2 b where a.ColorNo=b.ColorNo and a.Size1=b.Size
UNION ALL
SELECT a.ColorNo,a.Color,b.Size AS [Size],a.Amount2 as Amount,b.Atmount FROM @Table1 a,@Table2 b where a.ColorNo=b.ColorNo and a.Size2=b.Size
UNION ALL
SELECT a.ColorNo,a.Color,b.Size AS [Size],a.Amount3 as Amount,b.Atmount FROM @Table1 a,@Table2 b where a.ColorNo=b.ColorNo and a.Size3=b.Size
UNION ALL
SELECT a.ColorNo,a.Color,b.Size AS [Size],a.Amount4 as Amount,b.Atmount FROM @Table1 a,@Table2 b where a.ColorNo=b.ColorNo and a.Size4=b.Size
)T
ORDER BY ColorNo,[Size]

[claro]

帮顶。

[cookies10wen]

谢谢大家..