單純、幸福

set datefirst 1 -->預設起始日為禮拜一

select (CAST((select (((select datepart(DAYOFYEAR, getdate())) - (select datepart(DAYOFYEAR, (SELECT  dateadd(ms,-3,DATEADD(mm,  DATEDIFF(mm,0,getdate()),0))))) + (select datepart(weekday, '2014/07/01')-1)) / 7) +1 ) as char(1))) -->查出第幾個禮拜

Select 4 stom_id,'去冰,半糖' od_memo Into #D union all
Select 9 stom_id,'' od_memo union all
Select 5 stom_id,'' od_memo union all
Select 6 stom_id,'' od_memo union all
Select 5 stom_id,'無糖,去冰' od_memo union all
Select 6 stom_id,'半糖' od_memo union all
Select 4 stom_id,'無糖' od_memo ;

Select 4 stom_id,N'茉莉綠茶(大)' stom_Item Into #T Union All
Select 5 stom_id,N'茉莉綠茶(中)' stom_Item Union All
Select 6 stom_id,N'阿薩姆紅茶(大)' stom_Item Union All
Select 9 stom_id,N'珍珠奶茶(中)' stom_Item 

--彙整特製品數量
select d.stom_id,od_memo+'('+Convert(varchar,Count(1))+')' as od_memo
Into #SubOD
From #D as d
where od_memo<>''
group by d.stom_id,od_memo



select a.*,t.stom_Item
--字串處理去除結尾'/'字符
,Case When len(b.od_memo)>0 then  substring(b.od_memo,1,len(b.od_memo)-1) else '' end as od_memo 
From 
(
        select d.stom_id,Count(1) as total
        from #D as d 
        group by d.stom_id
)as a
left join 
--特製品
(
        select stom_id,
        (
                --巢狀子查詢帶入XML字串結合
                Select od_memo+'/'
                From #SubOD as a
                where a.stom_id=p.stom_id
                FOR XML PATH('') 
        )as od_memo
        From #SubOD as p
        Group By p.stom_id      
)As b on a.stom_id=b.stom_id
--品名關連
left join #T as t on a.stom_id=t.stom_id

http://www.dotblogs.com.tw/peterdotnet/archive/2010/12/24/20354.aspx

sql世界真是博大精深。

今天又學了一招。

今天我正在苦惱說怎樣把二維陣列丟進DataList裡面，因為顯示文字跟點選值是要塞不一樣的東西。

後來我同事跟我說我可以建立一個虛擬的資料庫，這樣剩下的動作就跟原本的一樣了。

Select TOP 1 Travel_ID From Travel ORDER BY Travel_ID DESC

可選擇該資料表的第一筆。