一奥吩，查詢數(shù)據(jù)關(guān)聯(lián)的文件
方法1根據(jù)設(shè)備編號(hào)查詢
//查詢?cè)O(shè)備文件
SELECT
seb.sn AS [設(shè)備編號(hào)],
'http://117.35.99.77:9766/GetFile/' + REVERSE( SUBSTRING ( REVERSE( Files.filePath ), 1, CHARINDEX( '\', REVERSE( Files.filePath ) ) - 1 ) ) AS [查看路徑], 最后一個(gè)出現(xiàn)的字符后的字符串 seb.ArchivePolicyDetailId AS [設(shè)備ID],
Files.TimeUpdate AS [修改時(shí)間],
Files.fileName AS [文件名稱],
Files.sourceFilePath AS [設(shè)備路徑]
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 七天內(nèi)
ORDER BY
修改時(shí)間 DESC
查詢數(shù)據(jù)關(guān)聯(lián)的文件
方法2根據(jù)數(shù)據(jù)關(guān)聯(lián)的文件id查詢
//查詢文件id
SELECT TOP 1 db.fileId AS [文件id],db.sampleNo AS [報(bào)告編號(hào)],db.itemName AS [項(xiàng)目名稱]
from CollectionData AS db
WHERE db.sampleNo LIKE '%(2022)SPD0203%' AND db.itemName
LIKE N'%Cd 228.80(μg/L)%' AND db.IsDelete=0
//合并起來(lái)
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 [修改時(shí)間],
Files.fileName AS [文件名稱],
Files.sourceFilePath AS [設(shè)備路徑]
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
修改時(shí)間 DESC
其他方法
1.獲取“-”前面的字符串：
SUBSTRING( FBILLNO , 1 , CHARINDEX('-',FBILLNO)-1 )
列名 铐达，從第一位取值 假丧，要取的字符串長(zhǎng)度是“-”字符在字段中的位置減1
2.獲取“-”后面的字符串：
SUBSTRING( FBILLNO , CHARINDEX('-',FBILLNO)+1 , len(FBILLNO)-charindex('-',FBILLNO) )
列名 從“-”字符后一位開(kāi)始 盖奈，要取的字符串長(zhǎng)度是字段總長(zhǎng)度減去“-”字符所在位置
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”
