一个查询语句

Question

表goods
日期  品名 规格 批次 重量
2009-01-01 A 3kg 090101 0
2009-01-01 B 1kg 090101 0
2009-01-02 B 1kg 090101 2
2009-01-03 C 2kg 090103 0
2009-01-03 D 2kg 090103 0
2009-01-04 C 2kg 090103 5
2009-01-04 E 5kg 900104 8如果品名、规格、批次都相同，就取重量最大的那条记录。查询结果要如下：
日期  品名 规格 批次 重量
2009-01-01 A 3kg 090101 0
2009-01-02 B 1kg 090101 2
2009-01-04 C 2kg 090103 5
2009-01-03 D 2kg 090103 0
2009-01-04 E 5kg 900104 8
这样的语句该怎样写？

Answer 1

select t.* from tb t where 重量 = (select max(重量) from tb where 品名 = t.品名 and 规格 = t.规格 and 批次 = t.批次) order by t.品名 ,t.规格 ,t.批次select t.* from tb t where not exists (select 1 from tb where 品名 = t.品名 and 规格 = t.规格 and 批次 = t.批次 and 重量 > t.重量) order by t.品名 ,t.规格 ,t.批次

Answer 2

SELECT * FROM TB T 
WHERE 
重量=(SELECT MAX(重量) FROM TB WHERE 品名=T.品名 AND 
规格=T.规格 AND 批次=T.批次)

Answer 3

select * from goods a where not exists
(select 1 from goods where 品名=a.品名 and 规格=a.规格 and 批次=a.批次 and 重量>a.重量)

Answer 4

create table tb(日期 varchar(10), 品名 varchar(10), 规格 varchar(10), 批次 varchar(10), 重量 int)
insert into tb values('2009-01-01' , 'A' , '3kg' , '090101' , 0)
insert into tb values('2009-01-01' , 'B' , '1kg' , '090101' , 0)
insert into tb values('2009-01-02' , 'B' , '1kg' , '090101' , 2)
insert into tb values('2009-01-03' , 'C' , '2kg' , '090103' , 0)
insert into tb values('2009-01-03' , 'D' , '2kg' , '090103' , 0)
insert into tb values('2009-01-04' , 'C' , '2kg' , '090103' , 5)
insert into tb values('2009-01-04' , 'E' , '5kg' , '900104' , 8)
goselect t.* from tb t where 重量 = (select max(重量) from tb where 品名 = t.品名 and 规格 = t.规格 and 批次 = t.批次) order by t.品名 ,t.规格 ,t.批次select t.* from tb t where not exists (select 1 from tb where 品名 = t.品名 and 规格 = t.规格 and 批次 = t.批次 and 重量 > t.重量) order by t.品名 ,t.规格 ,t.批次
drop table tb/*
日期         品名         规格         批次         重量          
---------- ---------- ---------- ---------- ----------- 
2009-01-01 A          3kg        090101     0
2009-01-02 B          1kg        090101     2
2009-01-04 C          2kg        090103     5
2009-01-03 D          2kg        090103     0
2009-01-04 E          5kg        900104     8（所影响的行数为 5 行）日期         品名         规格         批次         重量          
---------- ---------- ---------- ---------- ----------- 
2009-01-01 A          3kg        090101     0
2009-01-02 B          1kg        090101     2
2009-01-04 C          2kg        090103     5
2009-01-03 D          2kg        090103     0
2009-01-04 E          5kg        900104     8（所影响的行数为 5 行）
*/

Answer 5

declare @tb table([日期] datetime,[品名] varchar(1),[规格] varchar(3),[批次] varchar(6),[重量] int)
insert @tb
select '2009-01-01','A','3kg','090101',0 union all
select '2009-01-01','B','1kg','090101',0 union all
select '2009-01-02','B','1kg','090101',2 union all
select '2009-01-03','C','2kg','090103',0 union all
select '2009-01-03','D','2kg','090103',0 union all
select '2009-01-04','C','2kg','090103',5 union all
select '2009-01-04','E','5kg','900104',8select * 
from @tb a
where not exists(select 1 from @tb where a.品名 = 品名 and a.规格=规格 and a.批次 = 批次 and 重量>a.重量 )
ORDER BY 品名,规格,批次
--测试结果:
/*
日期                                                     品名   规格   批次     重量          
------------------------------------------------------ ---- ---- ------ ----------- 
2009-01-01 00:00:00.000                                A    3kg  090101 0
2009-01-02 00:00:00.000                                B    1kg  090101 2
2009-01-04 00:00:00.000                                C    2kg  090103 5
2009-01-03 00:00:00.000                                D    2kg  090103 0
2009-01-04 00:00:00.000                                E    5kg  900104 8（所影响的行数为 5 行）
*/

Answer 6

declare @tb table([日期] datetime,[品名] varchar(1),[规格] varchar(3),[批次] varchar(6),[重量] int)
insert @tb
select '2009-01-01','A','3kg','090101',0 union all
select '2009-01-01','B','1kg','090101',0 union all
select '2009-01-02','B','1kg','090101',2 union all
select '2009-01-03','C','2kg','090103',0 union all
select '2009-01-03','D','2kg','090103',0 union all
select '2009-01-04','C','2kg','090103',5 union all
select '2009-01-04','E','5kg','900104',8--sql2000
select * 
from @tb a
where not exists(select 1 from @tb where a.品名 = 品名 and a.规格=规格 and a.批次 = 批次 and 重量>a.重量 )
ORDER BY 品名,规格,批次--SQL2005
SELECT *
FROM (SELECT * ,ROW_NUMBER() OVER(PARTITION BY 品名,规格,批次 ORDER BY 品名,规格,批次,重量 DESC) AS RID FROM @TB) T
WHERE T.RID = 1
--测试结果:
/*
日期                                                     品名   规格   批次     重量          
------------------------------------------------------ ---- ---- ------ ----------- 
2009-01-01 00:00:00.000                                A    3kg  090101 0
2009-01-02 00:00:00.000                                B    1kg  090101 2
2009-01-04 00:00:00.000                                C    2kg  090103 5
2009-01-03 00:00:00.000                                D    2kg  090103 0
2009-01-04 00:00:00.000                                E    5kg  900104 8（所影响的行数为 5 行）
*/

Answer 7

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-11-23 20:45:04
-- 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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([日期] datetime,[品名] varchar(1),[规格] varchar(3),[批次] varchar(6),[重量] int)
insert [tb]
select '2009-01-01','A','3kg','090101',0 union all
select '2009-01-01','B','1kg','090101',0 union all
select '2009-01-02','B','1kg','090101',2 union all
select '2009-01-03','C','2kg','090103',0 union all
select '2009-01-03','D','2kg','090103',0 union all
select '2009-01-04','C','2kg','090103',5 union all
select '2009-01-04','E','5kg','900104',8
--------------开始查询--------------------------
select 
  * 
from
 tb t 
where 
 重量 =(select max(重量) from tb where 品名 = t.品名 and 规格 = t.规格 and 批次 = t.批次)
order by
  品名,规格,批次----------------结果----------------------------
/* 日期                      品名   规格   批次     重量
----------------------- ---- ---- ------ -----------
2009-01-01 00:00:00.000 A    3kg  090101 0
2009-01-02 00:00:00.000 B    1kg  090101 2
2009-01-04 00:00:00.000 C    2kg  090103 5
2009-01-03 00:00:00.000 D    2kg  090103 0
2009-01-04 00:00:00.000 E    5kg  900104 8(5 行受影响)*/

Answer 8

declare @goods table (日期 datetime,品名 nvarchar(10),
         规格 nvarchar(10),批次 nvarchar(10),重量 int)
insert into @goods select '2009-01-01','A','3kg','090101',0
      union all    select '2009-01-01','A','1kg','090101',0
      union all    select '2009-01-02','B','1kg','090101',2
      union all    select '2009-01-03','C','2kg','090103',0
      union all    select '2009-01-03','D','2kg','090103',0
      union all    select '2009-01-04','C','2kg','090103',5
      union all    select '2009-01-04','E','5kg','900104',8
select * from @goods a where not exists 
          (select 1 from @goods where a.品名=品名 and a.规格=规格 and 重量>a.重量)
/*
日期                      品名         规格         批次         重量
----------------------- ---------- ---------- ---------- -----------
2009-01-01 00:00:00.000 A          3kg        090101     0
2009-01-02 00:00:00.000 B          1kg        090101     2
2009-01-03 00:00:00.000 D          2kg        090103     0
2009-01-04 00:00:00.000 C          2kg        090103     5
2009-01-04 00:00:00.000 E          5kg        900104     8(5 行受影响)*/