T-sql 查询

------------------case---when-------的使用---------------------

select  userAccount , eatingDate ,
    case when sum(a)=1 then'早餐' else '' end   as 'breakfast' ,
    case when sum(b)=1 then '午餐' else '' end  as 'lunch',
    case when sum(c)=1 then '晚餐' else '' end  as 'dinner'
    from (
        select  userAccount , eatingDate ,
           case when menuType='早餐' then 1 else 0 end  AS a ,
           case when menuType='午餐' then 1 else 0 end  AS b ,
           case when menuType='晚餐' then 1 else 0 end  AS c
        from view_menuReservation
           where userAccount = '7923'
           and eatingDate between '2016-12-01' and'2016-12-31'
    ) t  
    group by userAccount ,eatingDate
    order by eatingDate asc

---------------------查询的时候嵌套子查询-----------------------------------------

SELECT tb.account, '2016-10'  month,
    ( max(CASE signStatus WHEN '准时' THEN total ELSE 0 END )+
     max(CASE signStatus WHEN '调休' THEN total ELSE 0 END)+
     max(CASE signStatus WHEN '迟到' THEN total ELSE 0 END)
     ) sumDay,
    (max(CASE signStatus WHEN '准时' THEN total ELSE 0 END)
   + max(CASE signStatus WHEN '调休' THEN total ELSE 0 END)) onTime,
     max(CASE signStatus WHEN '迟到' THEN total ELSE 0 END) late,
     max(CASE signStatus WHEN '请假' THEN total ELSE 0 END) leave,
     max(CASE signStatus WHEN '早退' THEN total ELSE 0 END) absenteeism,
    ( select count(*) from tb_workAttendance
where workStatus='加班' and
docdate between '2016-10-1' and '2016-10-31' and account = tb.account and signType='上班'  group by workStatus) overtime,
    0 dayOff,
    (select sum (totalHour) totalHour  from tb_restApply where  docdate between '2016-11-01' and '2016-11-30' and account=tb.account group by account) totalHour
     from(
     select count(1) total, signStatus, account
     from tb_workAttendance
     where docdate between '2016-10-1' and '2016-10-31'
     group by signStatus,account)
     tb
     group by account

 

----------------------sql循环-------------------------------------------------------------

 declare @Num int;


set @Num = 1 ;
while @Num< 10
BEGIN
    select '1';
    set @Num = @Num +1 ;
END

-----------------T sql--------批量处理修改数据 数据来源别的关联表------------------------------

 update GL_Journal  set madedate=b.madedate  from
(select madedate,id,accountingperiod   from GL_Doc where maker ='admin' and accountingperiod ='12'  ) b
inner join GL_Journal a on a.docid =b.id

 -------------------------------------------------------数据表格的备份

select *  into   dddd from aaaa

 -------------------------SQL 查询数据 后转换成 xml 的数据

select top 2 * from jkpt_order_mx FOR xml path('item'),root('xml')