多字段关联求最大，最小，平均值

[八妹]

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，请教了！

[wencsoft]

5楼才是正解！

[geniuswjt]

--数据给的很扭曲，看不懂，楼下你呢？

[八妹]

不好意思，没对齐。。。我说明下，ID对应A，name对应a,date对应时间，quantity对应最后数量,前面1,2,3为序号，无意义

[geniuswjt]

要统计出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

[geniuswjt]

--平均的改下，还是显示下name吧。。。
select name,avg(quantity) from tb 
group by name

[oO寒枫Oo]

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 

[八妹]

[Quote=引用 3 楼 geniuswjt 的回复:]
SQL code


要统计出A物品在哪个date中取得最大的quantity，最小的quantity并算出A在所有date中的平均quantity,物品B、C同理


--quantity最大
select * from (
select rid=row_number() over (partition by name order by quantity desc),*
fro……
[/Quote]

搞得定，我正准备把这三个合一个。。回复晚了，不好意思，另5楼貌似少了对a,b,c的定义?我试下，谢谢名位了！！！！
PS:CSDN果然高手如云，嘿嘿，努力学习。。

[qq517995084]

--建立测试表
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’即可

[--小F--]

select date,max(quantity) from test_1 where ID= 'A' group by date  --最大
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   --平均

[八妹]

昨天晚上具体测了下3楼和5楼的代码，发现两个问题，一为：3楼求最大、最小值时结果会漏很多行，平均值没问题，5楼除倒数第二行改为AVG外，报错如下：

消息 102，级别 15，状态 1，第 2 行
'b' 附近有语法错误。
消息 102，级别 15，状态 1，第 8 行
'a' 附近有语法错误。
消息 102，级别 15，状态 1，第 13 行
'b' 附近有语法错误。
消息 102，级别 15，状态 1，第 17 行
'c' 附近有语法错误。

我SQL刚入门，还请lxpbs8851指点，请教了！在线等。。

[八妹]

已搞定！！！

a.quantity as max_quantity差个“,”b.date 

我太大意了！！

[八妹]

用实际库测试，5楼正解！！！，在此谢谢以上各位，学习了！！！
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