有两个表格 A B
A 表
AID NAME CONTENT
1 张三 你好张三
2 李四 你好李四B 表
BID AID PRICE
1 1 400
2 1 500
3 2 600
4 2 800我要查询的是 A表中的 AID NAME CONTENT B 表中对应的AID PRICE 但只取BID小的一个 如结果为
AID NAME CONTENT PRICE
1 张三 你好张三 400
A 表
AID NAME CONTENT
1 张三 你好张三
2 李四 你好李四B 表
BID AID PRICE
1 1 400
2 1 500
3 2 600
4 2 800我要查询的是 A表中的 AID NAME CONTENT B 表中对应的AID PRICE 但只取BID小的一个 如结果为
AID NAME CONTENT PRICE
1 张三 你好张三 400
A.*,B.PRICE
FROM A,B T
WHERE A.AID=T.AID
AND NOT EXISTS(SELECT 1 FROM B WHERE AID=T.AID AND PRICE<T.PRICE)
A.*,B.PRICE
FROM A,B T
WHERE A.AID=T.AID
AND NOT EXISTS(SELECT 1 FROM B WHERE AID=T.AID AND BID<T.BID)
a.*,b.price
form
a
join
(select aid,min(price) as price from b group by aid)b
on
a.aid=b.aid
(
AID int identity(1,1) primary key,
NAME varchar(30),
CONTENT varchar(30)
)
insert into #A select '张三','你好张三'
insert into #A select '李四','你好李四'create table #B
(
BID int identity(1,1) primary key,
AID int,
PRICE int
)
insert into #B select 1,400
insert into #B select 1,500
insert into #B select 2,600
insert into #B select 2,800select A.*,B.Price from #A A join
(
select AID,PRICE from #B where BID in
(
select top 1 min(BID) BID from #B group by AID
)
) B
on A.AID=B.AID
AID NAME CONTENT Price
----------- ------------------------------ ------------------------------ -----------
1 张三 你好张三 400
AID NAME CONTENT PRICE
1 张三 你好张三 400
3 李四 你好李四 600
如果是这样 就是2楼和3楼 如果单纯是
AID NAME CONTENT PRICE
1 张三 你好张三 400
就是果果的
AID NAME CONTENT PRICE
1 张三 你好张三 400
3 李四 你好李四 600 这个效果
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-15 19:54:34
-- Version:
-- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
-- May 3 2005 23:18:38
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal 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]([AID] int,[NAME] varchar(4),[CONTENT] varchar(8))
insert [A]
select 1,'张三','你好张三' union all
select 2,'李四','你好李四'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([BID] int,[AID] int,[PRICE] int)
insert [B]
select 1,1,400 union all
select 2,1,500 union all
select 3,2,600 union all
select 4,2,800
--------------开始查询--------------------------
select
a.*,b.price
from
a
join
(select aid,min(price) as price from b group by aid)b
on
a.aid=b.aid----------------结果----------------------------
/* AID NAME CONTENT price
----------- ---- -------- -----------
1 张三 你好张三 400
2 李四 你好李四 600(所影响的行数为 2 行)*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-15 19:54:34
-- Version:
-- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
-- May 3 2005 23:18:38
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal 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]([AID] int,[NAME] varchar(4),[CONTENT] varchar(8))
insert [A]
select 1,'张三','你好张三' union all
select 2,'李四','你好李四'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([BID] int,[AID] int,[PRICE] int)
insert [B]
select 1,1,400 union all
select 2,1,500 union all
select 3,2,600 union all
select 4,2,800
--------------开始查询--------------------------
select
a.*,b.price
from
a
join
(select * from b t where not exists(select 1 from b where aid=t.aid and bid<a.bid))b
on
a.aid=b.aid----------------结果----------------------------
/* AID NAME CONTENT price
----------- ---- -------- -----------
1 张三 你好张三 400
2 李四 你好李四 600(所影响的行数为 2 行)*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-15 19:54:34
-- Version:
-- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
-- May 3 2005 23:18:38
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal 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]([AID] int,[NAME] varchar(4),[CONTENT] varchar(8))
insert [A]
select 1,'张三','你好张三' union all
select 2,'李四','你好李四'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([BID] int,[AID] int,[PRICE] int)
insert [B]
select 1,1,400 union all
select 2,1,500 union all
select 3,2,600 union all
select 4,2,800
--------------开始查询--------------------------
select
a.*,b.price
from
a
join
(select * from b t where not exists(select 1 from b where aid=t.aid and bid<t.bid))b
on
a.aid=b.aid----------------结果----------------------------
/* AID NAME CONTENT price
----------- ---- -------- -----------
1 张三 你好张三 400
2 李四 你好李四 600(所影响的行数为 2 行)*/
go
create table [A]([AID] int,[NAME] varchar(4),[CONTENT] varchar(8))
insert [A]
select 1,'张三','你好张三' union all
select 2,'李四','你好李四'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([BID] int,[AID] int,[PRICE] int)
insert [B]
select 1,1,400 union all
select 2,1,500 union all
select 3,2,600 union all
select 4,2,800SELECT
A.*,T.PRICE
FROM A,B T
WHERE A.AID=T.AID
AND NOT EXISTS(SELECT 1 FROM B WHERE AID=T.AID AND BID<T.BID)
(所影响的行数为 2 行)
(所影响的行数为 4 行)AID NAME CONTENT PRICE
----------- ---- -------- -----------
1 张三 你好张三 400
2 李四 你好李四 600(所影响的行数为 2 行)
1 张三 你好张三 1 1 400 就是把 A 表中 和 B表中 BID最小的一张表取出来,然后何必成一张表
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-15 19:54:34
-- Version:
-- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
-- May 3 2005 23:18:38
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal 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]([AID] int,[NAME] varchar(4),[CONTENT] varchar(8))
insert [A]
select 1,'张三','你好张三' union all
select 2,'李四','你好李四'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([BID] int,[AID] int,[PRICE] int)
insert [B]
select 1,1,400 union all
select 2,1,500 union all
select 3,2,600 union all
select 4,2,800
--------------开始查询--------------------------
select * from a join
(
select * from b where BID in
(
select top 1 min(BID) BID from b group by AID
)
) B
on A.AID=B.AID
----------------结果----------------------------
/*AID NAME CONTENT BID AID PRICE
----------- ---- -------- ----------- ----------- -----------
1 张三 你好张三 1 1 400(所影响的行数为 1 行)
*/