MySQL 从 SELECT 语句返回值
我不断收到此错误： 结果由多行组成
我有这个功能：
DROP FUNCTION IF EXISTS db.GetUserIDByCourseID;
CREATE FUNCTION db.`GetUserIDByCourseID`(CourseID int) RETURNS int(11)
BEGIN
SELECT (c.user_id + COALESCE(pi.user_id, 0) + COALESCE(p.user_id, 0))
INTO @user_id
FROM courses c
LEFT JOIN users u
ON u.user_id = c.user_id
LEFT JOIN partners p
ON p.partner_id = c.partner_id
LEFT JOIN partners_individual pi
ON pi.individual_id = c.individual_id;
WHERE c.course_id = CourseID;
SELECT user_type_id
INTO @user_type_id
FROM users
WHERE user_id = @user_id;
RETURN @user_type_id;
END;
I keep getting this error:
Result consisted of more than one row
I have this function:
DROP FUNCTION IF EXISTS db.GetUserIDByCourseID;
CREATE FUNCTION db.`GetUserIDByCourseID`(CourseID int) RETURNS int(11)
BEGIN
SELECT (c.user_id + COALESCE(pi.user_id, 0) + COALESCE(p.user_id, 0))
INTO @user_id
FROM courses c
LEFT JOIN users u
ON u.user_id = c.user_id
LEFT JOIN partners p
ON p.partner_id = c.partner_id
LEFT JOIN partners_individual pi
ON pi.individual_id = c.individual_id;
WHERE c.course_id = CourseID;
SELECT user_type_id
INTO @user_type_id
FROM users
WHERE user_id = @user_id;
RETURN @user_type_id;
END;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
当
SELECT进入变量时，结果集必须恰好由一行组成。在您当前的示例中，第一个 select 语句可能会返回多行；您可以通过手动执行来检查出现问题的CourseID值。我不能说该怎么办，因为这取决于你的设计细节。When
SELECTing into a variable, the result set must consist of exactly one row. In your current example, it seems likely that the first select statement will return more than one row; you can check that by executing it by hand for the values ofCourseIDthat are giving trouble. What to do about it I couldn't say, as that depends on the details of your design.您可以尝试
选择前 1 个。您的一个或两个SELECT语句可能会返回多行。我认为您需要坐下来 1：了解您的数据模型，2：了解您的数据。为了更清楚地了解发生了什么，请单独运行这些查询并查看结果集：
You could try
SELECT TOP 1. Either or both yourSELECTstatements could be returning multiple rows. I think you need to sit down and 1: understand your data model and 2: understand your data.To see what's going on more clearly, run these queries by themselves and look at the result sets: