例如:
SELECT A.service_code, A.comm_code, A.service_kind, A.factory_code
FROM (
SELECT C . service_code , C . comm_code , C . service_kind , C . factory_code , C . auth
FROM tab_service_information C
) AS A
INNER JOIN tab_sys_machine_stat B ON ( A.comm_code = B.comm_code )
WHERE A.service_kind <>2
AND A.service_kind <>3
AND A.factory_code = '01'
AND (
concat
(
'|'
,
A
.
auth
,
'|'
)
LIKE
'%|15|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|2|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|20|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|3|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|4|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|5|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|80|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|81|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|82|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|2|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|20|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|3|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|4|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|5|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|80|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|81|%'
OR concat ( '|' , A . auth , '|' ) LIKE '%|82|%'
)
如果数据库里面auth的值有1,11,2,22,222,在查询2的时候,会把2,22,222三个值同时查出来,这样就不对了
为了解决这个问题:concat( '|', A.auth, '|' ) 在查询前,先将数据库表的字段auth的值加上竖线,然后再进行匹配 在视图的值就变成 |2|,|22|,|222|,在这基础上再LIKE ,
就能准确的查到2这个值了