我需要從資料庫中獲取值并將它們放入List<Long>. 我做了一個本地查詢，但它不能正常作業。這是我資料庫中列的圖片：
我需要計算相同的狀態（“W”和“I”代表相同的狀態但由另一個字母表示）。我準備了一個查詢，如下所示：
@Query(value = "SELECT COUNT(CASE WHEN table.status = 'W' OR table.status = 'I' THEN 1 ELSE 0 END) AS WI,"
" COUNT(CASE WHEN table.status = 'S' THEN 1 ELSE 0 END) AS S,"
" COUNT(CASE WHEN table.status = 'N' THEN 1 ELSE 0 END) AS N,"
" COUNT(CASE WHEN table.status = 'D' THEN 1 ELSE 0 END) AS D,"
" COUNT(CASE WHEN table.status = 'E' THEN 1 ELSE 0 END) AS E,"
" COUNT(CASE WHEN table.status = 'T' THEN 1 ELSE 0 END) AS T"
" FROM table.table table"
" WHERE table.id = :id"
" GROUP BY table.status"
" ORDER BY table.status ASC",
nativeQuery = true)
List<Long> query1 (Long id);
但它不能正常作業。當我運行查詢時，我得到以下結果：
但它應該是{2(D),2(E),1(N),1(S),1(T),3(W and I)。你對如何解決它有任何想法嗎？
uj5u.com熱心網友回復：
此查詢回傳您想要的內容：
select count(*) from (
SELECT CASE WHEN status = 'W' OR status = 'I' THEN 'WI' else status end as status
FROM @table
) as a
group by status
order by status
所以也許這是您需要的代碼（您有非常復雜的表名）：
@Query(value = "select count(*) from ("
" SELECT CASE WHEN table.status = 'W' OR table.status = 'I' THEN 'WI' else table.status end as status"
" FROM table.table table "
" WHERE table.id = :id"
" ) as a "
" group by status"
" order by status",
nativeQuery = true)
List<Long> query1 (Long id);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/322751.html
標籤：爪哇 sql sql-server 春天 存储库
上一篇：React:基于道具的設定和更新