比较复杂的视图^^

cookies10wen 2009-04-06 09:26:39
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
...全文
96 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
cookies10wen 2009-04-06
  • 打赏
  • 举报
回复
谢谢大家..
claro 2009-04-06
  • 打赏
  • 举报
回复
帮顶。
dj3688 2009-04-06
  • 打赏
  • 举报
回复
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]


csdyyr 2009-04-06
  • 打赏
  • 举报
回复
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)
*/
cookies10wen 2009-04-06
  • 打赏
  • 举报
回复
实在不好意思,小弟搞错了问题..如下:
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 2009-04-06
  • 打赏
  • 举报
回复
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)
*/
rucypli 2009-04-06
  • 打赏
  • 举报
回复
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
wzy_love_sly 2009-04-06
  • 打赏
  • 举报
回复
[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]
马客
  • 打赏
  • 举报
回复

-->> 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 行受影响)
  • 打赏
  • 举报
回复
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

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧