我有一个表,用来跟踪我访问某个目录中的文件的次数。以下是一个示例...
DirectoryName FileName
A Ab.pdf
B bc.pdf
C cd.pdf
A de.pdf
A kl.mp3
B pq.pdf
B pq.pdf
B pq.pdf每次访问一个文件时,都会添加它的目录和名称。
另外,请注意一个文件不能出现在多个目录中。
请单击here查看本文所基于的表格示例。
我希望输出是这样的……
Directory DirectoryCount FileName FileCount
A 3 Ab.pdf 1
de.pdf 1
kl.pdf 1
B 4 bc.pdf 1
pq.pdf 3
C 1 cd.pdf 1这里DirectoryCount是目录被访问的次数,FileCount是文件被访问的次数。
请单击here查看我希望模拟的输出示例,尽管我希望每个文件只出现一次。
发布于 2017-04-14 11:27:46
请尝试以下操作...
SELECT CASE
WHEN directoryCountFinder.DirectoryName = LAG( directoryCountFinder.DirectoryName ) OVER ( ORDER BY directoryCountFinder.DirectoryName ) THEN
''
ELSE
directoryCountFinder.DirectoryName
END AS DirectoryName,
CASE
WHEN directoryCountFinder.DirectoryName = LAG( directoryCountFinder.DirectoryName ) OVER ( ORDER BY directoryCountFinder.DirectoryName ) THEN
''
ELSE
CONVERT( VARCHAR( 10 ),
DirectoryCount )
END AS DirectoryCount,
FileName,
FileCount
FROM ( SELECT DirectoryName AS DirectoryName,
COUNT( DirectoryName ) AS DirectoryCount
FROM tblTable
GROUP BY DirectoryName
) directoryCountFinder
JOIN ( SELECT DirectoryName AS DirectoryName,
FileName AS FileName,
COUNT( FileName ) AS FileCount
FROM tblTable
GROUP BY DirectoryName,
FileName
) fileCountFinder ON directoryCountFinder.DirectoryName = fileCountFinder.DirectoryName;当我在使用所提供的样本数据创建的测试数据库上测试我的语句时,输出按照指定的方式发生。
我用来形成我的声明的逻辑是,我们需要计数每个目录出现的次数,以及每个文件在所提供的列表中出现的次数。
为了计算每个目录出现的次数,我使用了以下查询...
SELECT DirectoryName AS DirectoryName,
COUNT( DirectoryName ) AS DirectoryCount
FROM tblTable
GROUP BY DirectoryName我使用了一个类似的查询来计算每个文件出现的次数...
SELECT DirectoryName AS DirectoryName,
FileName AS FileName,
COUNT( FileName ) AS FileCount
FROM tblTable
GROUP BY DirectoryName,
FileName这里包含了DirectoryName,因为我需要它来连接来自这个子查询的结果和来自另一个子查询的结果。
连接两个子查询后,除了FileName和FileCount的每个值都重复之外,我们得到了一个看起来像所需输出的数据集。因此,我应用CASE语句来测试当前记录中DirectoryName的值是否与前一条记录中的值相同,如果相同,则输出空字符串('')而不是字段值。
由于DirectoryCount是一个数字,因此如果您只是列出DirectoryCount的值,那么输出中该字段中的所有值也将是数字,包括将显示为0的空字符串''。但是如果我们将DirectoryCount格式化为输出字符串,那么空字符串将显示为空字符串,即空值,因此使用CONVERT( VARCHAR( 10 ), DirectoryCount )。
如果您有任何问题或意见,请随时发表相应的评论。
发布于 2017-04-14 16:01:14
试试这个:
DECLARE @table TABLE (DirectoryName NVARCHAR(1),FileNames NVARCHAR(10))
INSERT INTO @table VALUES
('A','Ab.pdf'),('B','bc.pdf'),('C','cd.pdf'),('A','de.pdf'),('A','kl.mp3')
SELECT * FROM @table -- your table
;WITH A AS (
SELECT DirectoryName, COUNT(DirectoryName) DirectoryCount FROM @table
GROUP BY DirectoryName )
SELECT CASE WHEN rowid = 1 THEN A.DirectoryName ELSE '' END DirectoryName,
CASE WHEN rowid = 1 THEN CONVERT(VARCHAR,A.DirectoryCount) ELSE '' END DirectoryCount,
B.FileNames,C.Filecount
FROM A LEFT JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY DirectoryName ORDER BY DirectoryName)
rowid, DirectoryName, FileNames FROM @table
) B ON A.DirectoryName = B.DirectoryName LEFT JOIN
(SELECT DirectoryName,FileNames,COUNT(FileNames) Filecount FROM @table GROUP BY FileNames,DirectoryName)
C ON C.FileNames = B.FileNames AND B.DirectoryName = C.DirectoryName --your output发布于 2017-04-25 10:29:39
我不能让我的本地或SQLfiddle服务器工作来检查,但是你需要这样的东西:
select
DirectoryName,
count(*) over( partition by directoryName) as DirectoryCount,
FileName,
count(*) over (partition by directoryname,filename) as filenameCount
from tblTable
order by DirectoryName,filename要获取间距,请使用前端检测DirectoryName中的更改。如果需要,您还可以选择ROW_NUMBER OVER (partition by DirectoryName)并仅显示1
还有此选项(同样,前端用于删除重复项)
select d.DirectoryName, d.DirectoryCount , f.Filename,f.FileNameCount from
(
select DirectoryName,
count(*)as DirectoryCount
from tblTable
group by DirectoryName
) as d
inner join
(
select DirectoryName, FileName,
count(*)as filenameCount
from tblTable
group by DirectoryName,FileName
) as f
on d.directoryName=f.DirectoryName
order by DirectoryName, FileNamehttps://stackoverflow.com/questions/43404497
复制相似问题