_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › 覲豌襴覓語

れ螻 螳 襯 襷り..
create function dbo.ufn_game_weekly (@bdt date, @edt date)
returns table
as
return (
    select 
        蟆
    ,   sum(蟆襾碁) 蟆襾碁
    from 誤企
    where 讌 between @bdt and @edt
    group by
        蟆
)
go

select dbo.ufn_game_weekly ('20120801', '20120807') 
--願唄 覲豌襴.

襯 伎 れ螻 螳 譯殊姶覲襦 cross apply , 覲豌襴螳 蟆曙郁 . 企蟆 襯 伎 伎 group by 貉朱朱 group 朱 覓玖鍵 伎. 覿 貉殊 group by 蠍 企.
select
    a.begin_dt
,   a.end_dt
,   b.蟆
,   b.蟆襾碁
from (
    select distinct 
        convert(date, DATEADD(dd, CONVERT(int, convert(datetime, date_key), 112) % 7 * -1, date_key)) begin_dt
    ,   convert(date, DATEADD(dd, 7 - (CONVERT(int, convert(datetime, date_key), 112) % 7 + 1 ), date_key)) end_dt
    from dm.dim.date a --願唄 讌 企
    where 1=1
    and date_key between convert(char(8), DATEADD(dd, CONVERT(int, convert(datetime, '20120801'), 112) % 7 * -1, '20120801'), 112)
    and convert(char(8), DATEADD(dd, (CONVERT(int, convert(datetime, '20120904'), 112) % 7 + 1) * -1, '20120904'), 112)    
) a
    cross apply reader.dbo.ufn_game_weekly(begin_dt, end_dt) b
go

企 蟆曙一 れ螻 螳 企 xml襦 蟆郁骸襯 襷り 襴危. 蠏碁 覲豌襴 . 企 糾螳 豕語 讌 誤讌 .

--drop function dbo.ufn_game_weekly 
create function dbo.ufn_game_weekly (@bdt date, @edt date)
returns @rs table
(
    蟆 int
,   蟆襾碁 bigint
)
as
    declare @x xml
    set @x = (
        select 
            蟆
        ,   sum(蟆襾碁) 蟆襾碁
        from 誤企
        where 讌 between @bdt and @edt
        group by
            蟆
        for xml path('row')
    )

    insert @rs
    select 
        y.item.value('(蟆)[1]', 'smallint')
    ,   y.item.value('(蟆襾碁)[1]', 'bigint')
    from (select @x xitem) x
        cross apply x.xitem.nodes('/row') as y(item)

    return;
go

覦 覦 覃伎 瑚る 谿場 襭企.
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:54

覈伎 蠍語伎? 蠏瑚碓. 螳 殊伎. れ螻 れ螻 れ れ朱 譟郁襷 れ 譟郁襷 れ 譟郁襷 れ 螳覃 蟇一. <伎危 語 (Grooks 1) 譴>