日期:2014-05-17 浏览次数:20829 次
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-22 23:16:52
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
-- Jun 17 2011 00:57:23
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([产品] varchar(4),[单价] numeric(2,1),[数量] int,[最小包装] int,[类型] varchar(8))
insert [huang]
select '铅笔',1.2,5,10,'特价商品' union all
select '铅笔',0.8,2,20,'新品' union all
select '铅笔',1.0,1,5,'一般产品' union all
select '铅笔',1.2,5,15,'一般产品'
--------------开始查询--------------------------
SELECT TOP 1 *
FROM [huang] a
WHERE EXISTS (SELECT 1 FROM (
SELECT [产品],MAX([单价])[单价]
from [huang]
WHERE [产品]='铅笔'
GROUP BY [产品])b WHERE a.[产品]=b.[产品] AND a.[单价]=b.[单价])
----------------结果----------------------------
/*
产品 单价 数量 最小包装 类型
---- --------------------------------------- ----------- ----------- --------
铅笔 1.2 &