两张表A表
id name
1 苹果
2 梨子
3 香蕉
4 橘子B表
id stime
1 2001-01-01
1 2001-01-02
2 2001-03-01
2 2001-04-01
2 2001-05-01
2 2001-06-01
3 2001-07-01
3 2001-08-01
3 2001-08-02
3 2001-08-03
3 2001-09-01
3 2001-09-15
4 2001-03-01
4 2001-04-01
4 2001-05-01
4 2001-06-01
4 2001-07-01
要查询的条件是B表stime>2001-05-01的数量
想要结果是这样
id name count(b.id)
1 苹果 0
2 梨子 2
3 香蕉 8
4 橘子 3我是这样写的,但没对SELECT A.id, COUNT(B.id)
FROM B RIGHT OUTER JOIN
A ON B.id = A.id
WHERE (CONVERT(varchar(10), B.stime, 120) > '2001-05-01')
GROUP BY B.id求帮助!
id name
1 苹果
2 梨子
3 香蕉
4 橘子B表
id stime
1 2001-01-01
1 2001-01-02
2 2001-03-01
2 2001-04-01
2 2001-05-01
2 2001-06-01
3 2001-07-01
3 2001-08-01
3 2001-08-02
3 2001-08-03
3 2001-09-01
3 2001-09-15
4 2001-03-01
4 2001-04-01
4 2001-05-01
4 2001-06-01
4 2001-07-01
要查询的条件是B表stime>2001-05-01的数量
想要结果是这样
id name count(b.id)
1 苹果 0
2 梨子 2
3 香蕉 8
4 橘子 3我是这样写的,但没对SELECT A.id, COUNT(B.id)
FROM B RIGHT OUTER JOIN
A ON B.id = A.id
WHERE (CONVERT(varchar(10), B.stime, 120) > '2001-05-01')
GROUP BY B.id求帮助!
(
id int,
name nvarchar(100)
)
insert #A表
select 1, '苹果' union all
select 2, '梨子' union all
select 3, '香蕉' union all
select 4, '橘子'
create table #B表
(
id int,
stime datetime
)
insert #B表
select 1, '2001-01-01' union all
select 1, '2001-01-02' union all
select 2, '2001-03-01' union all
select 2, '2001-04-01' union all
select 2, '2001-05-01' union all
select 2, '2001-06-01' union all
select 3, '2001-07-01' union all
select 3, '2001-08-01' union all
select 3, '2001-08-02' union all
select 3, '2001-08-03' union all
select 3, '2001-09-01' union all
select 3, '2001-09-15' union all
select 4, '2001-03-01' union all
select 4, '2001-04-01' union all
select 4, '2001-05-01' union all
select 4, '2001-06-01' union all
select 4, '2001-07-01'
--#1.
select * from #A表 a
outer apply
(select cnt=COUNT(id) from #B表 where id = a.id and stime > '2001-05-01') b
--#2.
select a.id, a.name, cnt=COUNT(b.id)
from #A表 a
left join #B表 b
on a.id = b.id and b.stime > '2001-05-01'
group by a.id, a.name
order by a.id
/*
id name cnt
1 苹果 0
2 梨子 1
3 香蕉 6
4 橘子 2
*/
left join A表 b on a.id=b.id
where cast(stime as datetime)>cast('2001-05-01' as datetime)
group by a.id,b.name
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-10 22:42:33
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[name] varchar(4))
insert [A]
select 1,'苹果' union all
select 2,'梨子' union all
select 3,'香蕉' union all
select 4,'橘子'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[stime] datetime)
insert [B]
select 1,'2001-01-01' union all
select 1,'2001-01-02' union all
select 2,'2001-03-01' union all
select 2,'2001-04-01' union all
select 2,'2001-05-01' union all
select 2,'2001-06-01' union all
select 3,'2001-07-01' union all
select 3,'2001-08-01' union all
select 3,'2001-08-02' union all
select 3,'2001-08-03' union all
select 3,'2001-09-01' union all
select 3,'2001-09-15' union all
select 4,'2001-03-01' union all
select 4,'2001-04-01' union all
select 4,'2001-05-01' union all
select 4,'2001-06-01' union all
select 4,'2001-07-01'
--------------开始查询--------------------------
select
a.id as id,a.name,isnull(b.num,0) as [count(b.id)]
from
a
left join
(select id,count(1) as num from b where datediff(dd,'2001-05-01',stime)>=0 group by id)b
on
a.id=b.id
----------------结果----------------------------
/* id name count(b.id)
----------- ---- -----------
1 苹果 0
2 梨子 2
3 香蕉 6
4 橘子 3(4 行受影响)
*/