select A=min(case ID when 1 then Name end) ,B=min(case ID when 2 then Name end) from tabename group by ID
select A=a.name,B=b.name from (select name from tb where id=1)a left join (select name from tb where id=2)b on right(a.name,1)=right(b.name,1)
结果是 A1 NULL NULL B1 不对
sorry,我的写错的,同意 hdhai9451(New New People---新新人类)
但是如果是 ID Name 1 A 1 B 1 C 1 D 2 E 2 F 2 G 这样那就不行了
select t.name A,b.name B from (select name from tab1 where id=1) t left join (select name from tab1 where id=2) b on substr(t.name,2,1) = substr(b.name,2,1); 調過了,能得出你想要的結果!
借用临时表可不可以??select IDD=identity(int,1,1),Name into #T1 from CC where ID=1 Order By Name select IDD=identity(int,1,1),Name into #T2 from CC where ID=2 Order By Name select A=#T1.name,B=#T2.name from #T1 Left Join #T2 On #T1.IDD=#T2.IDD可以实现你的效果
借花献佛了阿! select A=a.name,B= (case when b.name is null then ' ' else b.name end) from (select name from t where id=1)a left join (select name from t where id=2)b on right(a.name,1)=right(b.name,1)
create table t(id int,col2 nvarchar(10)) insert into t select 1,'A1' union all select 1,'A2' union all select 1,'A3' union all select 1,'A4' union all select 2,'B1' union all select 2,'B2' union all select 2,'B3' godeclare @i int declare @pro_bh varchar(10)set @i=1 set @pro_bh = ''select 0 as sid, a.* into #t from t a order by a.col2,newid()update #t set @i=(case when (@pro_bh=id) then @i+1 else 1 end),sid=@i,@pro_bh=id goselect * from #tselect distinct sid,[1]=(select col2 from #t where sid=a.sid and id=1), [2]=(select col2 from #t where sid=a.sid and id=2) from #t adrop table #t drop table t go
请问wlongy2k (Worms) : 你所示的表中,name 这个字段的内容是否可以比较大小?你举例中 A1,A2,A3. A3>A2>A1: a,b,c c>b>a . 如果确认是可以比较大小的,下面的这一条语句就 可以实现:SELECT X.name as A , Y.Name as B FROM ( SELECT count(g.name) ID,f.name FROM (select name from _test1 where id = 1) f, (select name from _test1 where id = 1) g WHERE f.name >= g.name group by f.name ) X LEFT JOIN ( SELECT count(g.name) ID,f.name FROM (select name from _test1 where id = 2) f, (select name from _test1 where id = 2) g WHERE f.name >= g.name group by f.name ) Y ON X.ID = Y.ID
,B=min(case ID when 2 then Name end)
from tabename
group by ID
(select name from tb where id=1)a
left join (select name from tb where id=2)b
on right(a.name,1)=right(b.name,1)
A1 NULL
NULL B1
不对
ID Name
1 A
1 B
1 C
1 D
2 E
2 F
2 G
这样那就不行了
from
(select name from tab1 where id=1) t
left join (select name from tab1 where id=2) b
on substr(t.name,2,1) = substr(b.name,2,1);
調過了,能得出你想要的結果!
select IDD=identity(int,1,1),Name into #T2 from CC where ID=2 Order By Name
select A=#T1.name,B=#T2.name from #T1 Left Join #T2 On #T1.IDD=#T2.IDD可以实现你的效果
select A=a.name,B= (case when b.name is null then ' ' else b.name end) from
(select name from t where id=1)a
left join (select name from t where id=2)b
on right(a.name,1)=right(b.name,1)
insert into t select 1,'A1' union all select 1,'A2' union all select 1,'A3' union all select 1,'A4'
union all select 2,'B1' union all select 2,'B2' union all select 2,'B3'
godeclare @i int
declare @pro_bh varchar(10)set @i=1
set @pro_bh = ''select 0 as sid, a.* into #t from t a order by a.col2,newid()update #t set @i=(case when (@pro_bh=id) then @i+1 else 1 end),sid=@i,@pro_bh=id
goselect *
from #tselect distinct sid,[1]=(select col2 from #t where sid=a.sid and id=1),
[2]=(select col2 from #t where sid=a.sid and id=2)
from #t adrop table #t
drop table t
go
你所示的表中,name 这个字段的内容是否可以比较大小?你举例中 A1,A2,A3.
A3>A2>A1: a,b,c c>b>a . 如果确认是可以比较大小的,下面的这一条语句就
可以实现:SELECT X.name as A , Y.Name as B
FROM
(
SELECT count(g.name) ID,f.name
FROM
(select name from _test1 where id = 1) f,
(select name from _test1 where id = 1) g
WHERE f.name >= g.name
group by f.name
) X
LEFT JOIN
(
SELECT count(g.name) ID,f.name
FROM
(select name from _test1 where id = 2) f,
(select name from _test1 where id = 2) g
WHERE f.name >= g.name
group by f.name
) Y
ON X.ID = Y.ID