菜鸟请教一个SQL写法

PackChen 2009-12-02 01:26:51
销售记录(子表)
Date     Inventory    Quantity      Price
2009-01-01  奔驰      10        100.00
2009-01-01  奔驰      15        150.00
2009-01-01  宝马      5         80.00
2009-01-02  奔驰      15        120.00
2009-01-02  宝马      8         95.00

如何用SELECT得到下面的结果

日      商   平均   最高   最低   销售量最  销售量最
期      品   售价   售价   售价   高的价格  低的价格
2009-01-01  奔驰  125.00  150.00  100.00  150.00   100.00
2009-01-01  宝马  80.00  80.00   80.00  80.00    80.00
2009-01-02  奔驰  120.00  120.00  120.00  120.00   120.00
2009-01-02  宝马  95.00  95.00   95.00  95.00    95.00

我只会写
SELECT
Date,
Inventory,
Avg(Price) AS AvgPrice,
Max(Price) AS MaxPrice,
Min(Price) AS MinPrice,
[如何取对应当天的该商品的最大销售数量的单价和最少销售数量的单价]我这里不会写
FROM
销售记录
WHERE Date BETWEEN '2009-01-01' AND '2009-01-31'
GROUP BY Date,Inventory

请各位大大帮忙教导
...全文
101 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
PackChen 2010-08-25
  • 打赏
  • 举报
回复
对不起,忘记了,等到现在
所有回答的各位都平分分数
yaj52125 2009-12-02
  • 打赏
  • 举报
回复
上面加上
WHERE Date BETWEEN '2009-01-01' AND '2009-01-31'
guo_ke881 2009-12-02
  • 打赏
  • 举报
回复

处理的不错,我也会写了

abercrombie clothing
广州防漏公司
yaj52125 2009-12-02
  • 打赏
  • 举报
回复

SELECT
Date as 日期,
Inventory as 商品,
Avg(Price) AS 平均售价,
Max(Price) AS 最高售价,
Min(Price) AS 最低售价,
(select Price from table_1 A where A.Quantity=(select max(Quantity) from table_1 B where A.Date=B.Date and A.Invertory=B.Inventory)AS 销售量最高的售价,
(select Price from table_1 A where A.Quantity=(select min(Quantity) from table_1 B where A.Date=B.Date and A.Invertory=B.Inventory) AS 销售量最低的售价,
from table_1
GROUP BY Date,Inventory
ming_Y 2009-12-02
  • 打赏
  • 举报
回复
declare @tb table (Date datetime,Inventory varchar(4),Quantity int,Price numeric(5,2))
insert into @tb
select '2009-01-01','奔驰',10,100.00 union all
select '2009-01-01','奔驰',15,150.00 union all
select '2009-01-01','宝马',5,80.00 union all
select '2009-01-02','奔驰',15,120.00 union all
select '2009-01-02','宝马',8,95.00


select Date as 日期,Inventory as 商品,AVG(Price) as 平均售价,MAX(Price) as 最高售价,MIN(Price) as 最低售价,MAX(Price) as 销售量最高的价格,MIN(Price) as 销售量最低的价格
from @tb
where Date BETWEEN '2009-01-01' AND '2009-01-31'
group by Date,Inventory order by Date
--小F-- 2009-12-02
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-02 13:38:06
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Date] datetime,[Inventory] varchar(4),[Quantity] int,[Price] numeric(5,2))
insert [tb]
select '2009-01-01','奔驰',10,100.00 union all
select '2009-01-01','奔驰',15,150.00 union all
select '2009-01-01','宝马',5,80.00 union all
select '2009-01-02','奔驰',15,120.00 union all
select '2009-01-02','宝马',8,95.00
--------------开始查询--------------------------
SELECT
Date,
Inventory,
Avg(Price) AS AvgPrice,
Max(Price) AS MaxPrice,
Min(Price) AS MinPrice,
(select top 1 price from tb t where Price=(select max(Price) from tb where Date=t.Date)) as 最高价格,
(select top 1 price from tb t where Price=(select min(Price) from tb where Date=t.Date)) as 最低价格
FROM
tb
WHERE Date BETWEEN '2009-01-01' AND '2009-01-31'
GROUP BY Date,Inventory
----------------结果----------------------------
/* Date Inventory AvgPrice MaxPrice MinPrice 最高价格 最低价格
----------------------- --------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
2009-01-01 00:00:00.000 宝马 80.000000 80.00 80.00 120.00 80.00
2009-01-02 00:00:00.000 宝马 95.000000 95.00 95.00 120.00 80.00
2009-01-01 00:00:00.000 奔驰 125.000000 150.00 100.00 120.00 80.00
2009-01-02 00:00:00.000 奔驰 120.000000 120.00 120.00 120.00 80.00

(4 行受影响)
*/
--小F-- 2009-12-02
  • 打赏
  • 举报
回复
SELECT 
Date,
Inventory,
Avg(Price) AS AvgPrice,
Max(Price) AS MaxPrice,
Min(Price) AS MinPrice,
(select price from 销售记录 t where  Price=(select max(Price) from 销售记录 where Date=t.Date) as 最高价格,
(select price from 销售记录 t where  Price=(select min(Price) from 销售记录 where Date=t.Date) as 最低价格,
FROM
销售记录
WHERE Date BETWEEN '2009-01-01' AND '2009-01-31'
GROUP BY Date,Inventory
华夏小卒 2009-12-02
  • 打赏
  • 举报
回复
---------------------------------------------
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date : 2009-12-02 13:29:14
--> Version: SQL Server 2005
---------------------------------------------

--> 测试数据: @tb
declare @tb table (Date datetime,Inventory varchar(4),Quantity int,Price numeric(5,2))
insert into @tb
select '2009-01-01','奔驰',10,100.00 union all
select '2009-01-01','奔驰',15,150.00 union all
select '2009-01-01','宝马',5,80.00 union all
select '2009-01-02','奔驰',15,120.00 union all
select '2009-01-02','宝马',8,95.00

SELECT
Date,
Inventory,
Avg(Price) AS AvgPrice,
Max(Price) AS MaxPrice,
Min(Price) AS MinPrice,
(select top 1 Price from @tb where Date=t.Date and Inventory=t.Inventory order by Quantity desc) as p1,
(select top 1 Price from @tb where Date=t.Date and Inventory=t.Inventory order by Quantity asc) as p2
FROM
@tb t
WHERE Date BETWEEN '2009-01-01' AND '2009-01-31'
GROUP BY Date,Inventory
order by date


Date Inventory AvgPrice MaxPrice MinPrice p1 p2
----------------------- --------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
2009-01-01 00:00:00.000 宝马 80.000000 80.00 80.00 80.00 80.00
2009-01-01 00:00:00.000 奔驰 125.000000 150.00 100.00 150.00 100.00
2009-01-02 00:00:00.000 宝马 95.000000 95.00 95.00 95.00 95.00
2009-01-02 00:00:00.000 奔驰 120.000000 120.00 120.00 120.00 120.00

(4 行受影响)

34,571

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧