34,594
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- 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 行受影响)
*/
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
---------------------------------------------
--> 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 行受影响)