2张表，清楚重复，合并问题

[fangyi714]

A表：
ID name class
1 北京游 1
2 北京游 1
3 上海游 2
4 上海游 2
5 武汉游 6

B表：
NID class url
1 1 abc
2 3 ccc
3 2 yui
4 6 www

实现效果：
ID name class url
1 北京游 1 abc
3 上海游 2 yui
5 武汉游 6 www


我想我表达出我的意思了，万分感谢！

[AcHerat]

select c.id,c.name,c.class,b.url
from (select min(id)id,name,class from a group by name,class)c join b on c.class = b.class

[fangyi714]

单独一张表代条件的清楚重复

SELECT *
FROM dbo.Message
WHERE (guo = 0) AND (Mid IN
          (SELECT MIN(Mid)
         FROM Message
         GROUP BY tuanname)) AND (quname <> '武汉')

但多张表的问题就不是很清楚了，主要是想建一个视图

[--小F--]

select
  a.*,b.url
from
  a t,b 
where
  ID=(select id from a where id=(select min(id) from a where name=t.name)
  

[快溜]

select ID,name,class,url
from (
select row_number() over(partition by a.class order by a.id) as no,
a.*,b.url from A表 a left join B表 b on a.class=b.class) a
where a.no=1

[fangyi714]

[Quote=引用 3 楼 fredrickhu 的回复:]
from
a t,b
[/Quote]
这是什么？我菜鸟不是很明白！

[--小F--]

select
  a.*,b.url
from
  a t,b 
where
  t.class=b.class
and
  ID=(select id from a where id=(select min(id) from a where name=t.name)

[--小F--]

[Quote=引用 5 楼 fangyi714 的回复:]
引用 3 楼 fredrickhu 的回复:
from
a t,b

这是什么？我菜鸟不是很明白！
[/Quote]
需要取个别名

[fangyi714]

昨天下班了，呵呵，效果不行啊，还是出现很多重复的

[fangyi714]

[Quote=引用 4 楼 ssp2009 的回复:]
row_number()
[/Quote]
这是个函数吧，我在SQL2003里面报没这个函数

[ljking0731]

select min(a.ID) as ID,a.name,b.class,b.url from A表 as a,B表 as b
where a.ID=b.NID and a.class=b.class
group by a.name,b.class,b.url

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-03-16 09:39:59
-- Verstion:
--      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]([ID] int,[name] varchar(6),[class] int)
insert [A]
select 1,'北京游',1 union all
select 2,'北京游',1 union all
select 3,'上海游',2 union all
select 4,'上海游',2 union all
select 5,'武汉游',6
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([NID] int,[class] int,[url] varchar(3))
insert [B]
select 1,1,'abc' union all
select 2,3,'ccc' union all
select 3,2,'yui' union all
select 4,6,'www'
--------------开始查询--------------------------
select
  t.*,b.url
from
  a t,b 
where
  t.class=b.class
and
  ID=(select id from a where id=(select min(id) from a where name=t.name))
----------------结果----------------------------
/* ID          name   class       url
----------- ------ ----------- ----
1           北京游    1           abc
3           上海游    2           yui
5           武汉游    6           www

(3 行受影响)

*/

[cutebear2008]

CREATE TABLE t1
(
ID INT IDENTITY(1,1),
tname VARCHAR(10),
class INT 
)

CREATE TABLE t2
(
NID INT IDENTITY(1,1),
class INT,
url VARCHAR(10)
)

INSERT INTO t1
SELECT '北京游',1
UNION ALL
SELECT '北京游',1
UNION ALL
SELECT '上海游',2
UNION ALL
SELECT '上海游',2
UNION ALL
SELECT '武汉游',6

INSERT INTO t2
SELECT 1,'abc'
UNION ALL
SELECT 3,'ccc'
UNION ALL
SELECT 2,'yui'
UNION ALL
SELECT 6,'www'

SELECT ID,tname,t1.class,url
FROM t1,t2
WHERE t1.class=t2.class AND ID IN
(
SELECT MIN(ID) FROM t1 GROUP BY tname
)
/*
ID          tname      class       url
----------- ---------- ----------- ----------
1           北京游        1           abc
3           上海游        2           yui
5           武汉游        6           www

(3 row(s) affected)
*/