日期:2014-05-18 浏览次数:20497 次
IF OBJECT_ID(N'[奶粉销售表]') IS NOT NULL DROP TABLE [奶粉销售表] IF OBJECT_ID(N'[玩具销售表]') IS NOT NULL DROP TABLE [玩具销售表] IF OBJECT_ID(N'[客户表]') IS NOT NULL DROP TABLE [客户表] GO CREATE TABLE [奶粉销售表]([客户表ID] varchar(20),[客户姓名] varchar(20),[销售金额] int) CREATE TABLE [玩具销售表]([客户表ID] varchar(20),[客户姓名] varchar(20),[销售金额] int) CREATE TABLE [客户表]([客户表ID] varchar(20),[客户姓名] varchar(20),[联系电话] int) GO insert into [奶粉销售表] select 'A001','张三',210 union all select 'A002','李四',135 union all select 'A001','张三',140 insert into [玩具销售表] select 'A002','李四',120 union all select 'A003','王五',380 union all select 'A001','张三',110 union all select 'A003','王五',30 insert into [客户表] select 'A001','张三',111 union all select 'A002','李四',222 union all select 'A003','王五',333 union all select 'A004','赵六',444 SELECT a.[客户表ID],a.[客户姓名],ISNULL(b.[奶粉销售金额],0) AS '奶粉销售金额',ISNULL([玩具销售金额],0)AS '玩具销售金额',a.[联系电话] FROM [客户表] a LEFT JOIN (SELECT [客户表ID],SUM([销售金额]) AS '奶粉销售金额' FROM [奶粉销售表] GROUP BY [客户表ID]) b ON a.[客户表ID]=b.[客户表ID] LEFT JOIN (SELECT [客户表ID],SUM([销售金额]) AS '玩具销售金额' FROM [玩具销售表] GROUP BY [客户表ID]) c ON a.[客户表ID]=c.[客户表ID] /* (3 行受影响) (4 行受影响) (4 行受影响) 客户表ID 客户姓名 奶粉销售金额 玩具销售金额 联系电话 -------------------- -------------------- ----------- ----------- ----------- A001 张三 350 110 111 A002 李四 135 120 222 A003 王五 0 410 333 A004 赵六 0 0 444 (4 行受影响) */
------解决方案--------------------
--借用下关将军的数据 IF OBJECT_ID(N'[奶粉销售表]') IS NOT NULL DROP TABLE [奶粉销售表] IF OBJECT_ID(N'[玩具销售表]') IS NOT NULL DROP TABLE [玩具销售表] IF OBJECT_ID(N'[客户表]') IS NOT NULL DROP TABLE [客户表] GO CREATE TABLE [奶粉销售表]([客户表ID] varchar(20),[客户姓名] varchar(20),[销售金额] int) CREATE TABLE [玩具销售表]([客户表ID] varchar(20),[客户姓名] varchar(20),[销售金额] int) CREATE TABLE [客户表]([客户表ID] varchar(20),[客户姓名] varchar(20),[联系电话] int) GO insert into [奶粉销售表] select 'A001','张三',210 union all select 'A002','李四',135 union all select 'A001','张三',140 insert into [玩具销售表] select 'A002','李四',120 union all select 'A003','王五',380 union all select 'A001','张三',110 union all select 'A003','王五',30 insert into [客户表] select 'A001','张三',111 union all select 'A002','李四',222 union all select 'A003','王五',333 union all select 'A004','赵六',444 SELECT a.[客户表ID], a.[客户姓名], ISNULL(b.[奶粉销售金额],0) AS '奶粉销售金额', ISNULL([玩具销售金额],0)AS '玩具销售金额', ISNULL(a.[联系电话],0) FROM [客户表] a LEFT JOIN (SELECT [客户表ID], SUM([销售金额]) AS '奶粉销售金额' FROM [奶粉销售表] GROUP BY [客户表ID]) b ON a.[客户表ID]=b.[客户表ID] LEFT JOIN (SELECT [客户表ID], SUM([销售金额]) AS '玩具销售金额' FROM [玩具销售表] GROUP BY [客户表ID]) c ON a.[客户表ID]=c.[客户表ID]