• 主页
layxbjl 2014年01月14日
##### 多表查询，分组合计的问题

id name
1 张三
2 李四
3 王五
4 赵六

id Num CreatedBy
1 X-1 张三
2 X-2 张三
3 X-3 张三
4 X-4 王五
5 X-5 李四
6 X-6 李四

id Num CreatedBy
1 B-1 张三
2 B-2 张三
3 B-3 张三
4 B-4 李四
5 B-5 李四
6 B-6 赵六

sql代码：
``````    SQL = "select Employee.Name as 员工姓名,count(GoodsSell.Num) as 销售单数量,count(GoodsBuy.Num) as 采购单数量 from Employee left join GoodsSell on Employee.Name=GoodsSell.CreatedBY and CONVERT(varchar(12),GoodsSell.createddate,111) between '" & dtpFromDate.Value & "' and '" & dtpToDate.Value & "' left join Goodsbuy on Employee.Name=Goodsbuy.CreatedBY and CONVERT(varchar(12),Goodsbuy.createddate,111) between '" & dtpFromDate.Value & "' and '" & dtpToDate.Value & "' where Employee.department<>'离职' Group by Employee.Name order by Employee.Name "
``````

``````    SQL = "select Employee.Name as 员工姓名,count(GoodsSell.Num) as 销售单数量 from Employee left join GoodsSell on Employee.Name=GoodsSell.CreatedBY and CONVERT(varchar(12),GoodsSell.createddate,111) between '" & dtpFromDate.Value & "' and '" & dtpToDate.Value & "' where Employee.department<>'离职' Group by Employee.Name order by Employee.Name "
``````

...全文
121 点赞 收藏 11

11 条回复

MS-SQL Server

1.4w+

25.3w+

MS-SQL Server相关内容讨论专区