MSSQL 查詢方式 |
答題得分者是:maakite
|
misjason
一般會員 ![]() ![]() 發表:9 回覆:17 積分:5 註冊:2007-03-14 發送簡訊給我 |
|
mike0518
一般會員 ![]() ![]() 發表:1 回覆:12 積分:7 註冊:2002-07-11 發送簡訊給我 |
<textarea class="sql" rows="10" cols="60" name="code">
--*************************
--Mssql demo --1. Data prepare
declare @MoneySpent table (
ID varchar(32) ,
spentdate datetime ,
SpentMoney Money
) insert into @MoneySpent values('a','2007-01-01',10)
insert into @MoneySpent values('a','2007-01-02',15)
insert into @MoneySpent values('a','2007-02-01',20)
insert into @MoneySpent values('a','2007-02-02',25) insert into @MoneySpent values('b','2007-01-01',30)
insert into @MoneySpent values('b','2007-01-02',35)
insert into @MoneySpent values('b','2007-02-01',40)
insert into @MoneySpent values('b','2007-02-05',45) --2. Month prepare
declare @month varchar(6) , @monthFrom varchar(6) , @monthTo varchar(6)
select @monthFrom='200701' , @monthTo='200702' declare @MonthTable Table ( MonthStr varchar(6) ) declare @i int select @month=@monthfrom
while @month<=@monthTo begin
insert into @MonthTable values(@month)
select @i = cast( substring(@month,1,4) as int) * 12 cast( substring(@month,5,2) as int) -1
select @i = @i 1
select @Month = cast ( (@i / 12) * 100 (@i % 12) 1 as nvarchar(6) )
end --3. Select prepare
select a.id , a.MonthStr , a.TotalM from (
select a.id , a.MonthStr , sum(b.SpentMoney) as TotalM from (
select a.MonthStr , b.ID
from @MonthTable a
cross join(
select id from @MoneySpent group by id
) b
) a
left join @MoneySpent b
on a.id=b.id and cast(a.MonthStr as int)=(year(b.SpentDate)*100 month(b.SpentDate))
group by a.id , a.MonthStr
union
select a.id , '***' as MonthStr, sum(SpentMoney) as TotalM
from @MoneySpent a
where (year(a.SpentDate)*100 month(a.SpentDate)) between cast(@monthFrom as int) and cast(@monthTo as int)
group by a.id
) a
order by a.id , a.MonthStr</textarea>
|
channel
尊榮會員 ![]() ![]() ![]() ![]() ![]() ![]() 發表:67 回覆:707 積分:854 註冊:2002-05-02 發送簡訊給我 |
<textarea class="sql" rows="10" cols="60" name="code">SELECT T.NAME,
SUM(T.MONEY) MONTY, //整年份money total
(SELECT SUM(MONEY) FROM TABLE WHERE DATE >= '2007/1/1'
AND DATE <= '2007/1/31' AND NAME = T.NAME) MONEY1, //一月份money total
(SELECT SUM(MONEY) FROM TABLE WHERE DATE >= '2007/2/1'
AND DATE <= '2007/2/28' AND NAME = T.NAME) MONEY2//二月份money total
FROM TABLE T WHERE T.DATE >= '2007/1/1' AND T.DATE <= '2007/12/31'
GROUP BY T.NAME
</textarea>
~小弟淺見,參考看看~
------
~小弟淺見,參考看看~ |
maakite
一般會員 ![]() ![]() 發表:4 回覆:7 積分:7 註冊:2007-04-06 發送簡訊給我 |
一个交叉表的问题:
两种方式,可以动态也可以是静态的. 动态的方法: declare @xcolums nvarchar(4000),@StartMonth char(6) , @EndMonth char(6) select @xcolums = '' , @StartMonth = '200701' , @EndMonth = '200712' while @StartMonth < = @EndMonth begin set @xcolums = @xcolums '[' @StartMonth '] = sum(case when CONVERT(char(6),[date],112)= ''' @StartMonth ''' then [money] else 0 end) , ' if right(@StartMonth,2) = '12' set @StartMonth = convert(char(4),convert(int,left(@StartMonth,4)) 1) '01' else set @STartMonth = left(@StartMonth,4) right('0' convert(varchar(2),convert(int,right(@StartMonth,2)) 1),2) end set @xcolums = 'select name, ' @xcolums ' Total = sum([money]) ' 'from [table] group by name order by name ' exec(@xcolums) 静态的比较长,但是简单: select frmcode,frmname,funit, frmtypname,frmtypcode, sum(case CONVERT(char(6),[date],112) when '200701' then [Money] else 0 end ) as '200701', …… …… …… sum(case CONVERT(char(6),[date],112) when '200712' then [Money] else 0 end ) as '200712', sum(money) as Total from [table] group by [name] order by [name] |
misjason
一般會員 ![]() ![]() 發表:9 回覆:17 積分:5 註冊:2007-03-14 發送簡訊給我 |
感謝樓上三位大大傾囊相受,著實讓小弟受益匪淺!
![]() 小弟現在使用的語法如下,完全符合小弟的需求。 select s_id, sum(case CONVERT(char(7),s_date,112) when '2007/01' then s_prod else 0 end ) as '200701', sum(case CONVERT(char(7),s_date,112) when '2007/02' then s_prod else 0 end ) as '200702', sum(s_prod) as Total from hotel2007 group by s_id order by s_id 另外香奈兒大大所用的語法,小弟會卡在 AND DATE <= '2007/12/31' AND NAME = T.NAME) MONEY, //整年份money total NAME = T.NAME <-- 這邊會出錯,不知道是哪裡出問題 ![]() 謝謝各位,問題已結案 但是分數不知道要給哪位大大 >o< maakite大 目前還沒有積分,所以小弟把分數給他 希望兩位大大別見怪~ ![]()
------
請多多指教,謝謝! |
eaglewolf
資深會員 ![]() ![]() ![]() ![]() ![]() 發表:4 回覆:268 積分:429 註冊:2006-07-06 發送簡訊給我 |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |