表1(产品表:产品名和条码)PName Barcode
N73 oN73
N95 oN95
6300 o6300
表2(产品价格表:id,条码,价格)
ID Barcode Price
1 oN73 1200
2 oN73 1800
3 oN95 2400
4 oN95 2600要求以表1为基准,查询产品的最新价格,即表2中同一产品中ID最大的那个的价格。则此次查询的结果为:
PName Price
N73 1800
N95 2600
6300 NULL
N73 oN73
N95 oN95
6300 o6300
表2(产品价格表:id,条码,价格)
ID Barcode Price
1 oN73 1200
2 oN73 1800
3 oN95 2400
4 oN95 2600要求以表1为基准,查询产品的最新价格,即表2中同一产品中ID最大的那个的价格。则此次查询的结果为:
PName Price
N73 1800
N95 2600
6300 NULL
LEFT JOIN (select * from tb2 as t
where not exists(select * from tb2
where barcode=t.barcode and price > t.price)) AS B
ON A.Barcode = B.Barcode
t1.PName,
t2.Price
from
t1
left join
t2
on
t1.Barcode=t2.Barcode
and
not exists(select 1 from t2 t where t.id>t2.id)
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-10-13 12:35:48
-------------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (PName varchar(4),Barcode varchar(5))
INSERT INTO @tb1
SELECT 'N73','oN73' UNION ALL
SELECT 'N95','oN95' UNION ALL
SELECT '6300','o6300'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (ID int,Barcode varchar(4),Price int)
INSERT INTO @tb2
SELECT 1,'oN73',1200 UNION ALL
SELECT 2,'oN73',1800 UNION ALL
SELECT 3,'oN95',2400 UNION ALL
SELECT 4,'oN95',2600--SQL查询如下:select a.pname,b.price from @tb1 AS A
LEFT JOIN (select * from @tb2 as t
where not exists(select * from @tb2
where barcode=t.barcode and price > t.price)) AS B
ON A.Barcode = B.Barcode/*
pname price
----- -----------
N73 1800
N95 2600
6300 NULL(3 行受影响)
*/
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([PName] varchar(4),[Barcode] varchar(5))
insert [t1]
select 'N73','oN73' union all
select 'N95','oN95' union all
select '6300','o6300'
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([ID] int,[Barcode] varchar(4),[Price] int)
insert [t2]
select 1,'oN73',1200 union all
select 2,'oN73',1800 union all
select 3,'oN95',2400 union all
select 4,'oN95',2600select
t1.PName,
t2.Price
from
t1
left join
t2
on
t1.Barcode=t2.Barcode
and
not exists(select 1 from t2 t where t.Barcode=t2.Barcode and t.id>t2.id)--测试结果:
/*
PName Price
----- -----------
N73 1800
N95 2600
6300 NULL(3 行受影响)*/
大哥,你的SQL语句用起来为什么有重复的内容出现,好像没去重哦!!
on a.Barcode=b.Barcode
但是我想在后面进行Barcode排序好像那不加了去重就不行了。!
我最终要在表1中以barcode排序,请问怎么解决?》
以你提示的,根据上面的大哥写了一个,不知道对不对。。select top 30 a.pname,a.Barcode,b.price from zz_Stock_Barcode AS A
LEFT JOIN (select * from zz_Stock_inProduct as t
where not exists(select * from zz_Stock_inProduct
where barcode=t.barcode and price > t.price)) AS B
ON A.Barcode = B.Barcode group by a.pname,a.Barcode,b.price order by a.Barcode
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-13 12:50:55
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[表1]
if object_id('[表1]') is not null drop table [表1]
go
create table [表1]([PName] varchar(4),[Barcode] varchar(5))
insert [表1]
select 'N73','oN73' union all
select 'N95','oN95' union all
select '6300','o6300'
--> 测试数据:[表2]
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([ID] int,[Barcode] varchar(4),[Price] int)
insert [表2]
select 1,'oN73',1200 union all
select 2,'oN73',1800 union all
select 3,'oN95',2400 union all
select 4,'oN95',2600
--------------开始查询--------------------------
select
t1.PName,
t2.Price
from
表1 t1
left join
表2 t2
on
t1.Barcode=t2.Barcode
and
not exists(select 1 from 表2 t where t.Barcode=t2.Barcode and t.id>t2.id)
order by 1
----------------结果----------------------------
/*PName Price
----- -----------
6300 NULL
N73 1800
N95 2600(3 行受影响)
*/
select
t1.PName,
t2.Price
from
tab1 t1
left join
(select t.Barcode,t.price from tab2 t inner join (select t2.Barcode,max(t2.id) as id from tab2 t2 group by t2.Barcode) as t2 on t.id=t2.id) as t2
on
t1.Barcode=t2.Barcode
t1.PName,
t2.Price
from
t1
left join
t2
on
t1.Barcode=t2.Barcode
and
not exists(select 1 from t2 t where t.Barcode=t2.Barcode and t.id>t2.id)
order by
t1.barcode
引用4楼的