22,210
社区成员
发帖
与我相关
我的任务
分享创建视图
有个表
有字段 F1，F2，F3，F4，F5 其中F1-F5字段的值范围在1-4之间，其中有两字段的值都为4，但是不知道是哪个字段，
现在要选出值不为4的另外三个字段（可理解为选出值小于4的三个字段）创建成一个视图
例如：（事先不知道那个字段的值全为4）
F1 F2 F3 F4 F5
4 3 2 4 2
4 1 3 4 2
4 1 3 4 2
...
创建的视图如下：
T1 T2 T3
3 2 2
1 3 2
1 3 2
...
有字段 F1，F2，F3，F4，F5 其中F1-F5字段的值范围在1-4之间，其中有两字段的值都为4，但是不知道是哪个字段，
现在要选出值不为4的另外三个字段（可理解为选出值小于4的三个字段）创建成一个视图
例如：（事先不知道那个字段的值全为4）
F1 F2 F3 F4 F5
4 3 2 4 2
4 1 3 4 2
4 1 3 4 2
...
创建的视图如下：
T1 T2 T3
3 2 2
1 3 2
1 3 2
...
...全文
请发表友善的回复…
发表回复
中国风 2011-10-18
- 打赏
- 举报
动态行转列视图方法，想到用存储过程+OPENROWSET可解决楼主的问题
刚把楼主的问题整理了一篇博文;
http://blog.csdn.net/roy_88/article/details/6883078
刚把楼主的问题整理了一篇博文;
http://blog.csdn.net/roy_88/article/details/6883078
longai123 2011-10-18
- 打赏
- 举报
头晕了...
中国风 2011-10-17
- 打赏
- 举报
9取7
use Tempdb
go
--> -->
if not object_id(N'T') is null
drop table T
Go
create table t(F1 int,F2 int,F3 int,F4 int,F5 int,F6 int,F7 int,F8 int,F9 int)
insert into t select 4,3,2,4,2,3,4,2,6
insert into t select 4,1,3,4,2,4,2,3,5
insert into t select 4,1,3,4,2,6,3,4,5
insert into t select 4,2,1,4,3,2,4,1,3
Go
if OBJECT_ID('v_T') is not null
drop view v_T
go
Create view v_T
as
with b
as
(
Select
*
from
(select *, ROW_NUMBER()over( order by (select 1)) as row from T) as a
UNPIVOT
(VALUE FOR Cols IN([F1],[F2],[F3],[F4],[F5],[F6],[F7],[F8],[F9]))AS b
),b2
as
(
select
VALUE,COls,Groups=ROW_NUMBER()over(partition by Cols order by row),DENSE_RANK()over(order by Cols) as row
from b
where
not exists(select 1 from b as c where c.Cols=b.Cols and VALUE=4 having COUNT(c.row)=(select MAX(row) from b))
)
select
max(case when row=1 then VALUE end) as T1,
max(case when row=2 then VALUE end) as T2,
max(case when row=3 then VALUE end) as T3,
max(case when row=4 then VALUE end) as T4,
max(case when row=5 then VALUE end) as T5,
max(case when row=6 then VALUE end) as T6,
max(case when row=7 then VALUE end) as T7
from b2
group by Groups
go
select * from v_T
/*
T1 T2 T3 T4 T5 T6 T7
3 2 2 3 4 2 6
1 3 2 4 2 3 5
1 3 2 6 3 4 5
2 1 3 2 4 1 3
*/中国风 2011-10-17
- 打赏
- 举报
改改
use Tempdb
go
--> -->
if not object_id(N'T') is null
drop table T
Go
Create table T([F1] int,[F2] int,[F3] int,[F4] int,[F5] int)
Insert T
select 4,3,2,4,2 union all
select 4,1,3,4,2 union all
select 4,1,3,4,2
Go
if OBJECT_ID('v_T') is not null
drop view v_T
go
Create view v_T
as
with b
as
(
Select
*
from
(select *, ROW_NUMBER()over( order by (select 1)) as row from T) as a
UNPIVOT
(VALUE FOR Cols IN([F1],[F2],[F3],[F4],[F5]))AS b
),b2
as
(
select
VALUE,COls,Groups=ROW_NUMBER()over(partition by Cols order by row),DENSE_RANK()over(order by Cols) as row
from b
where
not exists(select 1 from b as c where c.Cols=b.Cols and VALUE=4 having COUNT(c.row)=(select MAX(row) from b))
)
select
max(case when row=1 then VALUE end) as T1,
max(case when row=2 then VALUE end) as T2,
max(case when row=3 then VALUE end) as T3
from b2
group by Groups
go
select * from v_T
/*
T1 T2 T3
3 2 2
1 3 2
1 3 2
*/中国风 2011-10-17
- 打赏
- 举报
这样的视图有意义?
use Tempdb
go
--> -->
if not object_id(N'T') is null
drop table T
Go
Create table T([F1] int,[F2] int,[F3] int,[F4] int,[F5] int)
Insert T
select 4,3,2,4,2 union all
select 4,1,3,4,2 union all
select 4,1,3,4,2
Go
if OBJECT_ID('v_T') is not null
drop view v_T
go
Create view v_T
as
with b
as
(
Select
*,ROW_NUMBER()over( order by (select 1)) as row
from T
UNPIVOT
(VALUE FOR Cols IN([F1],[F2],[F3],[F4],[F5]))AS b
),b2
as
(
select
VALUE,COls,Groups=ROW_NUMBER()over(partition by Cols order by row),DENSE_RANK()over(order by Cols) as row
from b
where not exists(select 1 from b as c where c.COls=b.COls and VALUE=4)
)
select
max(case when row=1 then VALUE end) as T1,
max(case when row=2 then VALUE end) as T2,
max(case when row=3 then VALUE end) as T3
from b2
group by Groups
go
select * from v_T
/*
T1 T2 T3
3 2 2
1 3 2
1 3 2
*/
-晴天 2011-10-17
- 打赏
- 举报
关键不在于除了4以外其他是几,关键是要找出全是4的列,并将它去除.
如果是用其他处理方法还好弄,你要视图,要一句话完成,还不能用变量,临时表等其他辅助手段,上面那个,我觉得已经够简单的了.
不过,还是进一步优化一下:
方法,用列转行,将内容拆分,消除都为4的列,然后再根据总行数获得行序号,再作行转列操作.
如果是用其他处理方法还好弄,你要视图,要一句话完成,还不能用变量,临时表等其他辅助手段,上面那个,我觉得已经够简单的了.
不过,还是进一步优化一下:
create table tb(F1 int,F2 int,F3 int,F4 int,F5 int,F6 int,F7 int,F8 int,F9 int)
insert into tb select 4,3,2,4,2,3,4,2,6
insert into tb select 4,1,3,4,2,4,2,3,5
insert into tb select 4,1,3,4,2,6,3,4,5
insert into tb select 4,2,1,4,3,2,4,1,3
go
create view v1 as
select rn,
max(case when id=1 then f1 end)f1,
max(case when id=2 then f1 end)f2,
max(case when id=3 then f1 end)f3,
max(case when id=4 then f1 end)f4,
max(case when id=5 then f1 end)f5,
max(case when id=6 then f1 end)f6,
max(case when id=7 then f1 end)f7
from(
select (rn-1)/(select COUNT(*) from tb)+1 id,(rn-1)%(select COUNT(*) from tb)+1 rn,f1 from(
select row_number()over(order by (select 1))rn,* from(
select f1 from tb where exists(select 1 from tb where f1<>4)
union all
select f2 from tb where exists(select 1 from tb where f2<>4)
union all
select f3 from tb where exists(select 1 from tb where f3<>4)
union all
select f4 from tb where exists(select 1 from tb where f4<>4)
union all
select f5 from tb where exists(select 1 from tb where f5<>4)
union all
select f6 from tb where exists(select 1 from tb where f6<>4)
union all
select f7 from tb where exists(select 1 from tb where f7<>4)
union all
select f8 from tb where exists(select 1 from tb where F8<>4)
union all
select f9 from tb where exists(select 1 from tb where F9<>4)
)t
)t1
)t2 group by rn
go
select * from v1
/*
rn f1 f2 f3 f4 f5 f6 f7
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 3 2 2 3 4 2 6
2 1 3 2 4 2 3 5
3 1 3 2 6 3 4 5
4 2 1 3 2 4 1 3
警告: 聚合或其他 SET 操作消除了 Null 值。
(4 行受影响)
*/
go
drop table tb
--drop view v1
方法,用列转行,将内容拆分,消除都为4的列,然后再根据总行数获得行序号,再作行转列操作.
doubagui 2011-10-17
- 打赏
- 举报
意思就是，如果有9个字段，那么有两个字段的值都是4，其他字段的值是在1-3之间，那么创建的视图是去除了全是4的那两个字段！
doubagui 2011-10-17
- 打赏
- 举报
[Quote=引用 7 楼 qianjin036a 的回复:]
9取7(中间的处理过程比上面的简单一些):
SQL code
create table tb(F1 int,F2 int,F3 int,F4 int,F5 int,F6 int,F7 int,F8 int,F9 int)
insert into tb select 4,3,2,4,2,3,4,2,6
insert into tb select 4,1,3,4,2,4,2,3,5
in……
[/Quote]
要求有4的字段，那么这个字段全是4（其他字段的值都小于4，1-3之间，不存在其他值），这样程序应该可以简单点！
9取7(中间的处理过程比上面的简单一些):
SQL code
create table tb(F1 int,F2 int,F3 int,F4 int,F5 int,F6 int,F7 int,F8 int,F9 int)
insert into tb select 4,3,2,4,2,3,4,2,6
insert into tb select 4,1,3,4,2,4,2,3,5
in……
[/Quote]
要求有4的字段，那么这个字段全是4（其他字段的值都小于4，1-3之间，不存在其他值），这样程序应该可以简单点！
-晴天 2011-10-17
- 打赏
- 举报
9取7(中间的处理过程比上面的简单一些):
create table tb(F1 int,F2 int,F3 int,F4 int,F5 int,F6 int,F7 int,F8 int,F9 int)
insert into tb select 4,3,2,4,2,3,4,2,6
insert into tb select 4,1,3,4,2,4,2,3,5
insert into tb select 4,1,3,4,2,6,3,4,5
insert into tb select 4,2,1,4,3,2,4,1,3
go
create view v1 as
select rn,
max(case when id=1 then f1 end)f1,
max(case when id=2 then f1 end)f2,
max(case when id=3 then f1 end)f3,
max(case when id=4 then f1 end)f4,
max(case when id=5 then f1 end)f5,
max(case when id=6 then f1 end)f6,
max(case when id=7 then f1 end)f7
from(
select (rn1-1)/(select COUNT(*) from tb)+1 id,rn,f1 from(
select row_number()over(order by id,rn)rn1,* from(
select 1 as id,row_number()over(order by(select 1))rn,f1 from tb where exists(select 1 from tb where f1<>4)
union all
select 2,row_number()over(order by(select 1))rn,f2 from tb where exists(select 1 from tb where f2<>4)
union all
select 3,row_number()over(order by(select 1))rn,f3 from tb where exists(select 1 from tb where f3<>4)
union all
select 4,row_number()over(order by(select 1))rn,f4 from tb where exists(select 1 from tb where f4<>4)
union all
select 5,row_number()over(order by(select 1))rn,f5 from tb where exists(select 1 from tb where f5<>4)
union all
select 6,row_number()over(order by(select 1))rn,f6 from tb where exists(select 1 from tb where f6<>4)
union all
select 7,row_number()over(order by(select 1))rn,f7 from tb where exists(select 1 from tb where f7<>4)
union all
select 8,row_number()over(order by(select 1))rn,f8 from tb where exists(select 1 from tb where F8<>4)
union all
select 9,row_number()over(order by(select 1))rn,f9 from tb where exists(select 1 from tb where F9<>4)
)t
)t1
)t2 group by rn
go
select * from v1
/*
rn f1 f2 f3 f4 f5 f6 f7
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 3 2 2 3 4 2 6
2 1 3 2 4 2 3 5
3 1 3 2 6 3 4 5
4 2 1 3 2 4 1 3
警告: 聚合或其他 SET 操作消除了 Null 值。
(4 行受影响)
*/
go
drop table tb
drop view v1
doubagui 2011-10-17
- 打赏
- 举报
因为我又可能是9个字段取7个字段
doubagui 2011-10-17
- 打赏
- 举报
有没有简单点语句搞定
-晴天 2011-10-17
- 打赏
- 举报
写得繁了点儿,但可以用了:
create table tb(F1 int,F2 int,F3 int,F4 int,F5 int)
insert into tb select 4,3,2,4,2
insert into tb select 4,1,3,4,2
insert into tb select 4,1,3,4,2
insert into tb select 4,2,1,4,3
go
create view v1 as
select rn,
max(case when id=1 then f1 end)f1,
max(case when id=2 then f1 end)f2,
max(case when id=3 then f1 end)f3
from(
select (case when rn=id2 then 3 when id1=1 then 1 else 2 end)id,rn,f1 from(
select row_number()over(order by id desc)id2,id1,rn1,id,rn,f1 from(
select (case when rn1=rn then 1 else id end)id1,* from(
select row_number()over(order by id,rn)rn1,* from(
select 1 as id,row_number()over(order by(select 1))rn,f1 from tb where exists(select 1 from tb where f1<>4)
union all
select 2,row_number()over(order by(select 1))rn,f2 from tb where exists(select 1 from tb where f2<>4)
union all
select 3,row_number()over(order by(select 1))rn,f3 from tb where exists(select 1 from tb where f3<>4)
union all
select 4,row_number()over(order by(select 1))rn,f4 from tb where exists(select 1 from tb where f4<>4)
union all
select 5,row_number()over(order by(select 1))rn,f5 from tb where exists(select 1 from tb where f5<>4)
)t
)t1
)t2
)t3
)t4 group by rn
go
select * from v1
/*
rn f1 f2 f3
-------------------- ----------- ----------- -----------
1 3 2 2
2 1 3 2
3 1 3 2
4 2 1 3
警告: 聚合或其他 SET 操作消除了空值。
(4 行受影响)
*/
go
drop table tb
drop view v1中国风 2011-10-17
- 打赏
- 举报
use Tempdb
go
--> -->
if not object_id(N'T') is null
drop table T
Go
Create table T([F1] int,[F2] int,[F3] int,[F4] int,[F5] int)
Insert T
select 4,3,2,4,2 union all
select 4,1,3,4,2 union all
select 4,1,3,4,2
Go
CREATE PROCEDURE p1
as
declare @s nvarchar(2000),@i int
set @i=1
;with b
as
(
Select
*
from T
UNPIVOT
(VALUE FOR COls IN([F1],[F2],[F3],[F4],[F5]))AS b
)
select @s=isnull(@s+',',' select ')+quotename(Cols)+' as T'+rtrim(@i),@i=@i+1 from b where not exists(select 1 from b as c where c.COls=b.COls and VALUE=4) group by Cols
exec(@s+' from T')
go
exec p1
/*
T1 T2 T3
3 2 2
1 3 2
1 3 2
*/中国风 2011-10-17
- 打赏
- 举报
這是存儲過程不是視圖啊
-晴天 2011-10-17
- 打赏
- 举报
因为你的查询列不定,所以:
如果要创建视图,这就比较麻烦了.
如果是要查询,那要用if else 结构来跳转到不同的查询语句,也可以用动态查询.
如果要创建视图,这就比较麻烦了.
如果是要查询,那要用if else 结构来跳转到不同的查询语句,也可以用动态查询.
