table1
id wordid class
1 001 aa
2 002 bb
3 003 cc
table2
id wordid
1 001
2 001
3 003
我想实现结果 id wordid class counts
1 001 aa 2
2 002 bb 0
3 003 cc 1其中counts 是根据表1的wordid计算的在表2的总数
id wordid class
1 001 aa
2 002 bb
3 003 cc
table2
id wordid
1 001
2 001
3 003
我想实现结果 id wordid class counts
1 001 aa 2
2 002 bb 0
3 003 cc 1其中counts 是根据表1的wordid计算的在表2的总数
select *,(select isnull(COUNT(*),0) from table2 where wordid=a.wordid ) as counts from table1 a
create table #table1
(
id int identity(1,1) primary key,
wordid nvarchar(10),
class char(10)
)
insert into #table1 select '001','aa'
insert into #table1 select '002','bb'
insert into #table1 select '003','cc'create table #table2
(
id int identity(1,1) primary key,
wordid nvarchar(10)
)
insert into #table2 select '001'
insert into #table2 select '001'
insert into #table2 select '003'select b1.id,b1.class,isnull(cnt,'0') counts from #table1 b1
full join
(
select wordid,count(wordid) cnt from #table2 group by wordid
) b2
on b1.id=b2.wordid
create table #table1
(
id int identity(1,1) primary key,
wordid nvarchar(10),
class char(10)
)
insert into #table1 select '001','aa'
insert into #table1 select '002','bb'
insert into #table1 select '003','cc'create table #table2
(
id int identity(1,1) primary key,
wordid nvarchar(10)
)
insert into #table2 select '001'
insert into #table2 select '001'
insert into #table2 select '003'select b1.id,b1.class,isnull(cnt,'0') counts from #table1 b1
full join
(
select wordid,count(wordid) cnt from #table2 group by wordid
) b2
on b1.id=b2.wordid
id class counts
----------- ---------- -----------
1 aa 2
2 bb 0
3 cc 1(3 行受影响)
full join
(
select wordid,count(wordid) cnt from #table2 group by wordid
) b2
on b1.id=b2.wordid
id wordid class counts
----------- ---------- ---------- -----------
1 001 aa 2
2 002 bb 0
3 003 cc 1
-- Author :SQL77(只为思齐老)
-- Date :2010-02-01 17:47:12
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#table1
if object_id('tempdb.dbo.#table1') is not null drop table #table1
go
create table #table1([id] int,[wordid] varchar(3),[class] varchar(2))
insert #table1
select 1,'001','aa' union all
select 2,'002','bb' union all
select 3,'003','cc'
--> 测试数据:#table2
if object_id('tempdb.dbo.#table2') is not null drop table #table2
go
create table #table2([id] int,[wordid] varchar(3))
insert #table2
select 1,'001' union all
select 2,'001' union all
select 3,'003'
--------------开始查询--------------------------
SELECT T.*,T1.NUM FROM #TABLE1 T,
(
select T1.wordid,
ISNULL(COUNT(T2.wordid),0) NUM
from #table1 T1
LEFT JOIN #table2 T2 ON T1.wordid=T2.wordid
GROUP BY T1.wordid
)T1 WHERE T.wordid=T1.wordid
----------------结果----------------------------
/* (所影响的行数为 3 行)
(所影响的行数为 3 行)id wordid class NUM
----------- ------ ----- -----------
1 001 aa 2
2 002 bb 0
3 003 cc 1(所影响的行数为 3 行)警告: 聚合或其它 SET 操作消除了空值。*/