表1 ID no text1 text2
101 10 你好 不好
102 11 我好 真好表2 ID no text3 text4
101 10 佛挡 NULL
101 10 房东 NULL
102 12 NULL 搞事
102 12 NULL 高端
102 12 NULL 框架
(表2分组汇总)结果 ID no text1 text2 text3 text4
101 10 你好 不好 2 NULL
102 11 我好 真好 NULL NULL
102 12 NULL NULL NULL 3请问这个SQL怎么实现?请高手解决!小弟感激不尽,在线等待!
101 10 你好 不好
102 11 我好 真好表2 ID no text3 text4
101 10 佛挡 NULL
101 10 房东 NULL
102 12 NULL 搞事
102 12 NULL 高端
102 12 NULL 框架
(表2分组汇总)结果 ID no text1 text2 text3 text4
101 10 你好 不好 2 NULL
102 11 我好 真好 NULL NULL
102 12 NULL NULL NULL 3请问这个SQL怎么实现?请高手解决!小弟感激不尽,在线等待!
declare @ta table (ID int,no int,text1 varchar(4),text2 varchar(4))
insert into @ta
select 101,10,'你好','不好' union all
select 102,11,'我好','真好'
--> 测试数据: @tb
declare @tb table (ID int,no int,text3 varchar(4),text4 varchar(4))
insert into @tb
select 101,10,'佛挡',null union all
select 101,10,'房东',null union all
select 102,12,null,'搞事' union all
select 102,12,null,'高端' union all
select 102,12,null,'框架'select
ID=isnull(a.id,b.id),
no=isnull(a.no,b.no),
text1=max(text1),
text2=max(text2),
text3=sum(case when text3 is not null then 1 else null end),
text4=sum(case when text4 is not null then 1 else null end)
from @ta a
full join @tb b
on
a.id=b.id and a.no=b.no
group by
isnull(a.id,b.id),isnull(a.no,b.no)
ID no text1 text2 text3 text4
----------- ----------- ----- ----- ----------- -----------
101 10 你好 不好 2 NULL
102 11 我好 真好 NULL NULL
102 12 NULL NULL NULL 3
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
declare @TB table([ID] int,[no] int,[text1] varchar(4),[text2] varchar(4))
insert @TB
select 101,10,'你好','不好' union all
select 102,11,'我好','真好'
declare @TB1 table([ID] int,[no] int,[text3] varchar(4),[text4] varchar(4))
insert @TB1
select 101,10,'佛挡',null union all
select 101,10,'房东',null union all
select 102,12,null,'搞事' union all
select 102,12,null,'高端' union all
select 102,12,null,'框架'SELECT coalesce(A.[ID],B.[ID]), coalesce(B.[no],A.[no]), A.[text1], A.[text2], B.[text3], B.[text4]
FROM @TB A FULL JOIN (
select [ID] , [no],COUNT([text3]) AS [text3] , COUNT([text4]) AS [text4]
from @TB1
GROUP BY [ID], [no]
) AS B ON A.[ID] = B.[ID] AND A.[no] = B.[no]
/*
(5 row(s) affected)
text1 text2 text3 text4
----------- ----------- ----- ----- ----------- -----------
101 10 你好 不好 2 0
102 11 我好 真好 NULL NULL
102 12 NULL NULL 0 3
a.id,isnull(a.no,b.no) as [no],a. text1,a. text2,b.[text3],c.[text4]
from
表1 a
full join
(select [ID],[no],count(1) as [text3] from 表2 where id='101' group by [ID],[no])b
on
a.id=b.id
full join
(select [ID],count(1) as [text4] from 表2 where id='102' group by [ID] )c
on
a.id=c.id