Applying Where clause for Order by in SQL

问题:

Would like to sort the following data the way allows in the order of choice of keywords based on like clause:

Data in table:

EmpId EmpLotusNotes
10001 Amit B/India
20002 Bharat C/India
30003 Kyo Jun/Japan
40004 Jee Lee/China
50005 Xavier K/USA

Data to be presented/sorted based on certain country order (Japan, China, India, USA):

EmpId EmpLotusNotes
30003 Kyo Jun/Japan
40004 Jee Lee/China
10001 Amit B/India
20002 Bharat C/India
50005 Xavier K/USA

Note: I cannot create another table that holds the country order or any other change.

回答1:

The problem is the table violates first normal form, EmpLotusNotes should not contain the name of an employee and the country, presumably the country they work in.

You should challenge the reasons why you are not allowed to clean up the structure and the data.

See https://www.google.com.au/search?q=sql+first+normal+form+atomic

The answer, if you still cannot normalise the database after challenging, is create a query for countries, create a query to split the data in the first table into first normal form, then join the two.

An example that works for mysql follows, for MS SQL you would use CHARINDEX instead of INSTR and substring instead of substr.

select employeesWithCountries.*
, countries.sort 
from (
    select empId, empLotusNotes, substr( empLotusNotes, afterStartOfDelimiter ) country from (
        select empId
        , empLotusNotes
        , INSTR( empLotusNotes, '/' ) + 1 as afterStartOfDelimiter 
        from EmployeesLotusNotes
    ) employees
) employeesWithCountries
inner join (
    SELECT 'Japan' as country, 1 as sort
    union
    SELECT 'China' as country, 2 as sort
    union
    SELECT 'India' as country, 3 as sort
    union
    SELECT 'USA' as country, 4 as sort
) countries
on employeesWithCountries.country = countries.country
order by countries.sort, employeesWithCountries.empLotusNotes

Results.

30003    Kyo Jun/Japan   Japan    1
40004    Jee Lee/China   China    2
10001    Amit B/India    India    3
20002    Bharat C/India  India    3
50005    Xavier K/USA    USA      4

回答2:

This should make a trick:

SELECT
   EmpId, EmpLotusNotes
FROM 
   dbo.Table
ORDER BY
   CASE 
      WHEN EmpLotusNotes LIKE '%Japan' THEN 1
      WHEN EmpLotusNotes LIKE '%China' THEN 2 
      WHEN EmpLotusNotes LIKE '%India' THEN 3
      WHEN EmpLotusNotes LIKE '%USA' THEN 4
   END

回答3:

Perhaps:

SELECT
   EmpId, EmpLotusNotes
FROM 
   dbo.Table
ORDER BY
   CASE WHEN EmpLotusNotes LIKE '%Japan' THEN 0 ELSE 1 END,
   CASE WHEN EmpLotusNotes LIKE '%China' THEN 0 ELSE 1 END,
   CASE WHEN EmpLotusNotes LIKE '%India' THEN 0 ELSE 1 END,
   CASE WHEN EmpLotusNotes LIKE '%USA' THEN 0 ELSE 1 END

and here's the DEMO

回答4:

You can use a Common Table Expression to process the raw data first and do your filtering and/or ordering on the processed data later. Something like below. The function in myCol can be changed with any kind of function, even a CASE clause.

WITH T as 
(SELECT EmpId, EmpLotusNotes, SOMEFUNCTION(EmpLotusNotes) as myCol
 FROM Table1
)
SELECT *
FROM T 
WHERE myCol = XXX
ORDER BY myCol