改变SQL查询:基于ID，返回一个字符串-这是可能做到不改变表和存储字符串在那里吗?

Question

我在ASP中有这个SQL查询:

public async Task<IEnumerable<RequestOverview>> getRequestOverview (RequestOverview model)
{
var parameters = new DynamicParameters();
parameters.Add("@ClaimID", model.ClaimID); 
var getOverview = await _sqlconnection.QueryAsync<RequestOverview>($@"SELECT 
TOP 1 Vehicle.ID as claimID, 
Retailer.Name as dealerName, Retailer.Address as dealerAddress, 
Retailer.PostAddress as dealerCity,
Customer.Name as ownerName, Customer.StreetAddress as ownerAdress, 
Customer.Postnumber as ownerZipCode, 
Customer.PostAddress as ownerCity, 
Customer.CellphoneNumber as ownerMobile, 
Customer.Email as ownerEmail,
Invoice.ID as warrantyInvoiceNr, 
Invoice.PaidDate as warrantyPaymentDate, 
Invoice.DueDate as warrantyDueDate, 
Invoice.PaidDate as warrantyInvoiceDate, 
InvoiceStatus.Name as InvoiceStatusID,
Vehicle.RegNumber as vehicleRegNr, Vehicle.Brand as vehicleBrand, 
Vehicle.Model as vehicleModel, Vehicle.YearModel 
as vehicleYearModel, Vehicle.FirstDayInTraffic as vehicleRegDate, 
Vehicle.MeterReading as vehicleMilage, Vehicle.Vinnumber as vehicleVinNr,
Product.Name as warrantyProduct, Premie.Price as warrantyPrice, 
Premie.Months as warrantyDuration, 
Premie.Maxkilometer as warrantyDistance, 
ProductVarySetup.MaxDistance as warrantyMaxKm, 
Contract.ValidFrom as warrantyStartDate, 
Contract.ValidTo as warrantyValidTo, 
Contract.ContractNumber as warrantyContactNr, 
ContractStatus.Name as ContractStatusID
FROM ((Retailer
INNER JOIN Customer ON Retailer.ID = Customer.ID
INNER JOIN Vehicle ON Retailer.ID = Vehicle.ID
INNER JOIN Product ON Retailer.ID = Product.ID
INNER JOIN Premie ON Retailer.ID = Premie.ID
INNER JOIN Contract ON Retailer.ID = Contract.ID
INNER JOIN ProductVarySetup ON Retailer.ID = ProductVarySetup.ID
INNER JOIN ContractStatus ON Contract.ContractStatusID = ContractStatus.ID
INNER JOIN Invoice ON Retailer.ID = Invoice.ID
INNER JOIN InvoiceStatus ON Invoice.InvoiceStatusID = InvoiceStatus.ID))
WHERE (Vehicle.ID = @ClaimID); SELECT SCOPE_IDENTITY();", parameters);
return getOverview;
}

一切正常。但正如你所看到的，我用InvoiceStatus.Name as InvoiceStatusID和INNER JOIN InvoiceStatus ON Invoice.InvoiceStatusID = InvoiceStatus.ID来代替InvoiceStatus的ID。但是，我想更改，以便如果ID为1，我想返回"付费"一词，如果ID不是1，我想返回"未付费"。如果不向InvoiceStatus表添加新列并在那里存储"付费"/"未付费"，是否可以这样做?

Answer 1

删除

INNER JOIN InvoiceStatus ON Invoice.InvoiceStatusID = InvoiceStatus.ID

并替换"InvoiceStatus。名称为InvoiceStatusID，"这个

CASE
WHEN Invoice.InvoiceStatusID = 1 THEN 'Paid'
ELSE 'NotPaid'
END AS InvoiceStatusID,

IIF只在MS SQL Server 2012+上工作。CASE正在任意服务器上运行。

Answer 2

您可以简单地在select语句中执行立即if:

SELECT TOP 1
Vehicle.ID as claimID,
...
IIF (Invoice.InvoiceStatusID = 1, 'Paid', 'Not Paid') AS '<AliasName>'
...

这三个点是剩下的代码。