Windows 内部版本号的大小写函数

Question

第一次来这里 - 感谢一个伟大的社区。

我是SQL查询的新手，所以请原谅基本问题。需要一些帮助。

当 9600 = 'Windows 8.1' 和 14393 = 'Windows 10 TH2' 时，我怎样才能将"BuildNumber0"写成

？这是我的基本查询

SELECT
v_GS_COMPUTER_SYSTEM.Name0,
v_GS_PC_BIOS.SerialNumber0,
v_GS_COMPUTER_SYSTEM.Manufacturer0,
v_GS_COMPUTER_SYSTEM.Model0,
v_GS_OPERATING_SYSTEM.Caption0 AS [OS Type],
v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0,
v_R_System.User_Name0 AS [Last Logged],
v_GS_PC_BIOS.ReleaseDate0 AS [BIOS Date],
v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS [BIOS Version],
v_GS_LOGICAL_DISK.FreeSpace0,
v_GS_OPERATING_SYSTEM.BuildNumber0
FROM v_GS_PC_BIOS
INNER JOIN v_GS_COMPUTER_SYSTEM ON v_GS_PC_BIOS.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
INNER JOIN v_GS_OPERATING_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
INNER JOIN v_R_System ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID
LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID
INNER JOIN v_GS_LOGICAL_DISK ON v_R_System.ResourceID = v_GS_LOGICAL_DISK.ResourceID
WHERE(v_R_System.Operating_System_Name_and0 LIKE '%Workstation%')
AND (v_GS_LOGICAL_DISK.FreeSpace0 IS NOT NULL)
ORDER BY
[BIOS Date];

Answer 1

我会试试这个。根据您使用的 DBMS，语法可能会略有不同。干杯 - 菲利克斯

SELECT           v_GS_COMPUTER_SYSTEM.Name0
, v_GS_PC_BIOS.SerialNumber0
, v_GS_COMPUTER_SYSTEM.Manufacturer0
, v_GS_COMPUTER_SYSTEM.Model0
, v_GS_OPERATING_SYSTEM.Caption0 AS [OS Type]
, v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0
, v_R_System.User_Name0 AS [Last Logged]
, v_GS_PC_BIOS.ReleaseDate0 AS [BIOS Date]
, v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS [BIOS Version]
, v_GS_LOGICAL_DISK.FreeSpace0
,  CASE v_GS_OPERATING_SYSTEM.BuildNumber0
WHEN 9600 THEN 'Windows 8.1'
WHEN 14393 THEN 'Windows 10 TH2'
ELSE v_GS_OPERATING_SYSTEM.BuildNumber0
END AS B_NUM_0    
FROM            v_GS_PC_BIOS 
INNER JOIN      v_GS_COMPUTER_SYSTEM  ON v_GS_PC_BIOS.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID 
INNER JOIN      v_GS_OPERATING_SYSTEM  ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID 
INNER JOIN      v_R_System ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID 
LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID 
INNER JOIN      v_GS_LOGICAL_DISK ON v_R_System.ResourceID = v_GS_LOGICAL_DISK.ResourceID
WHERE           (v_R_System.Operating_System_Name_and0 LIKE '%Workstation%') 
AND             (v_GS_LOGICAL_DISK.FreeSpace0 IS NOT NULL)
ORDER BY        [BIOS Date]

Answer 2

将 select 语句中的v_GS_OPERATING_SYSTEM.BuildNumber0替换为：

CASE
WHEN v_GS_OPERATING_SYSTEM.BuildNumber0 = 9600 THEN'Windows 8.1'
WHEN v_GS_OPERATING_SYSTEM.BuildNumber0 = 14393 THEN 'Windows 10 TH2'
END