SQL 两个表联合查询 可以实现这样的查找么？求

SQL 两个表联合查询 可以实现这样的查找么？求高手指点
客户ID客户名称性别年龄
1张三男33
2李四男28
3王五男25


订单ID水果名客户ID数量金额
1苹果1100200
2雪梨2250750
3雪梨2100300
4雪梨1150450
5苹果2100200
6苹果2100200
7雪梨1100300
8苹果1150300

客户名称性别年龄苹果总量苹果总额雪梨数量雪梨金额
张三男33250500250750
李四男282004003501,050
王五男250000

金额显示千分符号，不保留小数位

可以实现么？在线等待高人解惑 拜谢啦~

[解决办法]

SQL code

create table [UserTB]([cliID] int IDENTITY (1,1) PRIMARY KEY ,[Uname] Char(10),[sex] Char(2),[Age] tinyint)create table [FruitTB]([OrderID] int IDENTITY (1,1) PRIMARY KEY ,[Fname] Char(10),[cliID] int ,[amount] int ,[Smoney] money)INSERT INTO [UserTB] (Uname,sex,Age) VALUES ('张三','男',33)INSERT INTO [UserTB] (Uname,sex,Age) VALUES ('李四','男',28)INSERT INTO [UserTB] (Uname,sex,Age) VALUES ('王五','男',25)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',1,100,200)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',2,250,750)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',2,100,300)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',1,150,450)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',2,100,200)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',2,100,200)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',1,100,300)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',1,150,300)goselect    a.Uname 客户名称,    a.sex 性别,    a.Age 年龄,    SUM(case when [Fname]='苹果' then [amount] else 0 end) as 苹果数量,    SUM(case when [Fname]='苹果' then [Smoney] else 0 end) as 苹果金额,    SUM(case when [Fname]='雪梨' then [amount] else 0 end) as 雪梨数量,    SUM(case when [Fname]='雪梨' then [Smoney] else 0 end) as 雪梨金额from    [UserTB] aleft join    [FruitTB] bon    a.cliID=b.cliIDgroup by    a.Uname,    a.sex,    a.Age/*客户名称    性别    年龄    苹果数量    苹果金额    雪梨数量    雪梨金额------------------------------------------------李四          男    28    200    400.00    350    1050.00王五          男    25    0    0.00    0    0.00张三          男    33    250    500.00    250    750.00*/
[解决办法]
SQL code
create table [UserTB]([cliID] int IDENTITY (1,1) PRIMARY KEY ,[Uname] Char(10),[sex] Char(2),[Age] tinyint)create table [FruitTB]([OrderID] int IDENTITY (1,1) PRIMARY KEY ,[Fname] Char(10),[cliID] int ,[amount] int ,[Smoney] money)INSERT INTO [UserTB] (Uname,sex,Age) VALUES ('张三','男',33)INSERT INTO [UserTB] (Uname,sex,Age) VALUES ('李四','男',28)INSERT INTO [UserTB] (Uname,sex,Age) VALUES ('王五','男',25)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',1,100,200)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',2,250,750)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',2,100,300)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',1,150,450)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',2,100,200)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',2,100,200)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('雪梨',1,100,300)INSERT INTO [FruitTB] (Fname,cliID,amount,Smoney) VALUES ('苹果',1,150,300)go--动态实现:declare @str varchar(2000)set @str=''select     @str=@str+','+rtrim([Fname])+'数量=sum(case when rtrim([Fname])='+    QUOTENAME(rtrim([Fname]),'''')+' then [amount] else 0 end),'    +rtrim([Fname])+'金额=sum(case when rtrim([Fname])='+QUOTENAME(rtrim([Fname]),'''')    +' then [Smoney] else 0 end)'from    [FruitTB]group by    [Fname]print @strset @str='select a.[Uname] as 客户名称,a.[sex] as 性别,a.[Age] as 年龄'    +@str+' from [UserTB] a left join [FruitTB] b on a.[cliID]=b.[cliID]     group by a.[Uname],a.[sex],a.[Age]'exec(@str)/*客户名称    性别    年龄    苹果数量    苹果金额    雪梨数量    雪梨金额------------------------------------------------李四          男    28    200    400.00    350    1050.00王五          男    25    0    0.00    0    0.00张三          男    33    250    500.00    250    750.00*/ 
 
[解决办法]
探讨

引用:
SQL code

create table [UserTB]
(
[cliID] int IDENTITY (1,1) PRIMARY KEY ,
[Uname] Char(10),
[sex] Char(2),
[Age] tinyint)

create table [FruitTB]
(
[OrderID] int IDENTITY (1,1……