比较复杂的视图^^

Question

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

Answer 1

-->> 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 
GOSELECT  * 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 nullgo
select * from v1 order by colorno,colorColorNo     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 行受影响)

Answer 2

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

Answer 3

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)
*/

Answer 4

实在不好意思,小弟搞错了问题..如下:
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字段...谢谢各位指教...

Answer 5

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,  nullDECLARE @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)
*/

Answer 6

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]