DECLARE @TA TABLE(aId INT,aContext VARCHAR(2))
INSERT @TA
SELECT 1, 'a' UNION ALL
SELECT 2, 'b' UNION ALL
SELECT 3, 'n'DECLARE @TB TABLE(bId INT,aId INT,bContext VARCHAR(2),bTime VARCHAR(5))
INSERT @TB
SELECT 1, 1, 'd', '12:00' UNION ALL
SELECT 2, 2, 'j', '11:00' UNION ALL
SELECT 3, 1, 'q', '22:00' UNION ALL
SELECT 4, 1, 'd', '23:00' UNION ALL
SELECT 5, 2, 't', '16:00' UNION ALL
SELECT 6, 3, 'g', '15:00'
SELECT A.AID,COUNT(*) AS ACOUNT,MAX(BTIME) AS BTIME
FROM @TA AS A JOIN @TB AS B ON A.AID=B.AID
GROUP BY A.AID
/*
AID ACOUNT BTIME
----------- ----------- -----
1 3 23:00
2 2 16:00
3 1 15:00
*/
INSERT @TA
SELECT 1, 'a' UNION ALL
SELECT 2, 'b' UNION ALL
SELECT 3, 'n'DECLARE @TB TABLE(bId INT,aId INT,bContext VARCHAR(2),bTime VARCHAR(5))
INSERT @TB
SELECT 1, 1, 'd', '12:00' UNION ALL
SELECT 2, 2, 'j', '11:00' UNION ALL
SELECT 3, 1, 'q', '22:00' UNION ALL
SELECT 4, 1, 'd', '23:00' UNION ALL
SELECT 5, 2, 't', '16:00' UNION ALL
SELECT 6, 3, 'g', '15:00'
SELECT A.AID,COUNT(*) AS ACOUNT,MAX(BTIME) AS BTIME
FROM @TA AS A JOIN @TB AS B ON A.AID=B.AID
GROUP BY A.AID
/*
AID ACOUNT BTIME
----------- ----------- -----
1 3 23:00
2 2 16:00
3 1 15:00
*/
A.AID,
COUNT(*) T_COUNT,
MAX(BTIME) T_BTIME
FROM TA A
INNER JOIN TB AS B ON A.AID=B.AID
GROUP BY A.AID
INSERT @TA
SELECT 1, 'a' UNION ALL
SELECT 2, 'b' UNION ALL
SELECT 3, 'n'
DECLARE @TB TABLE(bId INT,aId INT,bContext VARCHAR(2),bTime VARCHAR(5))
INSERT @TB
SELECT 1, 1, 'd', '12:00' UNION ALL
SELECT 2, 2, 'j', '11:00' UNION ALL
SELECT 3, 1, 'q', '22:00' UNION ALL
SELECT 4, 1, 'd', '23:00' UNION ALL
SELECT 5, 2, 't', '16:00' UNION ALL
SELECT 6, 3, 'g', '15:00'
select b.aid,count(b.aid)aid,max(b.btime)btime from @tb b, @ta a where b.aid=a.aid group by b.aid
/*aid aid btime
----------- ----------- -----
1 3 23:00
2 2 16:00
3 1 15:00
*/
4 NULL NULL再次感谢各位了!
把上面综合起来就是
表一
aId,aContext
1 a
2 b
3 n
4 q
表二
bId,aId,bContext,bTime
1 1 d 12:00
2 2 j 11:00
3 1 q 22:00
4 1 d 23:00
5 2 t 16:00
6 3 g 15:00
现在我想能够得到一张表,上面显示为
aId, aCount(表二中对于某一aId的统计) aLastTime(表二中按时间最后出现aId的时间) bContext(aId在b表中最后出现时间所对应的bContext)
1 3 23:00 d
2 2 16:00 t
3 1 15:00 g
4 0 null null
SELECT
A.AID,
COUNT(*) T_COUNT,
MAX(BTIME) T_BTIME
FROM TA A
LEFT JOIN TB AS B ON A.AID=B.AID
GROUP BY A.AID
是什么原因呢?
谢谢
go
create table [ta]([aid] int,[acontext] varchar(1))
insert [ta]
select 1,'a' union all
select 2,'b' union all
select 3,'n' union all
select 4,'q'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([bid] int,[aid] int,[bcontext] varchar(1),[btime] datetime)
insert [tb]
select 1,1,'d','12:00' union all
select 2,2,'j','11:00' union all
select 3,1,'q','22:00' union all
select 4,1,'d','23:00' union all
select 5,2,'t','16:00' union all
select 6,3,'g','15:00'select * from [ta]
select * from [tb]select a.aid,aCount=count(b.aid),aLastTime=convert(varchar(5),max(btime),108)
,bContext=(select top 1 bcontext from tb where aid=a.aid order by btime desc)
from ta a left join tb b
on a.aid=b.aid
group by a.aid
--测试结果:
/*
aid aCount aLastTime bContext
----------- ----------- --------- --------
1 3 23:00 d
2 2 16:00 t
3 1 15:00 g
4 0 NULL NULL
*/