T-SQL sorting by a calculated column

问题:

I have a table with these columns: ID, Price, IsMonthlyPrice

How can I select all and sort by weekly price, taking into account that:

if (IsMonthlyPrice = false) then Price is weekly

EDIT:

I need to sort by weekly price

weekly price of a monthly price would be: Price*12/52

回答1:

You don't need to include the calculation twice. You can order on the column number

SELECT 
    ID, 
    Price, 
    IsMonthlyPrice, 
    CASE IsMonthlyPrice
    WHEN 1 THEN Price * 12 / 52
    ELSE price
    END
FROM [TABLE]
    order by 
        4

回答2:

Not sure if this is what you look for but maybe try:

select 
    id,
    case isMonthlyPrice when 'false' then (price*52/12) else price end normalizedPrice
from
    yourTable
order by
    normalizedPrice

回答3:

You can sort calculated columns using two approaches. The more verbal approach (untested pseudocode) is:

select ID, CASE WHEN IsMonthlyPrice = 0 THEN Price * 52 / 12 ELSE Price END AS Price
ORDER BY CASE WHEN IsMonthlyPrice = 0 THEN Price * 52 / 12 ELSE Price END

The more concise approach is:

select * from
(
select ID, CASE WHEN IsMonthlyPrice = 0 THEN Price * 52 / 12 ELSE Price END AS Price
) as derived
ORDER BY derived.Price 

回答4:

I generally wrap the select in a another one. then the column is no longer "calculated"

select Price, ID from (select ID, Price, [calculate the IsMonthly Price] as 'IsMonthlyPrice' from table) order by IsMonthlyPrice

回答5:

You can use a case to calculate different values depending on whether the price is weekly or monthly.

This will calculate an approximate (assuming 31 days per month) price per day to sort on:

select ID, Price, IsMonthlyPrice
from TheTable
order by Price / case IsMonthlyPrice when 1 then 31.0 else 7.0 end