22,209
社区成员
发帖
与我相关
我的任务
分享
select
t2.callno,
t1.recno,
t1.[count],
count(t2.callno) as 数量
from
T1,T2
where
t1.callno=t2.callno
and
t1.进年=t2.进年
and
LEFT(T1.CALLNO,1)='A' AND t1.进年=2008 AND T2.出年 between 2008 and 2008
group by
t2.callno,
t1.recno,
t1.[count]
/**
callno recno count 数量
------ ----------- ----------- -----------
A12345 10001 4 2
A56789 10002 2 3
(所影响的行数为 2 行)
**/
select
t2.callno,
t1.recno,
t1.[count],
count(t2.callno) as 数量
from
T1,T2
where
t1.callno=t2.callno
and
t1.进年=t2.进年
and
LEFT(T1.CALLNO,1)='A' AND t1.进年=2008 AND T2.出年 between 2008 and 2009
group by
t2.callno,
t1.recno,
t1.[count]
/**
callno recno count 数量
------ ----------- ----------- -----------
A12345 10001 4 5
A56789 10002 2 4
(所影响的行数为 2 行)
**/
select
t2.callno,
t1.recno,
t1.[count],
count(t2.callno) as 数量
from
T1,T2
where
t1.callno=t2.callno
and
t1.进年=t2.进年
and
LEFT(T1.CALLNO,1)='A' AND t1.进年=2007 AND T2.出年 between 2007 and 2009
group by
t2.callno,
t1.recno,
t1.[count]
/**
callno recno count 数量
------ ----------- ----------- -----------
A12345 10001 6 4
A56789 10002 3 2
(所影响的行数为 2 行)
**/
---测试数据---
if object_id('[T1]') is not null drop table [T1]
go
create table [T1]([CALLNO] varchar(6),[RECNO] int,[进年] int,[COUNT] int)
insert [T1]
select 'A12345',10001,2008,4 union all
select 'A12345',10001,2007,6 union all
select 'A56789',10002,2008,2 union all
select 'A56789',10002,2007,3
if object_id('[T2]') is not null drop table [T2]
go
create table [T2]([CALLNO] varchar(6),[进年] int,[出年] int)
insert [T2]
select 'A12345',2008,2009 union all
select 'A12345',2008,2009 union all
select 'A12345',2008,2009 union all
select 'A12345',2008,2008 union all
select 'A12345',2008,2008 union all
select 'A12345',2007,2009 union all
select 'A12345',2007,2008 union all
select 'A12345',2007,2008 union all
select 'A12345',2007,2007 union all
select 'A56789',2008,2009 union all
select 'A56789',2008,2008 union all
select 'A56789',2008,2008 union all
select 'A56789',2008,2008 union all
select 'A56789',2007,2008 union all
select 'A56789',2007,2007
---查询---
select
t2.callno,
t1.recno,
t1.[count],
count(t2.callno) as 数量
from
T1,T2
where
t1.callno=t2.callno
and
LEFT(T1.CALLNO,1)='A' AND t1.进年='2008' AND T2.进年>='2008' and T2.出年 <='2008'
group by
t2.callno,
t1.recno,
t1.[count]
---结果---
callno recno count 数量
------ ----------- ----------- -----------
A12345 10001 4 2
A56789 10002 2 3
(所影响的行数为 2 行)
换成存储过程吧..
1、CREATE FUNCTION dbo.myFunc(@myCallNo varchar(30),@InYEAR varchar(4), @OutYEAR varchar(4))
RETURNS int
AS
BEGIN
DECLARE @RE int
SELECT @RE = count(t2.callno)
FROM T2
WHERE (CALLNO=@myCallNo) AND (进年=@InYEAR) AND (出年 >= @InYEAR AND 出年 <= @OutYEAR)
RETURN @RE
END
---测试数据---
if object_id('[T1]') is not null drop table [T1]
go
create table [T1]([CALLNO] varchar(6),[RECNO] int,[进年] int,[COUNT] int)
insert [T1]
select 'A12345',10001,2008,4 union all
select 'A12345',10001,2007,6 union all
select 'A56789',10002,2008,2 union all
select 'A56789',10002,2007,3 union all
select 'A00001',10003,2008,7
if object_id('[T2]') is not null drop table [T2]
go
create table [T2]([CALLNO] varchar(6),[进年] int,[出年] int)
insert [T2]
select 'A12345',2008,2009 union all
select 'A12345',2008,2009 union all
select 'A12345',2008,2009 union all
select 'A12345',2008,2008 union all
select 'A12345',2008,2008 union all
select 'A12345',2007,2009 union all
select 'A12345',2007,2008 union all
select 'A12345',2007,2008 union all
select 'A12345',2007,2007 union all
select 'A56789',2008,2009 union all
select 'A56789',2008,2008 union all
select 'A56789',2008,2008 union all
select 'A56789',2008,2008 union all
select 'A56789',2007,2008 union all
select 'A56789',2007,2007
select
t1.callno,
t1.recno,
t1.[count],
ISNULL(count(t2.callno),0) as 数量
from
T1
LEFT JOIN
T2
ON
t1.callno=t2.callno
and
t1.进年=t2.进年
and
T2.出年 between 2008 and 2008
WHERE
LEFT(T1.CALLNO,1)='A' AND t1.进年=2008
group by
t1.callno,
t1.recno,
t1.[count]
/**
callno recno count 数量
------ ----------- ----------- -----------
A00001 10003 7 0
A12345 10001 4 2
A56789 10002 2 3
(所影响的行数为 3 行)
**/
select
t2.callno,
t1.recno,
t1.[count],
ISNULL(count(t2.callno),0) as 数量
from
T1
LEFT JOIN
T2
ON
t1.callno=t2.callno
and
t1.进年=t2.进年
and
T2.出年 between 2008 and 2008
WHERE
LEFT(T1.CALLNO,1)='A' AND t1.进年=2008
group by
t2.callno,
t1.recno,
t1.[count]