很少做数据库项目,最近涉及到,发现真是不会弄,请大家帮个忙,先谢啦!
这样的.
有A表
__________
ID | Para|
----------
1 a
2 b
3 c
4 d
5 e有B表
___________________
AID | Price |State
-------------------
1 200 1
1 100 1
2 150 1
1 400 0
2 200 1
3 200 1
2 600 0现在我想建立一张视图
得到如下结果
__________________
AID | Price |Count
-------------------
5 0 0
4 0 0
3 200 1
1 300 3
2 350 3Price 是 B表中state = 1时的总和, Count 是在 B表中出现的次数,排序是首先按照Price升序, 然后按 Count 升序.
B表AID 是A表ID不知道我说清楚没,请大家看看这个视图SQL语句怎么写啊..
这样的.
有A表
__________
ID | Para|
----------
1 a
2 b
3 c
4 d
5 e有B表
___________________
AID | Price |State
-------------------
1 200 1
1 100 1
2 150 1
1 400 0
2 200 1
3 200 1
2 600 0现在我想建立一张视图
得到如下结果
__________________
AID | Price |Count
-------------------
5 0 0
4 0 0
3 200 1
1 300 3
2 350 3Price 是 B表中state = 1时的总和, Count 是在 B表中出现的次数,排序是首先按照Price升序, 然后按 Count 升序.
B表AID 是A表ID不知道我说清楚没,请大家看看这个视图SQL语句怎么写啊..
insert into a select 1, 'a' union all
select 2, 'b' union all
select 3, 'c' union all
select 4, 'd' union all
select 5, 'e'
create table b (AID int, Price int, State int)
insert into b select 1, 200, 1
union all select 1, 100, 1
union all select 2, 150, 1
union all select 1, 400, 0
union all select 2, 200, 1
union all select 3, 200, 1
union all select 2, 600, 0 select a.id,sum(case when b.state=1 then b.price else 0 end) as p,count(b.aid)
from a left join b on a.id=b.aid group by a.id order by p
/*
id p
----------- ----------- -----------
4 0 0
5 0 0
3 200 1
1 300 3
2 350 3
*/
from a left join b on a.id=b.aid group by a.id order by price,count
--> 测试数据: @tb
declare @tb table ([AID] int,[Price] int,[State] int)
insert into @tb
select 1,200,1 union all
select 1,100,1 union all
select 2,150,1 union all
select 1,400,0 union all
select 2,200,1 union all
select 3,200,1 union all
select 2,600,0
--> 测试数据: @ta
declare @ta table ([ID] int,[Para] varchar(1))
insert into @ta
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 5,'e'select a.ID,isnull(sum(case when b.State=1 then b.Price else 0 end),0),
sum(case when b.State=1 then 1 else 0 end) from
@ta a left join @tb b on a.ID=b.AID
group by a.ID order by a.ID desc
ID
----------- ----------- -----------
5 0 0
4 0 0
3 200 1
2 350 2
1 300 2