declare @p table ( id int, name varchar(20) ) insert into @p select 1,'张三' union select 2,'王五'declare @v table ( id int, cfname varchar(20), pid int ) insert into @v select 1,'张三出访',1 union all select 1,'张三出访',1select t.name,COUNT(t1.id)数量 from @p t left join @v t1 on t.name=LEFT(t1.cfname,len(t1.cfname)-2) group by t.name
select d.name,isnull(pid,0)[数量] from d left join g on d.id = g.pid where pid is null union all select d.name,count(1) from d inner join g on d.id = g.pid group by pid,d.name
select AA.NAME,isnull(sum(AB.pid),0) as 数量 from AA left join AB on AA.NAME=left(AB.CFname,2) group by AB.pid,AA.NAME
if object_id('[AA]') is not null drop table [AA] go create table [AA](ID int,NAME char(7)) insert [AA] select 1,'张三' union all select 2,'王五' create table [AB](ID int,CFname char(8),pid int) insert [AB] select 1,'张三出访',1 union all select 2,'张三出访',1 select AA.NAME,isnull(sum(AB.pid),0) as 数量 from AA left join AB on AA.NAME=left(AB.CFname,2) group by AB.pid,AA.NAMENAME 数量 ------- ----------- 王五 0 张三 2(2 行受影响)
(
id int,
name varchar(20)
)
insert into @p
select 1,'张三' union
select 2,'王五'declare @v table
(
id int,
cfname varchar(20),
pid int
)
insert into @v
select 1,'张三出访',1 union all
select 1,'张三出访',1select t.name,COUNT(t1.id)数量
from
@p t left join @v t1 on t.name=LEFT(t1.cfname,len(t1.cfname)-2)
group by t.name
select d.name,isnull(pid,0)[数量] from d left join g on d.id = g.pid where pid is null
union all
select d.name,count(1) from d inner join g on d.id = g.pid group by pid,d.name
go
create table [AA](ID int,NAME char(7))
insert [AA]
select 1,'张三' union all
select 2,'王五' create table [AB](ID int,CFname char(8),pid int)
insert [AB]
select 1,'张三出访',1 union all
select 2,'张三出访',1 select AA.NAME,isnull(sum(AB.pid),0) as 数量 from AA left join AB
on AA.NAME=left(AB.CFname,2) group by AB.pid,AA.NAMENAME 数量
------- -----------
王五 0
张三 2(2 行受影响)