Contents

1 覓語
2 ろ 一危
3


1 覓語 #

蠏碁9 豌 覯讌 螳襷 蟆 るジ 覓語 覲 企 れ螻 螳 . 伎 覲企 蟆讌襷 覦覈襦螻 蠍 .
p1.jpg

磯Μ 螳覦 れ螻 螳 襦 蟆郁骸襯 螳語り鍵襯 . ( 蟆郁骸讌 危願 螳讌 谿 螳覦 れ 襾 企ゼ 谿 企 蠍一旧企.)
p2.jpg

2 ろ 一危 #

use tempdb
go

--drop table 覦覈襦
--drop table 覦覈襦蠍
create table 覦覈襦(
	朱 int
,	覦覈襦蠍覯 int
,	覦覈襦伎 nvarchar(500)
)	

create table 覦覈襦蠍(
	覦覈襦蠍覯 int
,	蠍覯 int
,	蠍 nvarchar(200)
)

insert 覦覈襦 values(1,1, '~襾語1');
insert 覦覈襦 values(1,2, '~襾語2');
insert 覦覈襦 values(1,3, '~襾語3');
insert 覦覈襦 values(1,4, '~襾語4');
insert 覦覈襦 values(2,5, '~襾語4');
insert 覦覈襦蠍 values(1, 1, '襾瑚1')
insert 覦覈襦蠍 values(1, 2,  '襾瑚1')
insert 覦覈襦蠍 values(2, 3,  '襾瑚2')
insert 覦覈襦蠍 values(2, 4,  '襾瑚2')
insert 覦覈襦蠍 values(2, 5,  '襾瑚2')
insert 覦覈襦蠍 values(2, 6,  '襾瑚2')
insert 覦覈襦蠍 values(3, 7,  '襾瑚3')

select * from 覦覈襦
select * from 覦覈襦蠍

/* 覈 蟆郁骸讌
覦覈襦蠍覯 覦覈襦伎 蠍覯 蠍伎
4            ~襾語4   null     null
3            ~襾語3   null     null
3            null       7        襾瑚3
2            ~襾語2   null     null
2            null       6        襾瑚2
2            null       5        襾瑚2
2            null       4        襾瑚2
2            null       3        襾瑚2
1            ~襾語1   null     null 
*/

3 #

select
	覦覈襦蠍覯
,	case when 覯 >= 2 then '' else 覦覈襦伎 end 覦覈襦伎
,	蠍覯
,	蠍
from (
	select
		覦覈襦蠍覯
	,	覦覈襦伎
	,	蠍覯
	,	蠍
	,	row_number() over(partition by 覦覈襦蠍覯 order by 覦覈襦蠍覯 desc) 覯
	from (
		select 
			a.覦覈襦蠍覯2 覦覈襦蠍覯
		,	a.覦覈襦伎
		,	b.蠍覯 
		,	b.蠍
		from (
			select 
				case when 蟲覿=2 then 覦覈襦蠍覯 end 覦覈襦蠍覯1
			,	覦覈襦蠍覯 覦覈襦蠍覯2
			,	覦覈襦伎
			,	蟲覿
			from 覦覈襦 cross join (select 1 蟲覿 union all select 2) t 
			where 覦覈襦.朱 = 1
		) a left outer join 覦覈襦蠍 b
		on a.覦覈襦蠍覯1 = b.覦覈襦蠍覯
		group by 
			a.覦覈襦蠍覯2
		,	a.覦覈襦伎
		,	b.蠍覯 
		,	b.蠍
	) t
) t
order by 覦覈襦蠍覯 DESC, 蠍覯

NULL 螻 矩る...
select
	覦覈襦蠍覯
,	isnull(case when 覯 >= 2 then '' else 覦覈襦伎 end, '') 覦覈襦伎
,	isnull(蠍覯, '') 蠍覯
,	isnull(蠍, '') 蠍
from (
	select
		覦覈襦蠍覯
	,	覦覈襦伎
	,	蠍覯
	,	蠍
	,	row_number() over(partition by 覦覈襦蠍覯 order by 覦覈襦蠍覯 desc) 覯
	from (
		select 
			a.覦覈襦蠍覯2 覦覈襦蠍覯
		,	a.覦覈襦伎
		,	b.蠍覯 
		,	b.蠍
		from (
			select 
				case when 蟲覿=2 then 覦覈襦蠍覯 end 覦覈襦蠍覯1
			,	覦覈襦蠍覯 覦覈襦蠍覯2
			,	覦覈襦伎
			,	蟲覿
			from 覦覈襦 cross join (select 1 蟲覿 union all select 2) t 
			where 覦覈襦.朱 = 1
		) a left outer join 覦覈襦蠍 b
		on a.覦覈襦蠍覯1 = b.覦覈襦蠍覯
		group by 
			a.覦覈襦蠍覯2
		,	a.覦覈襦伎
		,	b.蠍覯 
		,	b.蠍
	) t
) t
order by 覦覈襦蠍覯 DESC, 蠍覯