tableA
cls rl
1 123
2 125
3 566tableB
han name id
1 a 2
1 h 3
2 g 4
2 r 5
3 y 6有这样两个表,查询结果是 取tableA.cls=tableB.han ,当满足条件的tableB中有两条记录的时候取ID最小的那条进行组合得出结果,谢谢。
cls rl han name id
1 123 1 a 2
2 125 2 g 4
3 566 3 y 6
cls rl
1 123
2 125
3 566tableB
han name id
1 a 2
1 h 3
2 g 4
2 r 5
3 y 6有这样两个表,查询结果是 取tableA.cls=tableB.han ,当满足条件的tableB中有两条记录的时候取ID最小的那条进行组合得出结果,谢谢。
cls rl han name id
1 123 1 a 2
2 125 2 g 4
3 566 3 y 6
解决方案 »
- 求一Sql语句
- 在线等..'2006-10'字符串我要转换成日期相应的格式 谢谢
- 游标嵌套中,下一个游标如何获得上一个游标的定位?
- SQL SERVER 2000 OpenRowSet-存储过程中动态SQL语句问题
- 新建数据库库的名字不用汉语的解决
- 添加数据时,提示“不能在firehose方式下启动事务”,这是怎么回事?
- 求一个查询语句优化的问题,速度实在是慢
- 使用odbc怎样配置服务器上的sql server 2000的数据库
- 存储过程中使用动态SQL的问题
- SYABSE 11.0.X 下宿主变量类型为CS_DECIMAL,怎么将其加减乘除和用printf打印出来?
- 无法建立新表(在SQL中)
- 数据库查询问题
from ta a
join tb b on a.cls=b.han
where not exists(select 1 from tb where han=b.han and id<b.id)
------------------------------------------------------------------
select a.cls,a.rl,b.han,b.name,b.id
from ta a
join tb b on a.cls=b.han
where id=(select min(id) from tb where han=b.han)
-- Author : htl258(Tony)
-- Date : 2010-03-27 07:25:03
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tAIF NOT OBJECT_ID('[tA]') IS NULL
DROP TABLE [tA]
GO
CREATE TABLE [tA]([cls] INT,[rl] INT)
INSERT [tA]
SELECT 1,123 UNION ALL
SELECT 2,125 UNION ALL
SELECT 3,566
GO
--SELECT * FROM [tA]--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([han] INT,[name] NVARCHAR(10),[id] INT)
INSERT [tb]
SELECT 1,'a',2 UNION ALL
SELECT 1,'h',3 UNION ALL
SELECT 2,'g',4 UNION ALL
SELECT 2,'r',5 UNION ALL
SELECT 3,'y',6
GO
--SELECT * FROM [tb]-->SQL查询如下:
select a.cls,a.rl,b.han,b.name,b.id
from ta a
join tb b on a.cls=b.han
where not exists(select 1 from tb where han=b.han and id<b.id)
------------------------------------------------------------------
select a.cls,a.rl,b.han,b.name,b.id
from ta a
join tb b on a.cls=b.han
where id=(select min(id) from tb where han=b.han)
/*
cls rl han name id
----------- ----------- ----------- ---------- -----------
1 123 1 a 2
2 125 2 g 4
3 566 3 y 6(3 行受影响)cls rl han name id
----------- ----------- ----------- ---------- -----------
1 123 1 a 2
2 125 2 g 4
3 566 3 y 6(3 行受影响)
*/
DROP TABLE [tA]
GO
CREATE TABLE [tA]([cls] INT,[rl] INT)
INSERT [tA]
SELECT 1,123 UNION ALL
SELECT 2,125 UNION ALL
SELECT 3,566
GO
--SELECT * FROM [tA]--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([han] INT,[name] NVARCHAR(10),[id] INT)
INSERT [tb]
SELECT 1,'a',2 UNION ALL
SELECT 1,'h',3 UNION ALL
SELECT 2,'g',4 UNION ALL
SELECT 2,'r',5 UNION ALL
SELECT 3,'y',6
GO
select A.*,B.* from [tA] A
join
(
select * from [tb] b
where not exists(select * from [tb] where [han]=b.[han] and [id]<b.id)
) B
on A.[cls]=b.[han]cls rl han name id
----------- ----------- ----------- ---------- -----------
1 123 1 a 2
2 125 2 g 4
3 566 3 y 6(3 行受影响)
DROP TABLE [tA]
GO
CREATE TABLE [tA]([cls] INT,[rl] INT)
INSERT [tA]
SELECT 1,123 UNION ALL
SELECT 2,125 UNION ALL
SELECT 3,566
GO
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([han] INT,[name] NVARCHAR(10),[id] INT)
INSERT [tb]
SELECT 1,'a',2 UNION ALL
SELECT 1,'h',3 UNION ALL
SELECT 2,'g',4 UNION ALL
SELECT 2,'r',5 UNION ALL
SELECT 3,'y',6
GO
select tA.*,[name],ID
from tA join tb k on tA.cls=k.han
where not exists(select * from tb where k.han=han and k.id>id)
/*
cls rl name ID
----------- ----------- ---------- -----------
1 123 a 2
2 125 g 4
3 566 y 6
*/
pls refer the above
IF NOT OBJECT_ID('[tA]') IS NULL
DROP TABLE [tA]
GO
CREATE TABLE [tA]([cls] INT,[rl] INT)
INSERT [tA]
SELECT 1,123 UNION ALL
SELECT 2,125 UNION ALL
SELECT 3,566
GO
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([han] INT,[name] NVARCHAR(10),[id] INT)
INSERT [tb]
SELECT 1,'a',2 UNION ALL
SELECT 1,'h',3 UNION ALL
SELECT 2,'g',4 UNION ALL
SELECT 2,'r',5 UNION ALL
SELECT 3,'y',6
GO
select tA.*,[name],ID
from tA join tb k on tA.cls=k.han
where not exists(select * from tb where k.han=han and k.id>id)