Mysql交叉表固定列查詢不顯示某些多語言字符

我正在尋求幫助以了解在 MYSQL 中檢索某些字符的問題。我有一個表格，其中包含每個數字系統（例如羅馬、希伯來、泰語、高棉、老撾等）的 unicode 國際數字。表的字符集是utf8，校對規則是utf8_general_ci。

該表的組織如下：

等等，每個數字系統有 18 個數字，從 0 到 10，還有 50、100、500、1000、10000。

MySql 查詢是這樣的：

SELECT NUM_SYS_NAME,
       max(case
             when NUM_ID = 0 then
              TEXT
             else
              'N/A'
           end) as '0',
       max(case
             when NUM_ID = 1 then
              TEXT
             else
              'N/A'
           end) as '1',
       max(case
             when NUM_ID = 2 then
              TEXT
             else
              'N/A'
           end) as '2',
       max(case
             when NUM_ID = 3 then
              TEXT
             else
              'N/A'
           end) as '3',
       max(case
             when NUM_ID = 4 then
              TEXT
             else
              'N/A'
           end) as '4',
       max(case
             when NUM_ID = 5 then
              TEXT
             else
              'N/A'
           end) as '5',
       max(case
             when NUM_ID = 6 then
              TEXT
             else
              'N/A'
           end) as '6',
       max(case
             when NUM_ID = 7 then
              TEXT
             else
              'N/A'
           end) as '7',
       max(case
             when NUM_ID = 8 then
              TEXT
             else
              'N/A'
           end) as '8',
       max(case
             when NUM_ID = 9 then
              TEXT
             else
              'N/A'
           end) as '9',
       max(case
             when NUM_ID = 10 then
              TEXT
             else
              'N/A'
           end) as '10',
       max(case
             when NUM_ID = 50 then
              TEXT
             else
              'N/A'
           end) as '50',
       max(case
             when NUM_ID = 100 then
              TEXT
             else
              'N/A'
           end) as '100',
       max(case
             when NUM_ID = 500 then
              TEXT
             else
              'N/A'
           end) as '500',
       max(case
             when NUM_ID = 1000 then
              TEXT
             else
              'N/A'
           end) as '1000',
       max(case
             when NUM_ID = 10000 then
              TEXT
             else
              'N/A'
           end) as '10000'
  from numerals
 WHERE NUM_ID IN
       (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 50, 100, 500, 1000, 10000)
 GROUP BY NUM_SYS_NAME

結果按預期在交叉表中正確檢索和格式化，除了從 1 到 4 的羅馬數字以及 9、50、100、1000、10000。因此，例如，羅馬數字 5 將顯示為 V，3 顯示為 N /一種。如果我將羅馬數字 3 的值設定為 ZZZ 而不是 III 則它會在查詢結果中正確顯示。

I have experimented with various collations and also setting the values in TEXT field to other characters for Roman numeral system and have found that any character from O to Z is displayed properly, however, anything else results in an N/A value, hence all numerals starting with I, L, D, C in Roman system are not retrieved. I am cracking my head now for 2 days to find the root cause of the issue and believe this has to do something a collation and mixing multiple languages, but not very sure, moreover, it is bizarre that the issue is with basic latin characters rather than the exotic ones.

Would appreciate your advice and to be pointed to the right direction. So far, could not even find a similar issue via search or help docs.

Thanks in advance! Alex

P.S. Table definition: numerals Table definition

P.S. 2 Fiddle: https://www.db-fiddle.com/f/op6eAg6s6mDWJiFRYXyYFS/3

uj5u.com熱心網友回復：

編碼不是問題，而是使用 MAX()

max(case when NUM_ID = 4 then TEXT else 'N/A' end) as '4', < -字串'N/A'是更大的比串'IV'，按字母順序，然后，該'N/A'被選擇過'IV'，或任何具有比較小的排序順序'N/A'。這就是這封信'O'起作用的原因，因為它在'N'.

一種解決方法是用'N/A'值替換字串NULL：

MAX(case when NUM_ID = 4 then TEXT else NULL end) as '4'

標籤：mysql

上一篇：如何使用Java在androidstudio中制作帶有房間庫的表格？

下一篇：sql查詢以獲取等于和不等于條件的記錄