我有一個 MySQL 用戶表，如下所示：
CREATE TABLE `users` (
`ID` INT NOT NULL, -- NOTE: in practice, I'm using BINARY(16) and
-- the UUID()-function to create user IDs.
-- 'INT' is only a simplification for this
-- stackoverflow question.
`FirstName` NVARCHAR(100) NOT NULL,
`LastName` NVARCHAR(100) NOT NULL,
-- ...
PRIMARY KEY (`ID`)
);
INSERT INTO `users` (`ID`, `FirstName`, `LastName`)
VALUES (0, 'Albus', 'Dumbledore'),
(1, 'Lord', 'Voldemort'),
(2, 'Harry', 'Potter'),
(3, 'Hermione', 'Granger');
我想創建一個用戶定義的函式，如果（且僅當）結果是唯一的（即只有一行與查詢匹配），它回傳ID匹配 aFirstName和組合的行：LastName
CREATE FUNCTION `FindUser`(`first_name` NVARCHAR(100), `last_name` NVARCHAR(100)
RETURNS INT
BEGIN
RETURN (SELECT `ID`
FROM `users`
WHERE ((first_name is NULL) OR (`FirstName` LIKE CONCAT('%', first_name, '%')))
AND ((last_name Is NULL) OR (`LastName` LIKE CONCAT('%', last_name, '%')))
LIMIT 1);
END
這在以下示例中按預期作業：
SELECT `FindUser`(NULL, 'potter');
-- | ID |
-- |----|
-- | 2 |
SELECT `FindUser`('obama', NULL);
-- | ID |
-- |----|
但是，這不適用于SELECT FindUser(NULL, 'or');，因為令牌'or'可以匹配0 | Albus | Dumbledore和1 | Lord | Voldemort。
我嘗試了以下內容：
SET @cnt = 0;
SET @id = NULL;
SELECT @id = u.id, @cnt = COUNT(id)
FROM users u
WHERE ...; -- same conditions as before
RETURN IF(@cnt = 1, @id, NULL);
但是，這不起作用，因為@idand@cnt將始終被最后一行覆寫。另一種方法是執行兩個查詢，但效率很低。
我怎樣才能最有效地解決問題？
uj5u.com熱心網友回復：
如果您使用的是支持視窗函式的 MySql 版本，您可以進行的簡單修改是有條件地計算行數：
RETURN (
SELECT CASE WHEN count(*) over() = 1 then ID ELSE null END
FROM users
WHERE (first_name is NULL OR FirstName LIKE CONCAT('%', first_name, '%'))
AND (last_name Is NULL OR LastName LIKE CONCAT('%', last_name, '%'))
LIMIT 1
);
演示小提琴
uj5u.com熱心網友回復：
您可以使用聚合并在HAVING子句中設定條件：
CREATE FUNCTION FindUser(first_name NVARCHAR(100), last_name NVARCHAR(100))
RETURNS INT
BEGIN
RETURN (
SELECT MAX(ID)
FROM users
WHERE (first_name IS NULL OR FirstName LIKE CONCAT('%', first_name, '%'))
AND (last_name IS NULL OR LastName LIKE CONCAT('%', last_name, '%'))
GROUP BY NULL -- you can omit this clause
HAVING COUNT(*) = 1
);
END;
請參閱演示。
我懷疑對于表中只有 1 行的邊緣情況，您為函式傳遞的引數都是null您不希望ID回傳的行。
對于這種情況，您應該在WHERE子句中再添加一個條件，以確保至少有一個引數不是null：
WHERE (first_name IS NOT NULL OR last_name IS NOT NULL)
AND (first_name IS NULL OR FirstName LIKE CONCAT('%', first_name, '%'))
AND (last_name IS NULL OR LastName LIKE CONCAT('%', last_name, '%'))
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/536644.html
標籤：数据库数据库数据库
上一篇：將一些行從一個表復制到另一個新表
下一篇：填充空值