hh pm sl piao jj
10001 感冒 100 080112 5
10001 感冒 20 091005 4.5
10001 感冒 20 091005 4
10001 感冒 75 090525 6 用什么语句能达到如下:
hh pm sl jj
10001 感冒 215 4取最大批号的最小进价
21号提过一个,当时没想到有同一批号这个情况,惭愧!不知可否?
10001 感冒 100 080112 5
10001 感冒 20 091005 4.5
10001 感冒 20 091005 4
10001 感冒 75 090525 6 用什么语句能达到如下:
hh pm sl jj
10001 感冒 215 4取最大批号的最小进价
21号提过一个,当时没想到有同一批号这个情况,惭愧!不知可否?
from table1
gruop by hh,pm
hh,pm,sum(sl) as sl,
ii=(select min(jj) from tb where piao=(select max(piao) from tb))
from
tb
group by
hh,pm
insert @a select '10001', '感冒', 100 ,'080112', 5
union all select '10001', '感冒', 20 ,'091005', 4.5
union all select '10001', '感冒', 20 ,'091005', 4
union all select '10001', '感冒', 75 ,'090525', 6 SELECT hh,pm,sum(sl) sl,jj=(SELECT TOP 1 jj FROM @a WHERE hh=a.hh AND piao=max(a.piao)) FROM @a a
GROUP BY hh,pm
--result
/*
hh pm sl jj
-------------------- -------------------- ----------- -----------------------------------------------------
10001 感冒 215 4.5(所影响的行数为 1 行)
*/
where piao in (select max(piao) from tb and hh=t.hh)
order by jj asc
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-01-22 16:41:02
-- 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]([hh] int,[pm] varchar(4),[sl] int,[piao] varchar(6),[jj] numeric(2,1))
insert [tb]
select 10001,'感冒',100,'080112',5 union all
select 10001,'感冒',20,'091005',4.5 union all
select 10001,'感冒',20,'091005',4 union all
select 10001,'感冒',75,'090525',6
--------------开始查询--------------------------
select
hh,pm,sum(sl) as sl,
ii=(select min(jj) from tb where piao=(select max(piao) from tb))
from
tb
group by
hh,pm----------------结果----------------------------
/* hh pm sl ii
----------- ---- ----------- ---------------------------------------
10001 感冒 215 4.0(1 行受影响)*/
SELECT hh,pm,sum(sl) sl,jj=(SELECT TOP 1 jj FROM @a WHERE hh=a.hh AND piao=max(a.piao) ORDER BY jj) FROM @a a
GROUP BY hh,pm
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([hh] int,[pm] varchar(4),[sl] int,[piao] varchar(6),[jj] numeric(2,1))
insert [tb]
select 10001,'感冒',100,'080112',5 union all
select 10001,'感冒',20,'091005',4.5 union all
select 10001,'感冒',20,'091005',4 union all
select 10001,'感冒',75,'090525',6
---查询---
select a.hh,a.pm,a.sl,min(b.jj) as jj
from
(select hh,pm,sum(sl) as sl from tb group by hh,pm) a,
(select * from tb t where not exists(select 1 from tb where hh=t.hh and piao>t.piao)) b
where a.hh=b.hh and a.pm=b.pm
group by a.hh,a.pm,a.sl---结果---
hh pm sl jj
----------- ---- ----------- ----
10001 感冒 215 4.0(所影响的行数为 1 行)
if object_id('[tb]') is not null drop table [tb]
create table [tb]([hh] int,[pm] varchar(4),[sl] int,[piao] varchar(6),[jj] numeric(2,1))
insert [tb]
select 10001,'感冒',100,'080112',5 union all
select 10001,'感冒',20,'091005',4.5 union all
select 10001,'感冒',20,'091005',4 union all
select 10001,'感冒',75,'090525',6select top 1 * from tb t
where piao in (select max(piao) from tb where hh=t.hh)
order by jj asc
--或者
select top 1 * from tb t
where jj = (select min(jj) from tb where hh=t.hh and piao=t.piao)
order by piao desc
---------------------------
10001 感冒 20 091005 4.0
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([hh] int,[pm] varchar(4),[sl] int,[piao] varchar(6),[jj] numeric(2,1))
insert [tb]
select 10001,'感冒',100,'080112',5 union all
select 10001,'感冒',20,'091005',4.5 union all
select 10001,'感冒',20,'091005',4 union all
select 10001,'感冒',75,'090525',6select top 1
[hh],[pm],
(select sum(sl) from tb where hh=t.hh) as sl,
[jj]
from tb t
where piao in (select max(piao) from tb where hh=t.hh)
order by jj asc
--或者
select top 1
[hh],[pm],
(select sum(sl) from tb where hh=t.hh) as sl,
[jj]
from tb t
where jj = (select min(jj) from tb where hh=t.hh and piao=t.piao)
order by piao desc
------------------------
10001 感冒 215 4.0