ID name date quantity
1 A a 200901 100
2 A a 200902 200
3 A a 200903 300
4 B b 200901 100
5 B b 200902 200
6 B b 200903 300
7 C c 200901 100
8 C c 200902 200
9 C c 200903 300
"备注:1、ID与name列是对应的;
2、要统计出A物品在哪个date中取得最大的quantity,最小的quantity并算出A在所有date中的平均quantity,物品B、C同理;刚接触SQL,请教了!
1 A a 200901 100
2 A a 200902 200
3 A a 200903 300
4 B b 200901 100
5 B b 200902 200
6 B b 200903 300
7 C c 200901 100
8 C c 200902 200
9 C c 200903 300
"备注:1、ID与name列是对应的;
2、要统计出A物品在哪个date中取得最大的quantity,最小的quantity并算出A在所有date中的平均quantity,物品B、C同理;刚接触SQL,请教了!
要统计出A物品在哪个date中取得最大的quantity,最小的quantity并算出A在所有date中的平均quantity,物品B、C同理
--quantity最大
select * from (
select rid=row_number() over (partition by name order by quantity desc),*
from tb
)t where rid=1
--quantity最小
select * from (
select rid=row_number() over (partition by name order by quantity asc),*
from tb
)t where rid=1
--quantity平均
select avg(quantity) from tb
group by name
--平均的改下,还是显示下name吧
select name,avg(quantity) from tb
group by name
select a.ID,a.name,a.date as max_quantity_date,a.quantity as max_quantity
b.date as min_quantity_date,b.quantity as min_quantity,c.quantity as avg_quantity
from
(
select ID,name,date,quantity
from tablename t
where not exists(select 1 from tablename where t.ID=ID and t.name=name and quantity>t.quantity)
)a inner join
(
select ID,name,date,quantity
from tablename t
where not exists(select 1 from tablename where t.ID=ID and t.name=name and quantity<t.quantity)
)b on a.ID=b.ID and a.name=b.name
inner join
(
select ID,name,agv(quantity) as quantity from tablename group by ID,name
) c on a.ID=c.ID and a.name=c.name
搞得定,我正准备把这三个合一个。。回复晚了,不好意思,另5楼貌似少了对a,b,c的定义?我试下,谢谢名位了!!!!
PS:CSDN果然高手如云,嘿嘿,努力学习。。
--建立测试表
create table test_1 (ID char(10),name char(20), date char(30), quantity dec(18,2))
--插入测试数据
insert into test_1 values('A','a','200901',100)
insert into test_1 values( 'A','a','200902',200)
insert into test_1 values('A','a','200903',300)
insert into test_1 values( 'B','b','200901',100)
insert into test_1 values('B','b','200902',200)
insert into test_1 values( 'B','b','200903',300)
insert into test_1 values( 'C','c','200901',100)
insert into test_1 values( 'C','c','200902',200)
insert into test_1 values( 'C','c','200903',300)--查询ID 为A 的最大,最小及平均值 ,各个date中
select date,max(quantity) from test_1 where ID= 'A' group by date
union
select date,min(quantity) from test_1 where ID= 'A' group by date
union
select date,avg(quantity) from test_1 where ID= 'A' group by date---ID 为B和C 只改条件WEHRE ID =‘B’即可
union all
select date,min(quantity) from test_1 where ID= 'A' group by date --最小
union all
select date,avg(quantity) from test_1 where ID= 'A' group by date --平均
'b' 附近有语法错误。
消息 102,级别 15,状态 1,第 8 行
'a' 附近有语法错误。
消息 102,级别 15,状态 1,第 13 行
'b' 附近有语法错误。
消息 102,级别 15,状态 1,第 17 行
'c' 附近有语法错误。我SQL刚入门,还请lxpbs8851指点,请教了!在线等。。
geniuswjt与lxpbs8851各15分,谢谢!!
最终代码如下,MARK!!!select a.ID,a.name,a.date as max_quantity_date,a.quantity as max_quantity,
b.date as min_quantity_date,b.quantity as min_quantity,c.quantity as avg_quantity
from
(
select ID,name,date,quantity
from tablename t
where not exists(select 1 from tablename where t.ID=ID and t.name=name and quantity>t.quantity)
)a inner join
(
select ID,name,date,quantity
from tablename t
where not exists(select 1 from tablename where t.ID=ID and t.name=name and quantity<t.quantity)
)b on a.ID=b.ID and a.name=b.name
inner join
(
select ID,name,avg(quantity) as quantity from tablename group by ID,name
) c on a.ID=c.ID and a.name=c.name