0

## SQL語法求教

sphinx iT邦研究生 3 級 ‧ 2010-05-07 17:22:14 檢舉

### 3 個回答

14
pojen
iT邦新手 2 級 ‧ 2010-05-08 00:14:31

create table pojen_table (
id integer,
ym datetime,
qty integer)

insert into pojen_Table values(1,getdate(),3)
insert into pojen_table values(2,getdate(),5)

select * from pojen_table

declare @emydate datetime
set @emydate = convert(datetime,'05/11/2010',101);

declare @3yearsago datetime

select @3yearsago,@emydate;

with t as(
select datepart(year,ym) ecumyear ,datepart(year,ym)*100+datepart(month,ym) ecurrentyear, sum(qty) eqty
from pojen_table
where ym between @3yearsago AND @emydate
group by datepart(year,ym),datepart(year,ym)*100+datepart(month,ym) with rollup
)
select *
from t
where (ecumyear = datepart(year,@emydate) and ecurrentyear = (datepart(year,@emydate)*100+datepart(month,@emydate)))
OR (ecumyear = datepart(year,@emydate) and ecurrentyear is null)
or (ecumyear is null)

drop table pojen_table

10
jamesjan
iT邦高手 1 級 ‧ 2010-05-07 21:25:37

``````&lt;pre class="c" name="code">select p.id,p.qty,p.qty_year,p.qty_3year from ((
select id,sum(qty) as qty where id=your_parts and ym='201005') a inner join (
select id,sum(qty) as qty_year where id=your_parts and ym between '201001' and '201005') b on a.id=b.id inner join (
select id,sum(qty) as qty_3year where id=your_parts and ym between '200701' and '201005') c on a.id=c.id) p
``````

``````&lt;pre class="c" name="code">select p.id,sum(qty) as qty,
(select sum(qty) from abc where id=p.id and ym between '201001' and '201005') as qty_year,
(select sum(qty) from abc where id=p.id and ym between '200701' and '201005') as qty_year,
from abc p
where p.id=your_parts and ym='201005'
``````

sphinx iT邦研究生 3 級 ‧ 2010-05-07 22:16:37 檢舉

jamesjan iT邦高手 1 級 ‧ 2010-05-09 10:29:35 檢舉

jamesjan iT邦高手 1 級 ‧ 2010-05-09 10:32:31 檢舉

sorry 少了 group by

``````&lt;pre class="c" name="code">select p.id,sum(qty) as qty,
(select sum(qty) from abc where id=p.id and ym between '201001' and '201005') as qty_year,
(select sum(qty) from abc where id=p.id and ym between '200701' and '201005') as qty_year,
from abc p
where ym='201005'
group by id
``````

jamesjan iT邦高手 1 級 ‧ 2010-05-09 10:34:12 檢舉
``````&lt;pre class="c" name="code">select p.id,sum(qty) as qty,
(select sum(qty) from abc where id=p.id and ym between '201001' and '201005') as qty_year,
(select sum(qty) from abc where id=p.id and ym between '200701' and '201005') as qty_3year,
from abc p
where ym='201005'
group by p.id
``````
6
ansonchen
iT邦新手 1 級 ‧ 2010-05-14 19:45:22

SQL join分 Left Join / Right Join / Inner Join