SELECT s.BOOKID as bookid,s.CLASSID as classid, s.GQJ_CLASS as gqjClass,s.GQJ_CLASSIFY as gqjClassify, s.GQJ_STANDARDID as gqjStandardid, s.GQJ_STANDARD as gqjStandard,s.FACTORY_NUM as factoryNum, s.PK_USERLOCALEID as pkUserlocaleid, s.USE_ADDRESS_NAME, s.USE_ORGID as orgid, s.USE_ORG as orgname,s.NEXT_TEST_TIME as nextTestTime, s.CLASSIFYID as classifyid, s.COMMENT_STATUS as commentStatus,s.ISPLAN as isConsumables,1 AS num,1 AS collectedNum,s.I_CODE as iCode,USE_BZID as deptid, USE_BZNAME as deptname FROM gqj_gqjbook s WHERE s.ISPLAN = 0 and USE_RECORD_STATUS="2" and STATUS="0" and (COMMENT_STATUS!='0' or ISNULL(COMMENT_STATUS) or COMMENT_STATUS = '' ) and USE_BZID = '860dc38c2bed4130b3d0746cee8dbf2e' and PK_USERLOCALEID='0346c06203c84e398d3c4381d5b34b7a' and I_CODE IN ( '030080000000000000003134' ) UNION SELECT any_value(s.BOOKID) as bookid,any_value(s.CLASSID) as classid, any_value(s.GQJ_CLASS) as gqjClass, any_value(s.GQJ_CLASSIFY) as gqjClassify, any_value(s.GQJ_STANDARDID) as gqjStandardid, any_value(s.GQJ_STANDARD) as gqjStandard,any_value(s.FACTORY_NUM) as factoryNum, any_value(s.PK_USERLOCALEID) as pkUserlocaleid, any_value(s.USE_ADDRESS_NAME) as useAddressName, any_value(s.USE_ORGID) as orgid, any_value(s.USE_ORG) as orgname, any_value(s.NEXT_TEST_TIME) as nextTestTime, any_value(s.CLASSIFYID) as classifyid, any_value(s.COMMENT_STATUS) as commentStatus,any_value(s.ISPLAN) as isConsumables,COUNT( 0 ) AS num,count(0) AS collectedNum, any_value(s.I_CODE) as iCode, any_value(USE_BZID) as deptid,any_value(USE_BZNAME) as deptname FROM gqj_gqjbook s WHERE s.ISPLAN = 1 and USE_RECORD_STATUS="2" and STATUS="0" and (COMMENT_STATUS!='0' or ISNULL(COMMENT_STATUS) or COMMENT_STATUS = '' ) and USE_BZID = '860dc38c2bed4130b3d0746cee8dbf2e' and PK_USERLOCALEID='0346c06203c84e398d3c4381d5b34b7a' and I_CODE IN ( '030080000000000000003134' ) GROUP BY GQJ_STANDARDID order by any_value(commentStatus), any_value(gqjClass), any_value(gqjClassify), any_value(gqjStandard);

这是一个 SQL 查询语句，用于从 gqj_gqjbook 表中获取符合条件的数据，并按照一定的规则排序。具体的查询条件包括：ISPLAN=，USE_RECORD_STATUS="2"，STATUS=""，COMMENT_STATUS!='' 或 COMMENT_STATUS 为空，USE_BZID='860dc38c2bed413b3d0746cee8dbf2e'，PK_USERLOCALEID='0346c06203c84e398d3c4381d5b34b7a'，I_CODE='030080000000000000003134'。如果符合条件，会返回 BOOKID、CLASSID、GQJ_CLASS、GQJ_CLASSIFY、GQJ_STANDARDID、GQJ_STANDARD、FACTORY_NUM、PK_USERLOCALEID、USE_ADDRESS_NAME、USE_ORGID、USE_ORG、NEXT_TEST_TIME、CLASSIFYID、COMMENT_STATUS、ISPLAN、num、collectedNum、I_CODE、deptid、deptname 等字段的值。

相关问题

select bk.bookname,get_borrower_name(bk.bookid) as borrower_name from books bk join borrow_info bi on bk.bookid = bi.bookid where bk.in_out = true and bi.return_date is null and get_borrower_name(bk.bookid) = '张三';怎么修改可以使它返回多行数据

要想让查询语句返回多行数据，可以将它改写成存储过程或者使用临时表来存储查询结果。下面是一个使用存储过程的例子：

首先，创建一个存储过程，将查询结果存储到一个临时表中：

CREATE PROCEDURE get_borrower_books(IN p_borrower_name CHAR(10))
BEGIN
    DROP TEMPORARY TABLE IF EXISTS temp_books;
    CREATE TEMPORARY TABLE temp_books (
        bookname CHAR(20),
        borrower_name CHAR(10)
    );

    INSERT INTO temp_books
    SELECT bk.bookname, get_borrower_name(bk.bookid) as borrower_name 
    FROM books bk 
    JOIN borrow_info bi ON bk.bookid = bi.bookid 
    WHERE bk.in_out = true 
    AND bi.return_date IS NULL 
    AND get_borrower_name(bk.bookid) = p_borrower_name;

    SELECT * FROM temp_books;
END;

然后，执行该存储过程，传入参数 '张三' 来获取结果：

CALL get_borrower_books('张三');

这将返回一个包含多行数据的结果集，每一行包含一本书的名称和借阅者的姓名。

CREATE DEFINER=`root`@`localhost` FUNCTION `get_borrower_name`(p_bookid char(20)) RETURNS char(10) CHARSET utf8mb4 begin declare v_name char(10); select name into v_name from borrowers b join borrow_info bi on b.id = bi.id join books bk on bk.bookid = bi.bookid where bi.bookid = p_bookid; return v_name; end select bk.bookname,get_borrower_name(bk.bookid) as borrower_name from books bk join borrow_info bi on bk.bookid = bi.bookid where bk.in_out = true and bi.return_date is null and get_borrower_name(bk.bookid) = '张三';出现 Result consisted of more than one row错误

这个错误可能是因为你的`get_borrower_name()`函数返回了多行数据，而你在查询中使用了这个函数，并且设置了`get_borrower_name(bk.bookid) = '张三'`的条件，这样可能会导致查询结果出现多行数据。可以尝试修改查询条件，或者修改`get_borrower_name()`函数，让它只返回一行数据。比如，你可以修改函数，让它只返回第一个匹配的借阅者姓名：

CREATE DEFINER=`root`@`localhost` FUNCTION `get_borrower_name`(p_bookid char(20)) RETURNS char(10) CHARSET utf8mb4
BEGIN
  DECLARE v_name CHAR(10);
  SELECT name INTO v_name FROM borrowers b
  JOIN borrow_info bi ON b.id = bi.id
  JOIN books bk ON bk.bookid = bi.bookid
  WHERE bi.bookid = p_bookid LIMIT 1;
  RETURN v_name;
END

这个函数使用了`LIMIT 1`语句，表示只返回第一行匹配的数据。这样就可以避免函数返回多行数据的问题了。如果你还是想保留原来的函数逻辑，可以尝试修改查询条件，让它返回唯一的一行数据。比如，你可以修改查询条件，让它只返回第一本被借阅的图书：

SELECT bk.bookname, get_borrower_name(bk.bookid) AS borrower_name
FROM books bk
JOIN (
  SELECT bookid FROM borrow_info
  WHERE return_date IS NULL AND borrower_name = '张三'
  ORDER BY borrow_date ASC LIMIT 1
) bi ON bk.bookid = bi.bookid
WHERE bk.in_out = true;

这个查询使用了子查询和ORDER BY语句，表示只返回第一本被借阅的图书。这样也可以避免查询结果出现多行数据的问题。