--> 测试数据: #A if object_id('tempdb.dbo.#A') is not null drop table #A create table #A (id int,text varchar(4)) insert into #A select 1,'力量' union all select 2,'体力' union all select 3,'邪恶' --> 测试数据: #B if object_id('tempdb.dbo.#B') is not null drop table #B create table #B (pid int,whole varchar(2),Aid int) insert into #B select 1,'甲',2 union all select 2,'乙',2 union all select 3,'丙',3 go select b.pid,b.whole,a.text from #B b left outer join #A a on b.aid=a.id go drop table #A,#B/* pid whole text ----------- ----- ---- 1 甲 体力 2 乙 体力 3 丙 邪恶(3 row(s) affected)*/
--> 测试数据: #A if object_id('tempdb.dbo.#A') is not null drop table #A create table #A (id int,text varchar(4)) insert into #A select 1,'力量' union all select 2,'体力' union all select 3,'邪恶' --> 测试数据: #B if object_id('tempdb.dbo.#B') is not null drop table #B create table #B (pid int,whole varchar(2),Aid int) insert into #B select 1,'甲',2 union all select 2,'乙',2 union all select 3,'丙',3 go select a.id,case when exists(select * from #B where aid=a.id) then '有' else '无' end [B表是否有选], isnull(b.cnt,0) cnt from #A a left outer join (select aid,count(*) cnt from #B group by aid) b on a.id=b.aid go drop table #A,#B/* id B表是否有选 B表选的数量 1 无 0 2 有 2 3 有 1 */
--> --> (Ben)生成測試數據
if not object_id('Tempdb..#A') is null drop table #A Go Create table #A([id] int,[text] nvarchar(2)) Insert #A select 1,'力量' union all select 2,'体力' union all select 3,'邪恶' Go --> --> (Ben)生成測試數據
if not object_id('Tempdb..#B') is null drop table #B Go Create table #B([pid] int,[whole] nvarchar(1),[Aid] int) Insert #B select 1,'甲',2 union all select 2,'乙',2 union all select 3,'丙',3 Go select pid,whole,text from #B left join #A on id=Aidselect id,case when(select count(*) from #B where aid=a.id)=0 then '无' else '有' end, num=(select count(*) from #B where aid=a.id) from #A a
--> 测试数据: #a if object_id('tempdb.dbo.#a') is not null drop table #a create table #a (id int,text varchar(11)) insert into #a select 1,'力量' union all select 2,'体力' union all select 3,'邪恶' --> 测试数据: #b if object_id('tempdb.dbo.#b') is not null drop table #b create table #b (pid int,whole varchar(11),Aid int) insert into #b select 1,'甲',2 union all select 2,'乙',2 union all select 3,'丙',3select b.pid,b.whole,a.text from #b b join #a a on a.id=b.aid /* pid whole text ----------- ----------- ----------- 1 甲 体力 2 乙 体力 3 丙 邪恶 */select a.id,count(b.aid)cn from #a a left join #b b on a.id=b.aid group by a.id /* id cn ----------- ----------- 1 0 2 2 3 1 */
andyhooo 我认识你!!create table #B (pid int,whole varchar(2),Aid int) insert into #B select 1,'甲',2 union all select 2,'乙',2 union all select 3,'丙',3 goselect b.pid,b.whole,a.text from #B b left outer join #A a on b.aid=a.id goselect id, (case (select count(pid) from #B where #B.Aid = #A.id) when 0 then 'No' else 'Yes' end) as B表是否有选 , (select count(pid) from #B where #B.Aid = #A.id) as B表选的数量 from #A
--try create table A ( id int, text nvarchar(10) ) insert into A select 1,N'力量' union select 2,N'体力' union select 3,N'邪恶'create table B ( pid int, whole nvarchar(2), Aid int ) insert into B select 1,N'甲',2 union select 2,N'乙',2 union select 3,N'丙',3 select B.pid,B.whole,A.text from B inner join A on B.Aid = A.id pid whole text ----------- ----- ---------- 1 甲 体力 2 乙 体力 3 丙 邪恶(3 row(s) affected) select A.id, case when B.Aid is null then N'无' else N'有' end, sum(case when B.Aid is not null then 1 else 0 end ) from A left join B on A.id = B.Aid group by A.id ,B.Aidid ----------- ---- ----------- 1 无 0 2 有 2 3 有 1(3 row(s) affected)
1.SELECT * FROM A INNER JOIN B ON A.id=B.Aid2.SELECT A.Id,COUNT(B.Pid) AS B表选的数量 FROM A LEFT JOIN B ON A.id=B.Aid GROUP BY A.Id
--借数据! --> 测试数据: #A if object_id('tempdb.dbo.#A') is not null drop table #A create table #A (id int,text varchar(4)) insert into #A select 1,'力量' union all select 2,'体力' union all select 3,'邪恶' --> 测试数据: #B if object_id('tempdb.dbo.#B') is not null drop table #B create table #B (pid int,whole varchar(2),Aid int) insert into #B select 1,'甲',2 union all select 2,'乙',2 union all select 3,'丙',3 goselect pid,whole,text from #a a,#b b where a.id=b.aid/* pid whole text ----------- ----- ---- 1 甲 体力 2 乙 体力 3 丙 邪恶 */select a.id,case when aid is not null then '有' else '无' end as b表是否被选中, isnull(num,0) as b表选的数量 from #a a left join (select Aid,count(1) as num from #b group by aid) as b on a.id=b.aid /* id b表是否被选中 b表选的数量 ----------- ------- ----------- 1 无 0 2 有 2 3 有 1 */
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (id int,text varchar(4))
insert into #A
select 1,'力量' union all
select 2,'体力' union all
select 3,'邪恶'
--> 测试数据: #B
if object_id('tempdb.dbo.#B') is not null drop table #B
create table #B (pid int,whole varchar(2),Aid int)
insert into #B
select 1,'甲',2 union all
select 2,'乙',2 union all
select 3,'丙',3
go
select b.pid,b.whole,a.text
from #B b
left outer join #A a
on b.aid=a.id
go
drop table #A,#B/*
pid whole text
----------- ----- ----
1 甲 体力
2 乙 体力
3 丙 邪恶(3 row(s) affected)*/
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (id int,text varchar(4))
insert into #A
select 1,'力量' union all
select 2,'体力' union all
select 3,'邪恶'
--> 测试数据: #B
if object_id('tempdb.dbo.#B') is not null drop table #B
create table #B (pid int,whole varchar(2),Aid int)
insert into #B
select 1,'甲',2 union all
select 2,'乙',2 union all
select 3,'丙',3
go
select a.id,case when exists(select * from #B where aid=a.id) then '有' else '无' end [B表是否有选],
isnull(b.cnt,0) cnt
from #A a
left outer join (select aid,count(*) cnt from #B group by aid) b
on a.id=b.aid
go
drop table #A,#B/*
id B表是否有选 B表选的数量
1 无 0
2 有 2
3 有 1
*/
if not object_id('Tempdb..#A') is null
drop table #A
Go
Create table #A([id] int,[text] nvarchar(2))
Insert #A
select 1,'力量' union all
select 2,'体力' union all
select 3,'邪恶'
Go
--> --> (Ben)生成測試數據
if not object_id('Tempdb..#B') is null
drop table #B
Go
Create table #B([pid] int,[whole] nvarchar(1),[Aid] int)
Insert #B
select 1,'甲',2 union all
select 2,'乙',2 union all
select 3,'丙',3
Go
select pid,whole,text from #B
left join #A on id=Aidselect id,case when(select count(*) from #B where aid=a.id)=0 then '无' else '有' end,
num=(select count(*) from #B where aid=a.id) from #A a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a (id int,text varchar(11))
insert into #a
select 1,'力量' union all
select 2,'体力' union all
select 3,'邪恶'
--> 测试数据: #b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b (pid int,whole varchar(11),Aid int)
insert into #b
select 1,'甲',2 union all
select 2,'乙',2 union all
select 3,'丙',3select b.pid,b.whole,a.text from #b b join #a a on a.id=b.aid
/*
pid whole text
----------- ----------- -----------
1 甲 体力
2 乙 体力
3 丙 邪恶
*/select a.id,count(b.aid)cn from #a a left join #b b on a.id=b.aid group by a.id
/*
id cn
----------- -----------
1 0
2 2
3 1
*/
insert into #B
select 1,'甲',2 union all
select 2,'乙',2 union all
select 3,'丙',3
goselect b.pid,b.whole,a.text
from #B b
left outer join #A a
on b.aid=a.id
goselect id, (case (select count(pid) from #B where #B.Aid = #A.id) when 0 then 'No' else 'Yes' end) as B表是否有选 , (select count(pid) from #B where #B.Aid = #A.id) as B表选的数量 from #A
----------- ----- ----
1 甲 体力
2 乙 体力
3 丙 邪恶(3 row(s) affected)id B表是否有选 B表选的数量
----------- ------ -----------
1 No 0
2 Yes 2
3 Yes 1(3 row(s) affected)
create table A
(
id int,
text nvarchar(10)
)
insert into A
select
1,N'力量' union
select
2,N'体力' union
select
3,N'邪恶'create table B
(
pid int,
whole nvarchar(2),
Aid int
)
insert into B
select
1,N'甲',2 union
select
2,N'乙',2 union
select
3,N'丙',3
select B.pid,B.whole,A.text from B inner join A on B.Aid = A.id
pid whole text
----------- ----- ----------
1 甲 体力
2 乙 体力
3 丙 邪恶(3 row(s) affected)
select A.id,
case when B.Aid is null then N'无' else N'有' end,
sum(case when B.Aid is not null then 1 else 0 end )
from A
left join B on A.id = B.Aid
group by A.id ,B.Aidid
----------- ---- -----------
1 无 0
2 有 2
3 有 1(3 row(s) affected)
1.SELECT *
FROM A
INNER JOIN B
ON A.id=B.Aid2.SELECT A.Id,COUNT(B.Pid) AS B表选的数量
FROM A
LEFT JOIN B
ON A.id=B.Aid
GROUP BY A.Id
--> 测试数据: #A
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (id int,text varchar(4))
insert into #A
select 1,'力量' union all
select 2,'体力' union all
select 3,'邪恶'
--> 测试数据: #B
if object_id('tempdb.dbo.#B') is not null drop table #B
create table #B (pid int,whole varchar(2),Aid int)
insert into #B
select 1,'甲',2 union all
select 2,'乙',2 union all
select 3,'丙',3
goselect pid,whole,text from #a a,#b b
where a.id=b.aid/*
pid whole text
----------- ----- ----
1 甲 体力
2 乙 体力
3 丙 邪恶
*/select a.id,case when aid is not null then '有' else '无' end as b表是否被选中,
isnull(num,0) as b表选的数量
from #a a left join (select Aid,count(1) as num from #b group by aid) as b
on a.id=b.aid
/*
id b表是否被选中 b表选的数量
----------- ------- -----------
1 无 0
2 有 2
3 有 1
*/