现在要可以发帖子了,怎么搞的.问两个sql问题?表A字段: a b c
对应数据: 1 2 3表B字段: a d f
对应数据: 1 4 5
1 5 6问题
(1)create view a_view as
(select A.b,A.c,B.a,B.d,B.f
where A.a=B.a
)这个想要用B.d来排序,就是上面的后面加order by B.d desc就错误,为什么啊?(2)假如我要实现
create view a_view as
(select A.b,A.c,B.a,B.d,B.f,sum(B.f)
where A.a=B.a
)怎实现啊?就是没行的记录中还有包含一个B.f的sum总数.怎么实现啊?我用group也不知道怎么弄啊?
对应数据: 1 2 3表B字段: a d f
对应数据: 1 4 5
1 5 6问题
(1)create view a_view as
(select A.b,A.c,B.a,B.d,B.f
where A.a=B.a
)这个想要用B.d来排序,就是上面的后面加order by B.d desc就错误,为什么啊?(2)假如我要实现
create view a_view as
(select A.b,A.c,B.a,B.d,B.f,sum(B.f)
where A.a=B.a
)怎实现啊?就是没行的记录中还有包含一个B.f的sum总数.怎么实现啊?我用group也不知道怎么弄啊?
create view a_view
as
select A.b,A.c,B.a,B.d,B.f,sum(B.f)
where A.a=B.a
group by A.b,A.c,B.a,B.d,B.f
SELECT * FROM a_view ORDER BY D
1/create view a_view as
select top 100 percent A.b,A.c,B.a,B.d,B.f --需要加top 100 percent
where A.a=B.a
order by B.d desc
create view a_view as
(select top 100 percent A.b,A.c,B.a,B.d,B.f from W1 A,W2 B
where A.a=B.a order by B.d desc
)--创建视图语法规定排序要有top,它相当于子查询,子查询要排序,需要跟top
2/create view a_view as
select A.b,A.c,B.a,B.d,B.f,sum(B.f) as num
where A.a=B.a
group by A.b,A.c,B.a,B.d
create view b_view as
(select A.b,A.c,B.a,B.d,B.f from W1 A,(select a,d,sum(f)f from W2 group by a,d) B
where A.a=B.a
)
create table dbo.t_2(e varchar(10),d varchar(10),f varchar(10))
go
insert into t_1(a,b,c) values('1','2','3')
go
insert into t_2(e,d,f) values('1','4','5')
insert into t_2(e,d,f) values('1','5','6')
go
create view dbo.t_v as
select t_1.*,t_2.* from t_1 join t_2 on t_1.a=t_2.eselect * from t_v order by t_v.d desc
create view dbo.t_v2 as
select max(t_1.a) as a,sum(convert(integer,t_2.f))as s from t_1 join t_2 on t_1.a=t_2.e group by t_1.aselect * from t_v2
老是提示:
创建视图或函数失败,因为没有为第 x 列指定列名。
就是这个(select a,d,sum(f)f from W2 group by a,d)
create table W1( a int, b int,c int)
insert W1 select 1, 2, 3 create table W2(a int, d int,f int)
insert W2 select 1, 4, 5
insert W2 select 1, 5, 6
select * from view-name order by column