急; 求SQL語句
有2張表
表T
num         date
12           2007/03/11
11           2007/04/02
123         2007/03/22


表H
HDATE
2007/04/01
2007/04/03

表H紀錄的是所有的假日,我想得到以下的結果

num             count
12               2  
11               1
123             2

count為從T表至今的假日總數

------解决方案--------------------
declare @a table(num int, [date] smalldatetime)
insert @a select 12 , '2007/03/11 '
union all select 11 , '2007/04/02 '
union all select 123, '2007/03/22 '


declare @b table(HDATE smalldatetime)
insert @b select '2007/04/01 '
union all select '2007/04/03 '


select num,cou=(select count(1) from @b where hdate between a.[date] and getdate()) from @a a
------解决方案--------------------
create table T(num int,[date] datetime)
insert into T
select 12, '2007-3-11 ' union all
select 11, '2007-4-2 ' union all
select 123, '2007-3-22 '

create table H(Hdate datetime)
insert into H
select '2007-4-1 ' union all
select '2007-4-3 '

select num,count(*) as [count] from T
inner join H
on H.Hdate between T.[date] and getdate()
group by num

/*
num count
----------- -----------
11 1
12 2
123 2
*/

drop table t,h
------解决方案--------------------
CREATE TABLE T(NUM INT, TDATE DATETIME)
INSERT INTO T SELECT 12, '2007-03-11 '
UNION ALL SELECT 11, '2007-04-02 '
UNION ALL SELECT 123, '2007-03-22 '


CREATE TABLE H (HDATE DATETIME)
INSERT INTO H SELECT '2007-04-02 '
UNION ALL SELECT '2007-04-02 '
UNION ALL SELECT '2007-03-22 '


SELECT T.NUM,COUNT(T.tDATE) FROM T FULL OUTER JOIN H ON T.TDATE=H.HDATE GROUP BY T.NUM


11 2
12 1
123 1

------解决方案--------------------
declare @t table(num int,date datetime)
insert @t select 12, '2007/03/11 '
insert @t select 11, '2007/04/02 '
insert @t select 123, '2007/03/22 '

declare @H table(HDATE datetime)
insert @h select '2007/04/01 '
insert @h select '2007/04/03 '

select t.num,con=count(1)
from @t t left join @h h on t.date <h.HDATE
group by t.num


(1 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)
num con
----------- -----------
11 1
12 2
123 2

(3 行受影响)