SQL 汇总问题

fs33312

需求：用SQL实现想要的结果：8/5/18当天信息汇总；如图红框处。
第一列：人名
第二列： 总条数
第三列：Ｄｕｐ列标Ｙ的总条数。


可是写出来的是中间那样，名字不合并。



Sql = "select [NH Name], count([File Name]) as [Rcvd #], null as [Dup #] from [Sheet1$a1:F] where date >= #8/5/2018# group by [NH Name] union all " _
        & "select [NH Name],null,count(Dup) as [Dup #] from [Sheet1$a1:F] where Dup =""Y"" and date >=#8/5/2018# group by [NH Name]"
Sql = "select * from ( " & Sql & ") where [NH Name] is not null"






Received.zip (16.79 KB, 下载次数: 16)

2018-9-4 21:18 上传

点击文件名下载附件

as42065300

1. Sql = "select a.*,b.[Dup #] from " _
   
2.      & "((select [NH Name], count([File Name]) as [Rcvd #] from [Sheet1$a1:F] where date >= #8/5/2018# group by [NH Name])a" _
   
3.      & " inner Join " _
   
4.      & " (select [NH Name],count(Dup) as [Dup #] from [Sheet1$a1:F] where Dup ='Y' and date >=#8/5/2018# group by [NH Name])b" _
   
5.      & " on a.[NH Name]=b.[NH Name])"

复制代码

opiona

1.     Sql = ""
   
2.     Sql = Sql & "select [NH Name],COUNT([File Name]) as [Rcvd], SUM(IIF([Dup]='Y',1,0)) as [Dup]"
   
3.     Sql = Sql & " FROM [Sheet1$a1:F]"
   
4.     Sql = Sql & " WHERE [date] >= #8/5/2018#"
   
5.     Sql = Sql & " GROUP BY [NH Name]"

复制代码