我想創建一個 SELECT 查詢來選擇 TestID 為 34 的所有名稱，但還要確定串列中的下一個名稱Active是行為 0 且前一個活動行為 2、3 或 4 但不是 1 或 0。
我想在一個單獨的，以確定這些結果的“虛擬”稱為列Status，其中所有行Active是2，3或4 “完成”之后的下一行的最后Active2，3或4 “下一步”每隔一行之后可以識別為只是空白。
什么樣的邏輯可以用來確定這些型別的結果？
uj5u.com熱心網友回復：
/* This CTE is just to provide mock data to test the query */
WITH table_name (`ID`, `TestID`, `Name`, `Active`) AS (
SELECT 1, 34, 'Jackson', 2 UNION ALL
SELECT 2, 34, 'Jim', 4 UNION ALL
SELECT 3, 34, 'Jerry', 3 UNION ALL
SELECT 4, 34, 'Jackie', 0 UNION ALL
SELECT 5, 34, 'John', 0 UNION ALL
SELECT 6, 34, 'Kaleb', 0
)
SELECT
`Name`,
`Active`,
CASE
/* When current row Active is 2, 3 or 4 then Status is Done */
WHEN `Active` IN (2, 3, 4) THEN 'Done'
/* When current row Active is 0 and previous row Active is 2, 3 or 4 then Status is Next */
WHEN `Active` = 0 AND LAG(`Active`) OVER (ORDER BY `ID`) IN (2, 3, 4) THEN 'Next'
/* Otherwise Status is an empty string */
ELSE ''
END AS `Status`
FROM `table_name`
WHERE `TestID` = 34
ORDER BY `ID` ASC;
在此處閱讀有關視窗函式的更多資訊
uj5u.com熱心網友回復：
看起來您不需要那些空白status行，如果是這樣，您只需要知道下一個將是哪個。為了實作這一點，我們需要按某種順序排列資料 [讓我們使用id它] 以下代碼段可以提供幫助
select names, active,
'Done' as status
from <TableName>
where TestID = 34 and active in (2,3,4) order by id
union all
select * from (
select names, active,
'Next' as status
from <TableName>
where TestID = 34 and active in (0,1) order by id limit 1
) next
但是，如果還需要空白狀態行，那么我們可以再添加一個聯合來實作它
union all
select * from (
select names, active,
'' as status
from <TableName>
where TestID = 34 and active in (0,1) order by id limit <aBigNumber> offset 2
) blank
aBigNumber can be 18446744073709551615 (max Of Unsigned BIGINT)
那么最終的 SQL 看起來像
select names, active,
'Done' as status
from <TableName>
where TestID = 34 and active in (2,3,4) order by id
union all
select * from (
select names, active,
'Next' as status
from <TableName>
where TestID = 34 and active in (0,1) order by id limit 1
) next
union all
select * from (
select names, active,
'' as status
from <TableName>
where TestID = 34 and active in (0,1) order by id limit 18446744073709551615 offset 2
) blank
為什么使用子查詢：因為我們不能limit在兩者之間使用union
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/385473.html
標籤：mysql