我的mysql查询在SQL数据库服务器中正常运行，但是当我在Postgres数据库中运行相同的查询时，发现集不起作用。
以下是我的查询：
SELECT omh.id as idd, omh.modified as modified, omh.modified as modified1,
DATE_FORMAT(omh.modified, '%m/%d/%Y') as only_date, DATE_FORMAT(omh.modified, '%h:%i %p') as only_time
FROM order_memo_history as omh
LEFT JOIN users as usr ON (FIND_IN_SET(usr.user_id, omh.users_id) != 0)
INNER JOIN service_order as so ON omh.order_id = so.o_id
LEFT JOIN users as u ON omh.user_id = u.user_id
WHERE (omh.modified BETWEEN "2017-09-01 06:00:00" AND "2017-10-27 05:59:00")
AND IF((so.merge_company_id='0'),(omh.company_id = 2819), ((omh.order_id = so.o_id AND omh.company_id = so.merge_company_id) OR (so.merge_company_id = 2819
AND (omh.group_id = 2819 OR omh.group_id = '0'))))
GROUP BY idd ORDER BY modified ASC
我遇到错误
ERROR: function find_in_set(integer, text) does not exist
LINE 6: LEFT JOIN users as usr ON (FIND_IN_SET(usr.user_id, omh.user...
我尝试用string_to_array但没有用
最佳答案
PostgreSQL解决方案：
使用功能string_to_array和any可以帮助您获得所需的结果。
以下示例可以使您对如何应用该功能有所了解。
在下面的示例中，假定字符串'2,4,11,5,8,6'是来自字段omh.users_id的逗号分隔的用户ID，值11，1，9是来自字段usr.user_id的用户ID。
select string_to_array( '2,4,11,5,8,6', ',' ) as sa
, '11' = any( string_to_array( '2,4,11,5,8,6', ',' ) ) as matched
, '1' = any( string_to_array( '2,4,11,5,8,6', ',' ) ) as un_matched_1
, '9' = any( string_to_array( '2,4,11,5,8,6', ',' ) ) as un_matched_2
结果将是这样的：
+----------------+---------+--------------+--------------+
| sa | matched | un_matched_1 | un_matched_2 |
| text[] | boolean | boolean | boolean |
+----------------+---------+--------------+--------------+
| {2,4,11,5,8,6} | t | f | f |
+----------------+---------+--------------+--------------+