如图为一个多表联合后的一个视图,就显示几个典型字段:想实现的效果为得到:
Mid tid xin tuanname
0 13695 ... 工...
1 13694 ... 普吉...
3 13692 ... 武汉...这是昨天发的一个帖子,我可能昨天没表达清楚问题,各位大虾没能够真正解决我的问题。
http://topic.csdn.net/u/20110315/17/49aa5100-4f8f-4b59-9cdc-c2e15426a0fe.html
再次求教了!
Mid tid xin tuanname
0 13695 ... 工...
1 13694 ... 普吉...
3 13692 ... 武汉...这是昨天发的一个帖子,我可能昨天没表达清楚问题,各位大虾没能够真正解决我的问题。
http://topic.csdn.net/u/20110315/17/49aa5100-4f8f-4b59-9cdc-c2e15426a0fe.html
再次求教了!
Mid tid xin tuanname
0 13695 ... 工...
1 13694 ... 普吉...
1 13694 ... 普吉...
1 13694 ... 普吉...
1 13694 ... 普吉...
3 13692 ... 武汉...
这种。
按小F大虾的方法出来的结果是:不同ID,同name,显示最新出现该name的ID,
可是我有很多相同的ID,因为是视图。
from tb t
where not exists (select 1 from tb where tid = t.tid and mid < t.mid)
TID name url
1 中 a
1 中 b
1 中 c
2 中 d
2 中 124
3 我 s3
显示的效果
TID name url
1 中 a
3 我 s3
在这里谢谢各位大虾的帮忙了,不管结论怎么样,学习学习
as (select *,min(tid) over (partition by name) id
from tb)select a.tid,a.name,a.url from cte a where url =
(select top 1 url from tb where name = a.name and tid=a.id)
order by a.tid
insert into tb values (1,'中','a')
insert into tb values (1,'中','b')
insert into tb values (1,'中','c')
insert into tb values (2,'中','d')
insert into tb values (2,'中','124')
insert into tb values (3,'我','s3')with cte
as (select *,min(tid) over (partition by name) id
from tb)select a.tid,a.name,a.url from cte a where url =
(select top 1 url from tb where name = a.name and tid=a.id)
order by a.tid
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U')) --U 代表你查询的是表
DROP TABLE [TB]
GO---->建表
create table [TB]([TID] int,[name] varchar(2),[url] varchar(3))
insert [TB]
select 1,'中','a' union all
select 1,'中','b' union all
select 1,'中','c' union all
select 2,'中','d' union all
select 2,'中','124' union all
select 3,'我','s3'
GO--> 查询结果
;with t as (SELECT *,ROW_NUMBER()over(PARTITION by name order by TID) as px FROM [TB])
select * from t where px=1--> 删除表格
--DROP TABLE [TB]
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-16 17:19:34
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([TID] int,[name] varchar(2),[url] varchar(3))
insert [tb]
select 1,'中','a' union all
select 1,'中','b' union all
select 1,'中','c' union all
select 2,'中','d' union all
select 2,'中','124' union all
select 3,'我','s3'
--------------开始查询--------------------------
select * from (select id=row_number()over(partition by name order by getdate()),* from tb) t where id=1
----------------结果----------------------------
/* id TID name url
-------------------- ----------- ---- ----
1 3 我 s3
1 1 中 a(2 行受影响)
*/
一直报这错 ,呵呵
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-16 17:19:34
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([TID] int,[name] varchar(2),[url] varchar(3))
insert [tb]
select 1,'中','a' union all
select 1,'中','b' union all
select 1,'中','c' union all
select 2,'中','d' union all
select 2,'中','124' union all
select 3,'我','s3'
--------------开始查询--------------------------
select identity(int,1,1) as id0,* into #t from tb
select * from (select id=(select count(distinct name)+1 from #t where name=t.name and id0<t.id0),* from #t t) t where id=1
----------------结果----------------------------
/* id id0 TID name url
----------- ----------- ----------- ---- ----
1 1 1 中 a
1 6 3 我 s3(2 行受影响)
*/
delete a from tb a where exists(select 1 from tb where tid=a.tid and tuanname=a.tuaname and mid<a.mid)