《Mysql入門MySQL教程》要點:
本文介紹了Mysql入門MySQL教程,希望對您有用。如果有疑問,可以聯系我們。
導讀:本節內容:學習mysql子查詢IN中LIMIT的用法.項目中遇到的問題:mysql limit使用后報錯.需求是這樣的,我有3張表,infor信息表,mconfig...
MYSQL學習本節內容:
學習mysql子查詢IN中LIMIT的用法.
MYSQL學習項目中遇到的問題:mysql limit使用后報錯.
MYSQL學習需求是這樣的,我有3張表,infor信息表,mconfig物料配置表,maaply物料申請表,要求是讀出申請表中哪些人申請哪些物料
MYSQL學習首先,這樣:
?
SELECT infor.name,infor.phone,infor.add,
mconfig.mname,mapply.acount,from_unixtime(mapply.atime,'%Y-%m-%d') as 'atime'
FROM mapply right JOIN infor ON mapply.uid = infor.uid inner JOIN mconfig ON mapply.mid = mconfig.mid
WHERE mapply.aid
IN (
SELECT aid
FROM `mapply` where state = $state
ORDER BY `atime` , `uid` DESC
LIMIT 0,10
)
MYSQL學習結果報錯了.當時沒注意報的什么錯誤,只是看到LIMIT什么的錯誤,于是修改代碼:
?
SELECT infor.name,infor.phone,infor.add,
mconfig.mname,mapply.acount,from_unixtime(mapply.atime,'%Y-%m-%d') as 'atime'
FROM mapply right JOIN infor ON mapply.uid = infor.uid inner JOIN mconfig ON mapply.mid = mconfig.mid
WHERE mapply.aid
IN (
SELECT aid
FROM `mapply` where state = $state
ORDER BY `atime` , `uid` DESC
)
LIMIT 0,10
MYSQL學習這樣沒有報錯,以為OK了,但是運行后發現,數據有問題
MYSQL學習和單純的讀出申請表的內容不一樣,才發現LIMIT的位置放錯了,于是又把LIMIT發在IN里,結果報錯:
This version of MySQL doesn't yet support ‘LIMIT & IN/ALL/ANY/SOME subquery'
MYSQL學習細看才知道,IN里不支持LIMIT.如何解決呢?
MYSQL學習在IN里再使用一張臨時表,把需要的內容先查出來,修改后的代碼:
?
SELECT infor.name,infor.phone,infor.add,
mconfig.mname,mapply.acount,from_unixtime(mapply.atime,'%Y-%m-%d') as 'atime'
FROM mapply right JOIN infor ON mapply.uid = infor.uid inner JOIN mconfig ON mapply.mid = mconfig.mid
WHERE mapply.aid
IN (
SELECT aid
FROM (SELECT `aid` FROM `mapply` where state = $state
ORDER BY `atime` , `uid` DESC
LIMIT 0,10)AS `tp`
)
維易PHP培訓學院每天發布《Mysql入門MySQL教程》等實戰技能,PHP、MYSQL、LINUX、APP、JS,CSS全面培養人才。
轉載請注明本頁網址:
http://www.snjht.com/jiaocheng/12027.html