請教大家,我欲使用SQL SELECT LIKE子句 查詢資料的分類,如下圖示
欲只帶出資料分類代號為1的資料,若使用語言以下語法
SELECT * FROM case
WHERE case_class
LIKE '%1%'
會一併帶出分類代號為11的資料,想請教大家如何使用正確的語法,讓SELECT LIKE子句,只帶出分類代號為1的資料呢?感謝大家的幫忙!
因為分類順序的關係,我再補上*,1,*,成為
SELECT * FROM case WHERE case_class = '1' OR case_class like '1,%' OR case_class like '%,1' OR case_class like '%,1,%'
即可帶出所有的資料,非常感謝 japhenchen 的協助!
最後使用㊣浩瀚星空㊣給的方式,成功又簡潔地帶出資料
SELECT * FROM case WHERE FIND_IN_SET('1', case_class)
謝謝平台,謝謝各位網友!
只取出1?
SELECT * FROM case WHERE case_class = '1'
只取出1及1,*
SELECT * FROM case WHERE case_class = '1' OR case_class like '1,%'
只取出1、1,*、*.1
SELECT * FROM case WHERE case_class = '1' OR case_class like '1,%' OR case_class like '%,1'
加上逗號~
SELECT * FROM case WHERE ',' + case_class + ',' LIKE '%,1,%'
如果確定存入時已照大小排序、且只需帶出分類代號為1的資料
SELECT * FROM case WHERE SUBSTRING(case_class, 1, 1) = '1';
其實,MYSQL就有針對這種類型的欄位,IN用的函式了。如下範例
SELECT * FROM case WHERE FIND_IN_SET('1', case_class)='1';
SELECT * FROM case WHERE FIND_IN_SET('2', case_class)='2';
SELECT * FROM case WHERE FIND_IN_SET('5', case_class)='5';
您好 非常感謝 怎麼會現在才知道有這麼好用的函式了 我試著用 FIND_IN_SET 已成功精簡帶出資料 而且程式也變得更精簡了 非常感謝提供方法
學到了
Thanks for this. didn't know about FIND_In_SEt before. Heather | roofing contractor st petersburg fl
Anti peeping privacy magnetic iPhone 13 pro max case truly guards your privacy. iphone 13 pro max magsafe case
There are so many things I want to share with you guys and it takes a long hours if I do that. For now I share two games with you. Here are criminal finding games and free mahjong games for pc that I want you to check play. Get them by clicking the two links I drop here. Both games are amazing and free to play. Go now and invite your friends to visit the links and download the game.
Thanks for the questions I really enjoyed some of the points you elaborated! pelvic floor physio Barrhaven
SQL isn't that hard when there are tutorials like this one. credit repair laredo