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
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
覓語 覿蠍磯 豐 襷 . 蠏碁 螳 覲碁る れ螻 螳 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
覿覿 螳覦 蟆郁骸 り 譬 . 蠏碁 螳 SQL覓語 螳 企 2覯 曙伎 觜 覦 蟆企. 蠏碁覩襦 Cross Join朱 螳 蠏碁９ 2螳襯 襷.
SELECT *
FROM Pivot CROSS JOIN (SELECT 1 AS ID UNION ALL SELECT 2) T
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襦 蠏碁９ 讌伎 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
れ 危語 襦 れ螻 螳 貎朱Μ螳 .
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
蟆郁骸襯 覲企 覲, 讌覲, 覲 讌蟇伎 讌螻, 覲 覲 豐讌 蟇伎 覦 讌螳 讌螻, 煙 麹 襷 覯襦 讌螻襯 蟆 .
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
覓語 sqler.pe.kr 覦蟆 覓語(
![[http]](/moniwiki/imgs/http.png)
蠍(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
