Contents

1
2 所 覲旧″
3 Cross Join 襴
4 1
5 2
6 3


1 #

Cross Join 語^ 豺危螻煙企手 . Cross Join 譟一碁 企朱Μ 螻煙 一一 . 蠏碁覩襦 1 * 1 1企襦 蟯螻螳 企 1:1 蟯螻襷 襷れ 譴る Cross Join 企 覿螳 . Cross Join 糾襯 願鍵 一危 覲旧 譯 伎. 螻燕伎 轟煙 襦 麹 蟆曙郁 譯 . れ Cross Join 蟆企.

蟲蟲1
with temp
as
(
	select 1 id union all
	select 2 id union all
	select 3 id union all
	select 4 id union all
	select 5 id union all
	select 6 id union all
	select 7 id union all
	select 8 id union all
	select 9 id 
)
select  
	cast(a.id + 1 as varchar) + '*' + 
	cast(b.id + 1 as varchar) + '=' + 
	cast((a.id + 1)  * (b.id + 1) as varchar)
from   temp a cross join temp b
order by a.id, b.id

蟲蟲2
with temp
as
(
	select 1 seq
	union all
	select seq + 1 from temp
	where seq + 1 <= 12
)
select
	min(case when md = 1 then val1 end) val1
,	min(case when md = 1 then val2 end) val2
,	min(case when md = 1 then result end) result1

,	min(case when md = 2 then val1 end) val1
,	min(case when md = 2 then val2 end) val2
,	min(case when md = 2 then result end) result2

,	min(case when md = 0 then val1 end) val1
,	min(case when md = 0 then val2 end) val2
,	min(case when md = 0 then result end) result3
from (
	select  
		a.seq val1
	,	b.seq val2
	,	(a.seq)  * (b.seq) result
	,	a.seq % 3 md
	,	row_number() over(partition by a.seq % 3 order by a.seq, b.seq) rowno
	from   temp a cross join temp b
) t cross join temp c
where c.seq <= 3
group by
	rowno

0 ~ 999 覯 襷り鍵
select * into #temp
from (
         select 0 id union all
         select 1 id union all
         select 2 id union all
         select 3 id union all
         select 4 id union all
         select 5 id union all
         select 6 id union all
         select 7 id union all
         select 8 id union all
         select 9 id ) t
 
select cast(cast(c.id  as varchar) + 
          cast(b.id as varchar) + 
          cast(a.id as varchar) as int) number
from #temp a cross join #temp b cross join #temp c
order by 1

2 所 覲旧″ #

譯 蠍磯蓋 企. 伎手鍵 Cross Join 伎 一危 覲旧襦 襷 殊 . 願 蟆郁骸讌 襷り鍵 伎 狩 讌 る Cross Join 伎 一危磯ゼ 覲旧 蠏瑚 覃 . れ 螳 蠏碁 企. 殊 襷 蟆 覲 覲 蟆企.

CREATE TABLE Pivot
( Year      SMALLINT,
  Quarter   TINYINT, 
  Amount      DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO
 
SELECT MIN(CASE WHEN ID = 1 THEN CAST(Year AS VARCHAR) 
ELSE '覿蠍磯 豐' END) AS '', 
    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS '1覿蠍',
    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS '2覿蠍',
    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS '3覿蠍',
    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS '4覿蠍',
    SUM(Amount) '覲 螻'
FROM Pivot CROSS JOIN (SELECT 1 AS ID UNION ALL SELECT 2) T
GROUP BY CASE WHEN ID = 1 THEN Year END
ORDER BY 1

cross_join01.jpg

3 Cross Join 襴 #

れ襦 蟆郁骸襷 豢 覲企 覲蟆 蟆 螳 覲伎碁. 蠏碁 誤 覲企 朱 蟆瑚襯 蟆企. 覲 螻, 企 覿蠍磯 螻, 覈 覿蠍磯 螻蟾讌 蠏碁Μ螻 覈 螻蟾讌 蟆企. 蠏語朱襦 豌 貎朱Μ . 襷覯 襴 GROUP BY . 襴襯 危危襦 . 貎朱Μ 覿蠍磯 豐 觝る れ螻 螳 所 Cross Tab Query襯 . Cross Tab 襴 SUM(CASE ~ ) . SUM()螳 Group 螳 螳襷 覦 蠏 襴螳 .

SELECT Year AS '', 
    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS '1覿蠍',
    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS '2覿蠍',
    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS '3覿蠍',
    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS '4覿蠍',
    SUM(Amount) '覲 螻'
FROM Pivot 
GROUP BY Year

cross_join02.jpg

覓語 覿蠍磯 豐 襷 . 蠏碁 螳 覲碁る れ螻 螳 UNION ALL 磯Μ螳 蟆郁骸襯 襷 .

SELECT CAST(Year AS VARCHAR) AS '', 
    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS '1覿蠍',
    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS '2覿蠍',
    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS '3覿蠍',
    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS '4覿蠍',
    SUM(Amount) '覲 螻'
FROM Pivot 
GROUP BY Year
UNION ALL
SELECT '覿蠍磯 豐', SUM(a), SUM(b), SUM(c), SUM(d), SUM(YearSum)
FROM (
            SELECT Year AS 'Year', 
                SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS 'a',
                SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS 'b',
                SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS 'c',
                SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS 'd',
                SUM(Amount) 'YearSum'
            FROM Pivot 
            GROUP BY Year) T

cross_join03.jpg

覿覿 螳覦 蟆郁骸 り 譬 . 蠏碁 螳 SQL覓語 螳 企 2覯 曙伎 觜 覦 蟆企. 蠏碁覩襦 Cross Join朱 螳 蠏碁9 2螳襯 襷.

SELECT *    
FROM Pivot CROSS JOIN (SELECT 1 AS ID UNION ALL SELECT 2) T

cross_join04.jpg

ID螳 1 讌 蟆曙磯 Year襦 Group By襯 螻,

SELECT 
    MIN(CASE WHEN ID = 1 THEN CAST(Year AS VARCHAR) ELSE '覿蠍磯 豐' END) AS '', 
    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS '1覿蠍',
    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS '2覿蠍',
    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS '3覿蠍',
    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS '4覿蠍',
    SUM(Amount) '覲 螻'
FROM Pivot CROSS JOIN (SELECT 1 AS ID UNION ALL SELECT 2) T
GROUP BY CASE WHEN ID = 1 THEN Year END

ID螳 1 讌 蟆曙磯 NULL . 蠏碁覩襦 GROUP BY襦 蠏碁9 讌伎 ID螳 1 讌 豌願 . 蠏碁覩襦 讌 2 蟆曙磯 豌 讌螻 蟆郁骸螳 る 蟆企.

SELECT MIN(CASE WHEN ID = 1 THEN CAST(Year AS VARCHAR) 
ELSE '覿蠍磯 豐' END) AS '', 
    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS '1覿蠍',
    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS '2覿蠍',
    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS '3覿蠍',
    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS '4覿蠍',
    SUM(Amount) '覲 螻'
FROM Pivot CROSS JOIN (SELECT 1 AS ID UNION ALL SELECT 2) T
GROUP BY NULL

4 1 #

れ 危語 襦 れ螻 螳 貎朱Μ螳 .

SELECT MIN(CASE WHEN T.ID = '1' THEN CAST(a.eDate AS VARCHAR) 
ELSE '覈覲 豐' END) '蠍郁',
        SUM(CASE WHEN b.CommDetail = 'CS' THEN a.sqty ELSE '0' END) '覦豸    ',
        SUM(CASE WHEN b.CommDetail = 'DS' THEN a.sqty ELSE '0' END) '伎豸    ',
        SUM(CASE WHEN b.CommDetail = 'JP' THEN a.sqty ELSE '0' END) '    覦    ',
        SUM(CASE WHEN b.CommDetail = 'NT' THEN a.sqty ELSE '0' END) '      ',
        SUM(CASE WHEN b.CommDetail = 'TS' THEN a.sqty ELSE '0' END) '  豸    ',
        SUM(CASE WHEN b.CommDetail = 'SU' THEN a.sqty ELSE '0' END) '    覲    ',
        SUM(CASE WHEN b.CommDetail = 'JL' THEN a.sqty ELSE '0' END) '螳譯曙覦    ',
        SUM(CASE WHEN b.CommDetail = 'JA' THEN a.sqty ELSE '0' END) '    貅    ',
        SUM(CASE WHEN b.CommDetail = 'VS' THEN a.sqty ELSE '0' END) '譟        ',
        SUM(CASE WHEN b.CommDetail = 'PS' THEN a.sqty ELSE '0' END) '覲給讌    ',
        SUM(CASE WHEN b.CommDetail = 'PD' THEN a.sqty ELSE '0' END) '朱覦讌    ',
        SUM(CASE WHEN b.CommDetail = 'SW' THEN a.sqty ELSE '0' END) '      ',
        SUM(CASE WHEN b.CommDetail = 'OL' THEN a.sqty ELSE '0' END) '襦 貊     ',
        SUM(CASE WHEN b.CommDetail = 'OT' THEN a.sqty ELSE '0' END) '覦 覦 襴    ',
        SUM(CASE WHEN b.CommDetail = 'OH' THEN a.sqty ELSE '0' END) '覦 貊     ',
        SUM(CASE WHEN b.CommDetail = 'AC' THEN a.sqty ELSE '0' END) '語襴    ',
        SUM(CASE WHEN b.CommDetail = 'ES' THEN a.sqty ELSE '0' END) '  襯    ',
        SUM(a.amount) '螻'
FROM
         (SELECT code, MIN(eDate) 'eDate', SUM(sqty) 'sqty', SUM(amount) 'amount' 
         FROM saledetail_m 
         WHERE edate between '2004-04-01' and '2004-04-10' 
         GROUP BY code) a INNER JOIN BaseGoods_Head b
ON a.code = b.code CROSS JOIN (SELECT 1 AS ID UNION ALL SELECT 2) T
WHERE a.edate between '2004-04-01' and '2004-04-30'
GROUP BY  CASE WHEN T.ID = 1 THEN a.eDate END
ORDER BY 1 DESC

れ In-Line View 覿覿 碁殊碁薫螳 螻, 蠏碁 Join朱 郁屋. 螳 蟆郁骸讌 覲願鍵 伎 7豐 螳 . 蠏碁 Group By襯 伎 譟一語 襯 譴朱 5豐襯 渚 . るジ 襦 企 谿 襯 . 3螳 覲旧覲語 襷れ 3螳 覯襦 蠏殊 . 襷 企 一一 觜覯 曙企る 螻豸牛 企 蟲譟磯ゼ 襷 蟆 覦覯 .

select min(case when id in(1, 3) then cast(datepart(yyyy, tsSupportDate) as varchar) 
else '豐 讌 蟇伎' end)'', 
           min(case when id = 1 then tsSupporter 
                           when id = 2 then '------------->'
                           when id = 3 then '------------->' end) '讌', 
           count(case when datepart(mm, tsSupportDate) = '1' then tsSupporter end) '1',
           count(case when datepart(mm, tsSupportDate) = '2' then tsSupporter end) '2',
           count(case when datepart(mm, tsSupportDate) = '3' then tsSupporter end) '3',
           count(case when datepart(mm, tsSupportDate) = '4' then tsSupporter end) '4',
           count(case when datepart(mm, tsSupportDate) = '5' then tsSupporter end) '5',
           count(case when datepart(mm, tsSupportDate) = '6' then tsSupporter end) '6',
           count(case when datepart(mm, tsSupportDate) = '7' then tsSupporter end) '7',
           count(case when datepart(mm, tsSupportDate) = '8' then tsSupporter end) '8',
           count(case when datepart(mm, tsSupportDate) = '9' then tsSupporter end) '9',
           count(case when datepart(mm, tsSupportDate) = '10' then tsSupporter end) '10',
           count(case when datepart(mm, tsSupportDate) = '11' then tsSupporter end) '11',
           count(case when datepart(mm, tsSupportDate) = '12' then tsSupporter end) '12',
           count(*) '豐 讌 蟇伎 螻',
           sum(tsDuration) '豐 讌螳 螻'
from tblTechnicalSupport cross join (select 1 as id union all select 2 union all select 3) tmp
group by  case when id = 1 then tsSupporter end,
                case when id in(1, 3) then datepart(yyyy, tsSupportDate) end
order by 1, 2 desc

cross_join05.jpg

蟆郁骸襯 覲企 覲, 讌覲, 覲 讌蟇伎 讌螻, 覲 覲 豐讌 蟇伎 覦 讌螳 讌螻, 煙 麹 襷 覯襦 讌螻襯 蟆 .

5 2 #

SELECT CASE WHEN ID = 2 THEN CAST(DATEPART(YYYY, SALEDATE) AS VARCHAR) 
ELSE '' END '', 
           MIN(CASE WHEN ID = 1 THEN EMPID ELSE '螻' END) '', 
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '1' THEN EMPID END) '1',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '2' THEN EMPID END) '2',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '3' THEN EMPID END) '3',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '4' THEN EMPID END) '4',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '5' THEN EMPID END) '5',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '6' THEN EMPID END) '6',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '7' THEN EMPID END) '7',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '8' THEN EMPID END) '8',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '9' THEN EMPID END) '9',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '10' THEN EMPID END) '10',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '11' THEN EMPID END) '11',
           COUNT(CASE WHEN DATEPART(MM, SALEDATE) = '12' THEN EMPID END) '12',
           COUNT(*) '襷り 螻'
FROM SALES CROSS JOIN (SELECT 1 AS ID UNION ALL SELECT 2) TMP
GROUP BY  DATEPART(YYYY, SALEDATE), ID,
          CASE WHEN ID = 2 THEN CAST(DATEPART(YYYY, SALEDATE) AS VARCHAR) 
ELSE '' END ,
          CASE WHEN ID = 1 THEN EMPID END
cross_join06.jpg

6 3 #

覓語 sqler.pe.kr 覦蟆 覓語([http](http://sqler.pe.kr/web_board/view_list.asp?id=1218&read=149&pagec=1&gotopage=1&block=0&part=myboard7&tip=ok)). 襷襦 螳覲企 覓語螳 螳碁 觜訣 覦朱 碁 覓語. 覩語蟶 螳 螳襷 企瓦給. (襴襷 危危る 碁企 蟇企讌 讌.)

 蟆郁骸

num         a    b    c    d    e
----------- ---- ---- ---- ---- ----
5                              
4                              
3                             
2                             
1                            
0                           
NULL        1    3    5    0    2
--貎朱Μ 蟆郁骸 ろ碁 覲願 螻蠍語 佔(~~豌)襦 覲企 . 旧 覦蠑碁蟇 蠏谿朱 覃覈レ朱 覲旧伎 覲企 . 
with temp(num)
as
(
        select 6 numm
        union all
        select num - 1 from temp
        where num - 1 >= 0
),
val(a,b,c,d,e)
as
(
	select '1' a, '3' b, '5' c, '0' d, '2' e
)
select 
	case when b.num = 0 then null else b.num - 1 end num
,	case when b.num = 0 then a when b.num - 1 <= a.a then '' else '' end a
,	case when b.num = 0 then b when b.num - 1 <= a.b then '' else '' end b	
,	case when b.num = 0 then c when b.num - 1 <= a.c then '' else '' end c
,	case when b.num = 0 then d when b.num - 1 <= a.d then '' else '' end d
,	case when b.num = 0 then e when b.num - 1 <= a.e then '' else '' end e
from val a
	cross join temp b