Hi
有個問題，請教一下大家
資料表如下
CREATE TABLE `idxbib` (
`IDXTYPE` varchar(3) COLLATE utf16_bin NOT NULL DEFAULT '',
`TEXT` longtext CHARACTER SET utf16,
`BRN` int(11) NOT NULL,
`TEXTS` longtext CHARACTER SET utf16,
KEY `IDXBIB0` (`BRN`,`IDXTYPE`),
KEY `IDXBIB1` (`BRN`),
KEY `IDXBIB2` (`IDXTYPE`,`TEXTS`(150))
) ENGINE=InnoDB ;

資料如下
"IDXTYPE","TEXT","BRN","TEXTS"
,
"AU","黃慶萱 ","1067183","黃慶萱 ",
"AU","龔鵬程 ","1067183","龔鵬程 ",
"AU","林明峪 ","1067183","林明峪 ",
"BN","9571312347 :","1067183","9571312347 :",
"LC","8308394","1067183","8308394",
"NN","123","1067183","123",
"NN","8365","1067183","8365",
"PL","臺北市 :","1067183","臺北市 :",
"PR","時報文化,","1067183","時報文化,",
"TI","取經的卡通:","1067183","取經的卡通:",
"TI","西遊記 /","1067183","西遊記 /",
"TI","附錄:原典精選 ","1067183","附錄:原典精選 ",
"TI","旅美舞蹈家王仁璐教授2010年捐贈 ","1067183","旅美舞蹈家王仁璐教授2010年捐贈 ",
"YR","1994[民83] ","1067183","1994[民83] ",

查詢時有多個欄位供使用者輸入，並且找到 BRN
例如書名 【取經】作者【黃慶萱】
query string like
SELECT DISTINCT D1.BRN FROM IDXBIB D1,IDXBIB D2 WHERE (D1.TEXT LIKE "%取經%" AND D1.IDXTYPE="TI") AND (D2.TEXT LIKE "%黃慶萱%" AND D2.IDXTYPE="AU") AND D1.BRN=D2.BRN
or
SELECT * FROM IDXBIB D1,IDXBIB D2 WHERE D1.TEXT LIKE "%取經%" AND D2.TEXT LIKE "%黃%" AND D1.BRN=D2.BRN

這樣確實可以找到，但這種查詢如果遇到更多(例如TI AU PR 那會變得很長)，我希望能用比較簡短的方式，起碼在 FROM TABLENAME 這部份能不能短一些。
有人有其他的方法嗎 ?

我有找到一個方法，請大家看看是不是還有更好的…

SELECT D.BRN FROM IDXBIB D WHERE (D.TEXT LIKE "%黃慶萱%" OR D.TEXT LIKE "%取經%") GROUP BY BRN HAVING COUNT(BRN)>1

雖然是要找 書名 AND 作者，但先用 OR 再用 COUNT 計算 BRN 重複的筆數如果是 >1 代表是2個條件者符合 (那就是 AND 了) 。