22,209
社区成员
发帖
与我相关
我的任务
分享
create table [A]([ProductID] int,[a_price] numeric(4,2),[userid] int,[productName] varchar(16),[p_price] numeric(4,2))
insert [A]
select 8577,10.00,422,'StampP3-20091102',10.00 union all
select 8577,12.00,422,'StampP3-20091102',10.00 union all
select 8577,14.00,422,'StampP3-20091102',10.00 union all
select 8577,16.00,422,'StampP3-20091102',10.00 union all
select 8576,1.00,422,'StampP2-20091102',1.00 union all
select 8575,5.00,422,'StampP1-20091102',1.00 union all
select 8575,3.00,422,'StampP1-20091102',1.00 union all
select 8575,1.00,422,'StampP1-20091102',1.00
create table [B]([productID] int,[p_price] numeric(4,2),[userid] int)
insert [B]
select 8577,10.00,422 union all
select 8577,12.00,422 union all
select 8577,14.00,422 union all
select 8577,16.00,422 union all
select 8576,1.00,422 union all
select 8575,5.00,422 union all
select 8575,3.00,422 union all
select 8575,1.00,422
select a.* , maxpirce = (select max(p_price) from b where ProductID = a.ProductID) from a
drop table a , b
/*
ProductID a_price userid productName p_price maxpirce
----------- ------- ----------- ---------------- ------- --------
8577 10.00 422 StampP3-20091102 10.00 16.00
8577 12.00 422 StampP3-20091102 10.00 16.00
8577 14.00 422 StampP3-20091102 10.00 16.00
8577 16.00 422 StampP3-20091102 10.00 16.00
8576 1.00 422 StampP2-20091102 1.00 1.00
8575 5.00 422 StampP1-20091102 1.00 5.00
8575 3.00 422 StampP1-20091102 1.00 5.00
8575 1.00 422 StampP1-20091102 1.00 5.00
(所影响的行数为 8 行)
*/
select A.*, (select top 1 [p_price] from B where b.[productID] = A.[productID] and b.[userid] = a. [userid] order by b.[p_price] desc)
from A
/*
ProductID a_price userid productName p_price
----------- --------------------------------------- ----------- ---------------- --------------------------------------- ---------------------------------------
8577 10.00 422 StampP3-20091102 10.00 16.00
8577 12.00 422 StampP3-20091102 10.00 16.00
8577 14.00 422 StampP3-20091102 10.00 16.00
8577 16.00 422 StampP3-20091102 10.00 16.00
8576 1.00 422 StampP2-20091102 1.00 1.00
8575 5.00 422 StampP1-20091102 1.00 5.00
8575 3.00 422 StampP1-20091102 1.00 5.00
8575 1.00 422 StampP1-20091102 1.00 5.00
*/
if object_id('[A]') is not null drop table [A]
go
create table [A]([ProductID] int,[a_price] numeric(4,2),[userid] int,[productName] varchar(16),[p_price] numeric(4,2))
insert [A]
select 8577,10.00,422,'StampP3-20091102',10.00 union all
select 8577,12.00,422,'StampP3-20091102',10.00 union all
select 8577,14.00,422,'StampP3-20091102',10.00 union all
select 8577,16.00,422,'StampP3-20091102',10.00 union all
select 8576,1.00,422,'StampP2-20091102',1.00 union all
select 8575,5.00,422,'StampP1-20091102',1.00 union all
select 8575,3.00,422,'StampP1-20091102',1.00 union all
select 8575,1.00,422,'StampP1-20091102',1.00
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([productID] int,[p_price] numeric(4,2),[userid] int)
insert [B]
select 8577,10.00,422 union all
select 8577,12.00,422 union all
select 8577,14.00,422 union all
select 8577,16.00,422 union all
select 8576,1.00,422 union all
select 8575,5.00,422 union all
select 8575,3.00,422 union all
select 8575,1.00,422
Select A.*,maxprice
From A
inner join
(Select productid,maxprice=MAX(p_price) from b group by productid
) B on A.productid=B.productid
/*8575 5.00 422 StampP1-20091102 1.00 5.00
8575 3.00 422 StampP1-20091102 1.00 5.00
8575 1.00 422 StampP1-20091102 1.00 5.00
8576 1.00 422 StampP2-20091102 1.00 1.00
8577 10.00 422 StampP3-20091102 10.00 16.00
8577 12.00 422 StampP3-20091102 10.00 16.00
8577 14.00 422 StampP3-20091102 10.00 16.00
8577 16.00 422 StampP3-20091102 10.00 16.00*/
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([ProductID] int,[a_price] numeric(4,2),[userid] int,[productName] varchar(16),[p_price] numeric(4,2))
insert [A]
select 8577,10.00,422,'StampP3-20091102',10.00 union all
select 8577,12.00,422,'StampP3-20091102',10.00 union all
select 8577,14.00,422,'StampP3-20091102',10.00 union all
select 8577,16.00,422,'StampP3-20091102',10.00 union all
select 8576,1.00,422,'StampP2-20091102',1.00 union all
select 8575,5.00,422,'StampP1-20091102',1.00 union all
select 8575,3.00,422,'StampP1-20091102',1.00 union all
select 8575,1.00,422,'StampP1-20091102',1.00
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([productID] int,[p_price] numeric(4,2),[userid] int)
insert [B]
select 8577,10.00,422 union all
select 8577,12.00,422 union all
select 8577,14.00,422 union all
select 8577,16.00,422 union all
select 8576,1.00,422 union all
select 8575,5.00,422 union all
select 8575,3.00,422 union all
select 8575,1.00,422
SELECT DISTINCT
A.*,
(SELECT MAX(P_PRICE) FROM B
WHERE productID=T.productID AND userid=T.userid GROUP BY productID,userid)AS MAXPRICE
FROM A,B T WHERE A.ProductID=T.ProductID AND A.userid=T.userid
ORDER BY A.p_price DESC,MAXPRICE
(所影响的行数为 8 行)
(所影响的行数为 8 行)
ProductID a_price userid productName p_price MAXPRICE
----------- ------- ----------- ---------------- ------- --------
8577 10.00 422 StampP3-20091102 10.00 16.00
8577 12.00 422 StampP3-20091102 10.00 16.00
8577 14.00 422 StampP3-20091102 10.00 16.00
8577 16.00 422 StampP3-20091102 10.00 16.00
8576 1.00 422 StampP2-20091102 1.00 1.00
8575 1.00 422 StampP1-20091102 1.00 5.00
8575 3.00 422 StampP1-20091102 1.00 5.00
8575 5.00 422 StampP1-20091102 1.00 5.00
(所影响的行数为 8 行)
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-11 19:41:46
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([ProductID] int,[a_price] numeric(4,2),[userid] int,[productName] varchar(16),[p_price] numeric(4,2))
insert [A]
select 8577,10.00,422,'StampP3-20091102',10.00 union all
select 8577,12.00,422,'StampP3-20091102',10.00 union all
select 8577,14.00,422,'StampP3-20091102',10.00 union all
select 8577,16.00,422,'StampP3-20091102',10.00 union all
select 8576,1.00,422,'StampP2-20091102',1.00 union all
select 8575,5.00,422,'StampP1-20091102',1.00 union all
select 8575,3.00,422,'StampP1-20091102',1.00 union all
select 8575,1.00,422,'StampP1-20091102',1.00
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([productID] int,[p_price] numeric(4,2),[userid] int)
insert [B]
select 8577,10.00,422 union all
select 8577,12.00,422 union all
select 8577,14.00,422 union all
select 8577,16.00,422 union all
select 8576,1.00,422 union all
select 8575,5.00,422 union all
select 8575,3.00,422 union all
select 8575,1.00,422
--------------开始查询--------------------------
select
a.*,b.[p_price]
from
a
join
(Select productID,max(p_price) as p_price from b group by productID )b
on
a.productID=b.productID
----------------结果----------------------------
/*
(8 行受影响)
(8 行受影响)
ProductID a_price userid productName p_price p_price
----------- --------------------------------------- ----------- ---------------- --------------------------------------- ---------------------------------------
8575 5.00 422 StampP1-20091102 1.00 5.00
8575 3.00 422 StampP1-20091102 1.00 5.00
8575 1.00 422 StampP1-20091102 1.00 5.00
8576 1.00 422 StampP2-20091102 1.00 1.00
8577 10.00 422 StampP3-20091102 10.00 16.00
8577 12.00 422 StampP3-20091102 10.00 16.00
8577 14.00 422 StampP3-20091102 10.00 16.00
8577 16.00 422 StampP3-20091102 10.00 16.00
(8 行受影响)
*/
SELECT
A.*,
(SELECT MAX(P_PRICE) FROM B GROUP BY productID,USRID
WHERE productID=T.productID AND USRID=T.USERID)AS MAXPRICE
FROM A,B T WHERE A.ProductID=T.ProductID AND A.USERID=T.USERID
SELECT
A.*,
(SELECT MAX(PRICE) FROM B GROUP BY productID,USRID
WHERE productID=T.productID AND USRID=T.USERID)AS MAXPRICE
FROM A,B T WHERE A.ProductID=T.ProductID AND A.USERID=T.USERID