SELECT ISNULL(T1.FDM,T2.FDM), SUM(ISNULL(PGSL,0)), SUM(ISNULL(LZSL,0)), SUM(ISNULL(PGSL,0)+ISNULL(LZSL,0))
FROM 苹果 T1 FULL JOIN 梨子 T2
ON T1.FDM=T2.FDM
GROUP BY ISNULL(T1.FDM,T2.FDM)
FROM 苹果 T1 FULL JOIN 梨子 T2
ON T1.FDM=T2.FDM
GROUP BY ISNULL(T1.FDM,T2.FDM)
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @苹果表
DECLARE @苹果表 TABLE (NO INT,FDM VARCHAR(5),PGSL INT)
INSERT INTO @苹果表
SELECT 1,'分店1',100 UNION ALL
SELECT 2,'分店2',100 UNION ALL
SELECT 3,'分店3',100
--> 生成测试数据: @梨子表
DECLARE @梨子表 TABLE (NO INT,FDM VARCHAR(5),LZSL INT)
INSERT INTO @梨子表
SELECT 1,'分店2',100 UNION ALL
SELECT 2,'分店3',100 UNION ALL
SELECT 3,'分店4',100--SQL查询如下:SELECT FDM,SUM(PGSL) AS PGSL,SUM(LZSL) AS LZSL,SUM(PGSL)+SUM(LZSL) AS 合计数量
FROM (
SELECT NO,FDM,PGSL,0 AS LZSL
FROM @苹果表
UNION ALL
SELECT NO,FDM,0,LZSL
FROM @梨子表
) AS A
GROUP BY FDM
FROM
(
SELECT FDM,0 AS LZSL,PGSL
FROM 苹果表
UNION JOIN
SELECT FDM,LZSL,0 AS PGSL
FROM 梨子表
) AS Temp_T
GROUP BY Temp_T.FDM
create table apple(NO int,FDM Nvarchar(10),PGSL int)
insert apple select 1,N'分店1',100
union all select 2,N'分店2',100
union all select 3,N'分店3',100
create table pear(NO int,FDM Nvarchar(10),LZSL int)
insert pear select 1,N'分店2',100
union all select 2,N'分店3',100
union all select 3,N'分店4',100
SELECT ISNULL(A.FDM,B.FDM)FDM,ISNULL(PGSL,0)PGSL,ISNULL(LZSL,0)LZSL,ISNULL(PGSL,0)+ISNULL(LZSL,0) FROM APPLE A FULL JOIN PEAR B ON A.FDM=B.FDM
go
DROP TABLE APPLE,PEAR
/*FDM PGSL LZSL
---------- ----------- ----------- -----------
分店1 100 0 100
分店2 100 100 200
分店3 100 100 200
分店4 0 100 100
*/
FROM 苹果 T1 FULL JOIN 梨子 T2
ON T1.FDM=T2.FDM
GROUP BY ISNULL(T1.FDM,T2.FDM)
create table apple(NO int,FDM varchar(10),PGSL int)
insert apple select 1,'分店1',100
union all select 2,'分店2',100
union all select 3,'分店3',100
go
create table pear(NO int,FDM varchar(10),LZSL int)
insert pear select 1,'分店2',100
union all select 2,'分店3',100
union all select 3,'分店4',100
go select FDM ,
sum(case when class = 'apple' then PGSL else 0 end)[PGSL(苹果数量)],
sum(case when class = 'pear' then PGSL else 0 end)[LZSL(梨子数量)],
sum(isnull(PGSL,0))[合计数量]
from
(
select *,class = 'apple' from apple
union all
select *,class = 'pear' from pear
)T
group by FDM
还有后面的AS A 作用是什么