22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE a(id int,productname varchar(10))
insert into a
select 1, 'A' union all
select 2 ,'B' union all
select 3 ,'C'
create table b(id int ,productname varchar(10),color nvarchar(10))
insert into b
select 1, 'A', N'红' UNION ALL
select 1, 'A', N'黄' UNION ALL
select 2, 'B', N'红' UNION ALL
select 3, 'C', N'红' UNION ALL
select 3, 'C', N'绿' UNION ALL
select 3, 'C', N'黑'
with t
as(
select ret=row_number() over(partition by a.id order by a.id), a.id,a.productname, b.color from a right join b on a.id=b.id)
select case when ret=1 then id else null end,case when ret=1 then productname else null end ,color from t
create table t2(id int,product varchar(5),color varchar(10))
insert into t2
select 1,'a','red' union all
select 1,'a','yellow' union all
select 2,'b','blue' union all
select 2,'b','green' union all
select 2,'b','red' union all
select 3,'c','red' union all
select 3,'c','blue'
go
declare @a int,@b int
select @a=0,@b=0
update t2 set @a=@b,@b=id,product=case when id<>@a then product end, id=case when id<>@a then id end
from t2
select* from t2
drop table t2
/*
id product color
----------- ------- ----------
1 a red
NULL NULL yellow
2 b blue
NULL NULL green
NULL NULL red
3 c red
NULL NULL blue
(所影响的行数为 7 行)
*/
--排序后的结果集的样式应该是:
/*
id product color rid
----------- ------- ---------- --------------------
1 a red 1
1 a yellow 2
2 b blue 1
2 b green 2
2 b red 3
3 c red 1
3 c blue 2
*/
--那么将上边rid为1也就是序号为1的id和product显示,其他显示为null,这里用到case when。
--也就是上面几楼给出的语句,思想是这样。
create table t1(id int,product varchar(5))
insert into t1
select 1,'a' union all
select 2,'b' union all
select 3,'c'
go
create table t2(id int,product varchar(5),color varchar(10))
insert into t2
select 1,'a','red' union all
select 1,'a','yellow' union all
select 2,'b','blue' union all
select 2,'b','green' union all
select 2,'b','red' union all
select 3,'c','red' union all
select 3,'c','blue'
go
--SQL2005
with cte as
(
select a.id,a.product,b.color,px=row_number() over (partition by a.id,a.product order by getdate())
from t1 a join t2 b on a.id = b.id
)
select (case when px=1 then id else null end) as id,
(case when px=1 then product else null end) as product,color
from cte
/*
id product color
----------- ------- ----------
1 a red
NULL NULL yellow
2 b blue
NULL NULL green
NULL NULL red
3 c red
NULL NULL blue
(7 行受影响)
*/
--SQL200
select *,rid = identity(int,1,1)
into #tb
from t2
select (case when (select count(*) from #tb where id = t.id and rid <= t.rid)=1 then id else null end) as id,
(case when (select count(*) from #tb where id = t.id and rid <= t.rid)=1 then product else null end) as product,color
from #tb t
/*
id product color
----------- ------- ----------
1 a red
NULL NULL yellow
2 b blue
NULL NULL green
NULL NULL red
3 c red
NULL NULL blue
(7 行受影响)
*/
drop table t1,t2,#tb
declare @数据1 table (ID int,产品 varchar(1))
insert into @数据1
select 1,'A' union all
select 2,'B' union all
select 3,'C'
declare @数据2 table (ID int,产品ID varchar(1),颜色 varchar(2))
insert into @数据2
select 1,'A','红' union all
select 1,'A','黄' union all
select 2,'B','红' union all
select 3,'C','红' union all
select 3,'C','绿' union all
select 3,'C','黑'
select b.*,a.颜色 from
(select row_number() over (partition by 产品ID order by ID) as rid,* from @数据2) a
left join @数据1 b on a.产品ID=b.产品 and a.rid=1
/*
ID 产品 颜色
----------- ---- ----
1 A 红
NULL NULL 黄
2 B 红
3 C 红
NULL NULL 绿
NULL NULL 黑
*/
with cte as
(
select a.id,a.product,b.color,px=row_number() over (partition by a.id,a.product order by getdate())
from a join b on a.id = b.id
)
select (case when px=1 then id else '' end) as id,
(case when px=1 then product else '' end) as product,color
from cte