一，查询数据关联的文件

方法1根据设备编号查询

//查询设备文件
SELECT
    seb.sn AS [设备编号],
    'http://117.35.99.77:9766/GetFile/' + REVERSE( SUBSTRING ( REVERSE( Files.filePath ), 1, CHARINDEX( '\', REVERSE( Files.filePath ) ) - 1 ) ) AS [查看路径], 最后一个出现的字符后的字符串 seb.ArchivePolicyDetailId AS [设备ID],
    Files.TimeUpdate AS [修改时间],
    Files.fileName AS [文件名称],
    Files.sourceFilePath AS [设备路径] 
FROM
    dbo.CollectionFile AS Files,
    dbo.ArchivePolicyDetailDevices AS seb 
WHERE
    Files.archiveDetailRuleId = seb.ArchivePolicyDetailId 
    AND seb.sn LIKE '%XYSY-JSC-0044%' 
    AND DateDiff( dd, Files.TimeUpdate, getDate( ) ) <= 7 七天内 
ORDER BY
    修改时间 DESC
        

查询数据关联的文件

方法2根据数据关联的文件id查询

//查询文件id
        SELECT TOP 1  db.fileId AS [文件id],db.sampleNo  AS [报告编号],db.itemName AS [项目名称]
         from  CollectionData  AS db   
     WHERE   db.sampleNo LIKE '%(2022)SPD0203%'  AND  db.itemName 
        LIKE N'%Cd 228.80(µg/L)%'  AND db.IsDelete=0

//合并起来
SELECT DISTINCT
    Files.fileName,
    'http://117.35.99.77:9766/GetFile/' + REVERSE( SUBSTRING ( REVERSE( Files.filePath ), 1, CHARINDEX( '\', REVERSE( Files.filePath ) ) - 1 ) ) AS [查看路径],
    Files.TimeUpdate AS [修改时间],
    Files.fileName AS [文件名称],
    Files.sourceFilePath AS [设备路径] 
FROM
    dbo.CollectionFileHistory AS Files,
    dbo.CollectionData AS db 
WHERE
    Files.fileId = (
    SELECT TOP
        1 db.fileId AS [文件id] 
    FROM
        CollectionData AS db 
    WHERE
        db.sampleNo LIKE '%(2022)SPD0203%' 
        AND db.itemName LIKE N'%Cd 228.80(µg/L)%' 
        AND db.IsDelete= 0 
    ) 
    AND DateDiff( dd, Files.TimeUpdate, getDate( ) ) <= 7 
ORDER BY
    修改时间 DESC
        

其他方法

1.获取“-”前面的字符串：

SUBSTRING(  FBILLNO  ,          1           ,  CHARINDEX('-',FBILLNO)-1  )

列名 ，从第一位取值 ，要取的字符串长度是“-”字符在字段中的位置减1


2.获取“-”后面的字符串：

SUBSTRING(  FBILLNO  ,    CHARINDEX('-',FBILLNO)+1 ,    len(FBILLNO)-charindex('-',FBILLNO)  )

 列名   从“-”字符后一位开始  ，要取的字符串长度是字段总长度减去“-”字符所在位置


FreeSql.Generator -Razor 1 -NameOptions 0,0,0,1 -NameSpace LinCms.Core.Entities -DB “SqlServer,Data Source=.;Initial Catalog=StudentInfo;Integrated Security=False;User ID=sa;Password=1944887541”