高手达人救急:一张表两字段:ID(主键),Name(目前只考虑每条记录Name不相同,高手可考虑Name相同情况下达到效果),想要实现效果:每个ID只返回ID相同,Name不同的两条记录,不受该表记录行数影响。)(任何正确方法都行:自连接啥的...)?
想要效果:ID Name
ID001 林青霞
ID001 张曼玉
ID002 张飞
ID002 肖恩.康纳利
想要效果:ID Name
ID001 林青霞
ID001 张曼玉
ID002 张飞
ID002 肖恩.康纳利
select ID,Name from (select idd=row_number()over(partition by ID order by getdate()),* from tb)t where idd<=2
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int,name varchar(6))
insert into #tb
select 1,'a' union all
select 1,'b' union all
select 1,'按时的' union all
select 2,'的' union all
select 2,'的' union all
select 2,'个' union all
select 3,'热'select * from #tb t
where (select count(*) from #tb where id=t.id and name<t.name)<2id name
----------- ------
1 a
1 b
2 的
2 的
3 热(5 行受影响)
select t.* from tb t where name in (select top 2 name from tb where id = t.id order by name)select t.* from tb t where name in (select top 2 name from tb where id = t.id order by name desc)--id + name存在相同
--sql 2000
select id , name from
(
select t.* , px = (select count(distinct name) from tb where id = t.id and name < t.name) + 1 from tb t
) m
where px <= 2select id , name from
(
select t.* , px = (select count(distinct name) from tb where id = t.id and name > t.name) + 1 from tb t
) m
where px <= 2--sql 2005
select id , name from
(
select t.* , px = row_number() over(partition by id order by name) from tb t
) m
where px <= 2select id , name from
(
select t.* , px = row_number() over(partition by id order by name desc) from tb t
) m
where px <= 2
create table tb (id varchar(10),name varchar(20))
insert into tb
select 'ID001','林青霞' union all
select'ID001', '林青霞'union all
select'ID002' ,'张飞'union all
select'ID002' ,'肖恩.康纳利'
select count(distinct name ),id from tb group by id having count(distinct name ) >1
一行数据的(即已存在返回两行里的一行数据):要查询的表初始情况:
ID Name
ID001 林青霞
ID002 张飞
现在写SQL实现下面效果(这是伪命题么?我晕了!):ID Name
ID001 林青霞
ID001 张曼玉
ID002 张飞
ID002 肖恩.康纳利
TB )
SELECT
ROW_NUMBER() OVER( ORDER BY t1.Id ASC ) AS SQLRowNumber ,t1.Id,
Name =CASE WHEN t1.Name = t2.Name THEN rd1.Name + '_View'
WHEN t1.Name != t2.Name THEN rd1.Name + '_Audit'
ELSE NULL END
FROM tb t1 JOIN tb t2
ON t1.Name !=t2.Name ORDER BY t1.Id
(这是我自己写的,丫的,这样写WHEN里面的判断是否问题的,纠结中...看来得操作DATASET了...)
--那个on 条件似乎有问题。
SELECT
ROW_NUMBER() OVER( ORDER BY t1.Id ASC ) AS SQLRowNumber ,t1.Id,
Name =CASE WHEN t1.Name = t2.Name THEN rd1.Name + '_View'
WHEN t1.Name != t2.Name THEN rd1.Name + '_Audit'
ELSE NULL END
FROM tb t1 full JOIN tb t2
ON t1.Name !=t2.Name ORDER BY t1.Id