表:
ID NAME CODE
001001 张三 320100198506020014
002004 张三 320100198506020014
001002 李四 320100198506020065
003002 李四 320100198506020065
100001 张倩 32010019841014002x
101002 张倩 32010019841014002x
..............现在得到如下表:
id name code id name code
001001 张三 320100198506020014 002004 张三 320100198506020014
001002 李四 320100198506020065 003002 李四 320100198506020065
100001 张倩 32010019841014002x 101002 张倩 32010019841014002x.........
ID NAME CODE
001001 张三 320100198506020014
002004 张三 320100198506020014
001002 李四 320100198506020065
003002 李四 320100198506020065
100001 张倩 32010019841014002x
101002 张倩 32010019841014002x
..............现在得到如下表:
id name code id name code
001001 张三 320100198506020014 002004 张三 320100198506020014
001002 李四 320100198506020065 003002 李四 320100198506020065
100001 张倩 32010019841014002x 101002 张倩 32010019841014002x.........
解决方案 »
- 我有一堆存储过程,想用统一的输出参数值表示返回状态,只能用常数吗?
- 一个统计问题
- 存储过程多条纪录的回滚如何实现
- sql2000 写的存储过程,一般不用改,可以直接在 sql2005 下运行吗?
- 关于几个close()的区别~~~
- select len(nkey) as n from article where n>10这个查询为什么不对
- SQLServer创建脚本的BUG,非常奇怪的问题
- 刚装了MS-SQL Server 2000标准版 可是不会建数据库请各位指教~~~
- sql server 存储过程解密怎么用?
- 一个联合查询,我已经找到一个办法,但不知道是否有更好的办法,大家动动手啊:)
- 求助通过条件判断增加一列计算后的值到新表
- 请教记录的文件存储方式
declare @table table([ID] varchar(6),[NAME] varchar(4),[CODE] varchar(18))
insert @table
select '001001','张三','320100198506020014' union all
select '002004','张三','320100198506020014' union all
select '001002','李四','320100198506020065' union all
select '003002','李四','320100198506020065' union all
select '100001','张倩','32010019841014002x' union all
select '101002','张倩','32010019841014002x'select min(id) as id ,name,code ,
max(id) as id,name,code
from @table group by name,code
--结果
----------------------
100001 张倩 32010019841014002x 101002 张倩 32010019841014002x
001001 张三 320100198506020014 002004 张三 320100198506020014
001002 李四 320100198506020065 003002 李四 320100198506020065
declare @tb table([ID] varchar(6),[NAME] varchar(4),[CODE] varchar(18))
insert @tb
select '001001','张三','320100198506020014' union all
select '002004','张三','320100198506020014' union all
select '001002','李四','320100198506020065' union all
select '003002','李四','320100198506020065' union all
select '100001','张倩','32010019841014002x' union all
select '101002','张倩','32010019841014002x';with cte as
(
SELECT *, RID=ROW_NUMBER() OVER (PARTITION BY [NAME] ORDER BY [NAME]) FROM @tb
)select C1.[ID], C1.[NAME], C1.[CODE], C2.[ID], C2.[NAME], C2.[CODE]
from cte C1 INNER JOIN (SELECT * FROM CTE WHERE RID=2) AS C2 ON C1.NAME = C2.NAME
WHERE C1.RID=1/*
ID NAME CODE ID NAME CODE
------ ---- ------------------ ------ ---- ------------------
001002 李四 320100198506020065 003002 李四 320100198506020065
100001 张倩 32010019841014002x 101002 张倩 32010019841014002x
001001 张三 320100198506020014 002004 张三 320100198506020014
insert tb
select '001001','张三','320100198506020014' union all
select '002004','张三','320100198506020014' union all
select '001002','李四','320100198506020065' union all
select '003002','李四','320100198506020065' union all
select '100001','张倩','32010019841014002x' union all
select '101002','张倩','32010019841014002x'select
max(case px when 1 then id end) id,
max(case px when 1 then name end) name,
code,
max(case px when 2 then id end) id,
max(case px when 2 then name end) name,
code
from
(
select * , px = (select count(1) from tb where NAME = t.NAME and ID < t.ID) + 1 from tb t
) t
group by codedrop table tb/*
id name code id name code
------ ---- ------------------ ------ ---- ------------------
100001 张倩 32010019841014002x 101002 张倩 32010019841014002x
001001 张三 320100198506020014 002004 张三 320100198506020014
001002 李四 320100198506020065 003002 李四 320100198506020065(所影响的行数为 3 行)
*/
--> 测试数据:@table
declare @table table([ID] varchar(6),[NAME] varchar(4),[CODE] varchar(18))
insert @table
select '001001','张三','320100198506020014' union all
select '002004','张三','320100198506020014' union all
select '001002','李四','320100198506020065' union all
select '003002','李四','320100198506020065' union all
select '100001','张倩','32010019841014002x' union all
select '101002','张倩','32010019841014002x'select min(id) as id ,name,code ,
max(id) as id,name,code
from @table group by name,code
having count(*) > 1
order by id
--结果
-----------------------
001001 张三 320100198506020014 002004 张三 320100198506020014
001002 李四 320100198506020065 003002 李四 320100198506020065
100001 张倩 32010019841014002x 101002 张倩 32010019841014002x
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] varchar(6),[NAME] varchar(4),[CODE] varchar(18))
insert [TB]
select '001001','张三','320100198506020014' union all
select '002004','张三','320100198506020014' union all
select '001002','李四','320100198506020065' union all
select '003002','李四','320100198506020065' union all
select '100001','张倩','32010019841014002x' union all
select '101002','张倩','32010019841014002x'
select * from (select *,flag=(select count(1) from TB where t.NAME=NAME and t.id>id) from [TB] t)
A join (select *,flag=(select count(1) from TB where t.NAME=NAME and t.id>id) from [TB] t)B
on A.NAME=B.NAME and A.flag=B.flag+1/*
ID NAME CODE flag ID NAME CODE flag
------ ---- ------------------ ----------- ------ ---- ------------------ -----------
002004 张三 320100198506020014 1 001001 张三 320100198506020014 0
003002 李四 320100198506020065 1 001002 李四 320100198506020065 0
101002 张倩 32010019841014002x 1 100001 张倩 32010019841014002x 0(所影响的行数为 3 行)*/drop table TB
跟你的数据;我的理解是:
1.如果要把所有的name,与code重复的多条显示出来两条,不重复不显示,你可以
select min(id) as id1,name,code, max(id) as id2,name,code from infomation group by name,code having count(id)>1
2.如果要把所有的name,与code重复的多条显示出来两条,不重复也显示,你可以
select min(id) as id1,name,code, max(id) as id2,name,code from infomation group by name,code
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-18 19:34:40
-- 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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] varchar(6),[NAME] varchar(4),[CODE] varchar(18))
insert [tb]
select '001001','张三','320100198506020014' union all
select '002004','张三','320100198506020014' union all
select '001002','李四','320100198506020065' union all
select '003002','李四','320100198506020065' union all
select '100001','张倩','32010019841014002x' union all
select '101002','张倩','32010019841014002x'
--------------开始查询--------------------------select
a.id,a.name,a.code,b.id,b.name,b.code
from
(select id0=row_number()over(partition by NAME order by getdate()) ,* from tb)a
join
(select id0=row_number()over(partition by NAME order by getdate()) ,* from tb)b
on
a.name=b.name and a.id0=b.id0-1
----------------结果----------------------------
/*id name code id name code
------ ---- ------------------ ------ ---- ------------------
001002 李四 320100198506020065 003002 李四 320100198506020065
100001 张倩 32010019841014002x 101002 张倩 32010019841014002x
001001 张三 320100198506020014 002004 张三 320100198506020014(3 行受影响)
*/