iT邦幫忙

0

mysql搜尋兩個資料表,合併後輸出

  • 分享至 

  • xImage

小弟需要寫一個搜尋的功能,搜尋兩個table的資料合併後再輸出,對小弟來說非常的複雜,請教一下各位大大,這個sql語法該怎麼寫?(請看附的圖片)

假設當搜尋"a"時,使用like搜尋"Name"裡是否有類似的文字(先搜尋table1,之後再搜尋table2),之後再搜尋"Des"裡是否有類似的文字(也是一樣先搜尋table1,之後再搜尋table2)。

想要搜尋的結果為不重覆的資料,所以當在搜尋"Des"時,不重覆已經在"Name"找到的資料。(如附圖)

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
10
一級屠豬士
iT邦大師 1 級 ‧ 2013-12-10 11:30:01
最佳解答

使用MySQL

<pre class="c" name="code">CREATE TABLE footprint(
Uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
Userid VARCHAR(30) NOT NULL,
dCity VARCHAR(10) NOT NULL,
dPlacename VARCHAR(100) NOT NULL,
dRemoveing_footprint VARCHAR(300) NOT NULL
);

CREATE TABLE forum(
Uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
Fid INT UNSIGNED NOT NULL,
Userid VARCHAR(30) NOT NULL,
dCity VARCHAR(10) NOT NULL,
dTopic VARCHAR(100) NOT NULL,
dRemoveing_footprint VARCHAR(300) NOT NULL
);

INSERT INTO footprint(Uid, Userid, dCity, dPlacename, dRemoveing_footprint) VALUES
(1,'周杰倫','台北','京王日式拉麵','今天來到台北的京王'),
(2,'劉德華','台北','山本日本料理','想要吃好吃的日本料理'),
(3,'謝金燕','台中','永康牛肉麵','找到一家好吃的牛肉麵'),
(4,'張學友','高雄','台北珍琳鐵板燒','不如吃拉麵'),
(5,'五月天','台南','王朝日式拉麵','跟在日本比起來');

INSERT INTO forum(Uid, Fid, Userid, dCity, dTopic, dRemoveing_footprint) VALUES
(1,1,'謝金燕','台北','分享好吃的拉麵店','之前比較過兩間拉麵店'),
(2,2,'五月天','台中','正宗川味牛肉麵','不輸給台北的牛肉麵'),
(3,1,'周杰倫','高雄','餐廳推薦','新新鐵板燒一樣好吃'),
(4,4,'周杰倫','台北','Pizza哪間好吃','台北市或新北市都可以'),
(5,2,'五月天','台中','正宗日本懷石料理','不像在台北吃日本拉麵');

CREATE VIEW vIthelp1210 AS 
SELECT Uid, '' AS Fid, Userid, dCity, dPlacename, dRemoveing_footprint
     , dPlacename AS find
     , 1 AS xrank
  FROM footprint
 UNION ALL
SELECT Uid, Fid, Userid, dCity, dTopic, dRemoveing_footprint
     , dTopic AS find
     , 2 AS xrank
  FROM forum
 UNION ALL
SELECT Uid, '' AS Fid, Userid, dCity, dPlacename, dRemoveing_footprint
     , dCity AS find
     , 3 AS xrank
  FROM footprint
 UNION ALL
SELECT Uid, Fid, Userid, dCity, dTopic, dRemoveing_footprint
     , dCity AS find
     , 4 AS xrank
  FROM forum
 UNION ALL
SELECT Uid, '' AS Fid, Userid, dCity, dPlacename, dRemoveing_footprint
     , dRemoveing_footprint AS find
     , 5 AS xrank
  FROM footprint
 UNION ALL
SELECT Uid, Fid, Userid, dCity, dTopic, dRemoveing_footprint
     , dRemoveing_footprint AS find
     , 6 AS xrank
  FROM forum;

---
SELECT DISTINCT Uid, Fid, Userid, dCity, dPlacename, dRemoveing_footprint
  FROM vIthelp1210
 WHERE INSTR(find, '拉麵') > 0
ORDER BY xrank, Uid;

+-----+-----+-----------+--------+--------------------------+--------------------------------+
| Uid | Fid | Userid    | dCity  | dPlacename               | dRemoveing_footprint           |
+-----+-----+-----------+--------+--------------------------+--------------------------------+
|   1 |     | 周杰倫    | 台北   | 京王日式拉麵             | 今天來到台北的京王             |
|   5 |     | 五月天    | 台南   | 王朝日式拉麵             | 跟在日本比起來                 |
|   1 | 1   | 謝金燕    | 台北   | 分享好吃的拉麵店         | 之前比較過兩間拉麵店           |
|   4 |     | 張學友    | 高雄   | 台北珍琳鐵板燒           | 不如吃拉麵                     |
|   5 | 2   | 五月天    | 台中   | 正宗日本懷石料理         | 不像在台北吃日本拉麵           |
+-----+-----+-----------+--------+--------------------------+--------------------------------+

SELECT DISTINCT Uid, Fid, Userid, dCity, dPlacename, dRemoveing_footprint
  FROM vIthelp1210
 WHERE INSTR(find, '日本') > 0
ORDER BY xrank, Uid;

+-----+-----+-----------+--------+--------------------------+--------------------------------+
| Uid | Fid | Userid    | dCity  | dPlacename               | dRemoveing_footprint           |
+-----+-----+-----------+--------+--------------------------+--------------------------------+
|   2 |     | 劉德華    | 台北   | 山本日本料理             | 想要吃好吃的日本料理           |
|   5 | 2   | 五月天    | 台中   | 正宗日本懷石料理         | 不像在台北吃日本拉麵           |
|   5 |     | 五月天    | 台南   | 王朝日式拉麵             | 跟在日本比起來                 |
+-----+-----+-----------+--------+--------------------------+--------------------------------+

SELECT DISTINCT Uid, Fid, Userid, dCity, dPlacename, dRemoveing_footprint
  FROM vIthelp1210
 WHERE INSTR(find, '台北') > 0
ORDER BY xrank, Uid;

+-----+-----+-----------+--------+--------------------------+--------------------------------+
| Uid | Fid | Userid    | dCity  | dPlacename               | dRemoveing_footprint           |
+-----+-----+-----------+--------+--------------------------+--------------------------------+
|   4 |     | 張學友    | 高雄   | 台北珍琳鐵板燒           | 不如吃拉麵                     |
|   1 |     | 周杰倫    | 台北   | 京王日式拉麵             | 今天來到台北的京王             |
|   2 |     | 劉德華    | 台北   | 山本日本料理             | 想要吃好吃的日本料理           |
|   1 | 1   | 謝金燕    | 台北   | 分享好吃的拉麵店         | 之前比較過兩間拉麵店           |
|   4 | 4   | 周杰倫    | 台北   | Pizza哪間好吃            | 台北市或新北市都可以           |
|   2 | 2   | 五月天    | 台中   | 正宗川味牛肉麵           | 不輸給台北的牛肉麵             |
|   5 | 2   | 五月天    | 台中   | 正宗日本懷石料理         | 不像在台北吃日本拉麵           |
+-----+-----+-----------+--------+--------------------------+--------------------------------+
看更多先前的回應...收起先前的回應...

啊他就說資料太多了
小雨大你還把他x6倍
偷笑

miithome iT邦新手 5 級 ‧ 2013-12-10 12:11:27 檢舉

hitomitanaka你好,
這個排序就是我需要的,但是:
因為效能考量(不是一次顯示全部搜尋結果,而需用到limit取第30至第40的"搜尋結果"資料(假設)),所以要先"只"取出Uid,Fid, 之後再利用Fid是否為null,再取其它欄位資料(因為其實還有其它很多欄位,例如:Uid, Userid, dPlacetype, dCity, dPlacename, dfootprint, dRemoveing_footprint, dViewcountmtn, dViewcounttotal, dUsepic....(還有很多)。
如果先只抓符合搜尋條件30~40筆的Uid, Fid,然後再去取這10筆(30~40)的其它欄位資料,這樣子的效能會快很多。

fillano iT邦超人 1 級 ‧ 2013-12-10 12:23:13 檢舉

先不管Uid, Fid了,你有理解小雨大的sql在做什麼嗎?

另外,分頁是在排序之後才有可能做的,並不是在符合搜尋條件的狀況下就做的出來。

fillano iT邦超人 1 級 ‧ 2013-12-10 12:28:52 檢舉

嗯,其實你可以去改小雨大的查詢,在建view的時候就做關鍵字查詢,然後讓view只留下Uid, Fid, xrank三個欄位,排序之後,做limit,然後再join。

<pre class="c" name="code">EXPLAIN SELECT *
  FROM footprint\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: footprint
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: NULL
1 row in set (0.00 sec)

EXPLAIN SELECT *
  FROM footprint
 LIMIT 2\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: footprint
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: NULL

用LIMIT處理筆數是一樣的.
用JOIN是做卡氏積,處理筆數會膨脹.

至於哪個SQL Command執行速度是要看執行計畫,
在不同資料庫及不同版本,可能會有不同的執行計畫.

要考慮效能,最好就是做"重構".
目前的資訊是不夠的.
另外呢,我是用 INSTR(),這會比用LIKE快.

antijava提到:
啊他就說資料太多了
小雨大你還把他x6倍

加量不加價,佛心來的~~~~做菜

miithome iT邦新手 5 級 ‧ 2013-12-10 13:18:13 檢舉

hitomitanaka你好,

小弟受教了,但是用了一下你的方法,效能真的不是很理想...

fillano 說:

嗯,其實你可以去改小雨大的查詢,在建view的時候就做關鍵字查詢,然後讓view只留下Uid, Fid, xrank三個欄位,排序之後,做limit,然後再join。

fillano的方法可能會比較好

fillano iT邦超人 1 級 ‧ 2013-12-10 13:21:43 檢舉

影響查詢速度的因素還有很多,建議你實際跑一跑比較一下。跟MySQL設定也會有關係。

你可以試試看將費大的SQL Command與我的SQL Coomand
混合;

miithome iT邦新手 5 級 ‧ 2013-12-11 13:21:54 檢舉

感謝各位大大的幫忙,最後是照fillano及hitomitanaka所說,將大家的query混合使用(雖然不知"費大"是誰,因為顯示出來的會員ID都是英文,我猜應該是fillano吧)

因為資料龐大,而且還使用了create view,如果不先只抓Uid, Fid出來,更是不得了。
不知道按哪裡為"選為最佳解答",不過小弟有此寫出最後使用的方法,希望能夠幫助到其他人。
1.首先參考hitomitanaka的方法,先create view,並且在此先做關鍵字查詢,但只取Uid, Fid寫入view table,並在此做limit分頁(例如:抓30~40筆符合條件資料)。
2.使用fillano的方法,將Fid為空值的(NULL)去footprint資料表抓,不是空值的則在forum表要抓,這樣子就只需要抓30~40的"全部"資料。

miithome iT邦新手 5 級 ‧ 2013-12-11 13:25:57 檢舉

經過測試,此方法在資料庫資料不多時不適用,資料庫的資料少的話,hitomitanaka的方法會比較簡單,而且快。但是資料庫如果龐大的話,就會快很多了。

8
海綿寶寶
iT邦大神 1 級 ‧ 2013-12-07 11:27:54

今天假日比較沒有人
我先提供一個做法
如果不適用
你就再等一下
這裡有許多 SQL 高手

<pre class="c" name="code">
SELECT * FROM table2
WHERE name LIKE '%a%' OR des LIKE '%a%'

UNION

SELECT uid,"",userid,name,des FROM table1
WHERE name LIKE '%a%' OR des LIKE '%a%'
看更多先前的回應...收起先前的回應...
miithome iT邦新手 5 級 ‧ 2013-12-07 11:59:22 檢舉

antijava您好,
因為重要性的不同,所以尋詢結果的排列順序必需不同:
搜尋結果必須以重要性排列:

  1. table1 Name
  2. table2 Name
  3. table1 Des
  4. table2 Des

另外,兩個資料表中的資料還滿大的(還有很多其它資料,如:Name, Des, place, info, date.....),所以還必須考慮到效能問題(而且會用到limit),是否能先取Uid, Fid出來,如果Fid"不是"空的則在table2裡取其它全部資料,如果"是"空的,表示在table1裡取其它全部資料?

你上面的結果是用哪個值下去查的??
不然很難理解,還要去猜....
你說的越清楚就越容易找出答案.

實在不解其應用情境奧妙之處
只有硬湊出結果如下

<pre class="c" name="code">
SELECT uid,"" as Fid,userid,name,des FROM table1 WHERE name LIKE '%a%'
UNION
SELECT Uid, Fid, Userid, Name, Des FROM table2 WHERE name LIKE '%a%'
UNION
SELECT uid,"" as Fid,userid,name,des FROM table1 WHERE des LIKE '%a%'
UNION
SELECT Uid, Fid, Userid, Name, Des FROM table2 WHERE des LIKE '%a%'

請問兩個資料表有關連嗎?
個別Key值是什麼?

還有最後一句「不重覆已經在"Name"找到的資料」判定為同筆或不同筆資料的條件是什麼??
你的條件描述太含糊了..

fillano iT邦超人 1 級 ‧ 2013-12-09 10:11:50 檢舉

資料表有什麼關連、key值是什麼、條件描述太含糊...會讓人擔心一開始就沒做好正規化XD

miithome iT邦新手 5 級 ‧ 2013-12-09 11:33:37 檢舉

各位前輩大家好,
目前只可以在一個資料表下搜尋,現在想要可以直接搜尋兩個資料表,簡單的說,想要將兩個搜尋合併成一個。
我乾脆直接將目前的query貼上,請各位幫忙看一下如何將它合併搜尋。
*重點是資料表"forum"中多了一個欄位Fid,所以想說看有沒有辦法在第二層時,如果沒有Fid資料的,表示這項是資料表"forum"的,而在"forum"取其它全部資料出來。

搜尋資料表A:

<pre class="c" name="code">$footview = "(Select Uid From `footprint` WHERE (`dPlacename` like '%$placekeyword%' AND `dFoot_lang`='tw') OR (`dPlacename` like '%$placekeywordcn%' AND `dFoot_lang`='cn')) UNION (Select Uid From `footprint` WHERE (`dCity` like '%$placekeyword%' AND `dFoot_lang`='tw') OR (`dCity` like '%$placekeywordcn%' AND `dFoot_lang`='cn')) UNION (Select Uid From `footprint` WHERE (`dRemoveimg_footprint` like '%$placekeyword%' AND `dFoot_lang`='tw') OR (`dRemoveimg_footprint` like '%$placekeywordcn%' AND `dFoot_lang`='cn'))";



<pre class="c" name="code">$footviewa = "$footview LIMIT $npn,$maxnum";



<pre class="c" name="code">$news_result=sysDbSet("SELECT * FROM ($footviewa) as x inner join `mifoot_footprint` as f on f.Uid = x.Uid");

//在此取"footprint"全部欄位資料

miithome iT邦新手 5 級 ‧ 2013-12-09 11:33:59 檢舉

搜尋資料表B:

<pre class="c" name="code">$footview = "(Select Uid From `forum` WHERE (`dTopic` like '%$placekeyword%' AND `dFoot_lang`='tw' AND `dReply`='') OR (`dTopic` like '%$placekeywordcn%' AND `dFoot_lang`='cn' AND `dReply`='')) UNION (Select Uid From `forum` WHERE (`dCity` like '%$placekeyword%' AND `dFoot_lang`='tw' AND `dReply`='') OR (`dCity` like '%$placekeywordcn%' AND `dFoot_lang`='cn' AND `dReply`='')) UNION (Select Uid From `forum` WHERE (`dRemoveimg_footprint` like '%$placekeyword%' AND `dFoot_lang`='tw' AND `dReply`='') OR (`dRemoveimg_footprint` like '%$placekeywordcn%' AND `dFoot_lang`='cn' AND `dReply`=''))";



<pre class="c" name="code">$footviewa = "$footview LIMIT $npn,$maxnum";



<pre class="c" name="code">$news_result=sysDbSet("SELECT * from `mifoot_forum` as t inner join ($footviewa) as b on t.Uid=b.Uid");

在此取"forum"全部欄位資料

這個 table/column name 跟原始的問題又不一樣
我投降了
汗
期待其他高手出手
下雨

fillano iT邦超人 1 級 ‧ 2013-12-09 12:12:15 檢舉

sub query為什麼不能用group by而要用union?

miithome iT邦新手 5 級 ‧ 2013-12-09 12:18:23 檢舉

antijava您好,
其實你第一次回答的已經差不多是我要的結果了:

<pre class="c" name="code">SELECT uid,"" as Fid,userid,name,des FROM table1 WHERE name LIKE '%a%'
UNION
SELECT Uid, Fid, Userid, Name, Des FROM table2 WHERE name LIKE '%a%'
UNION
SELECT uid,"" as Fid,userid,name,des FROM table1 WHERE des LIKE '%a%'
UNION
SELECT Uid, Fid, Userid, Name, Des FROM table2 WHERE des LIKE '%a%'

但是有兩個問題:

  1. 這樣搜尋速度會很慢(因為還有很多其它的資料,而且又加上limit).
  2. table1(footprint)跟table1搜尋結果不能重覆,table2(forum)跟table2不能重覆,但同時兩個表都是用Uid做Key,所以不知道在合併時,而何時用UNION,何時用UNION ALL。
miithome iT邦新手 5 級 ‧ 2013-12-09 12:23:23 檢舉

table1及table2搜尋結果"必須"重覆,因為兩個資料表中的資料是不一樣的。

fillano iT邦超人 1 級 ‧ 2013-12-09 12:32:13 檢舉

$footview沒有需要做union阿?用六個or條件加上group by Uid就好了吧?

另外,$news_result中,不要用*,指定好欄名,tableA的部份,多加一個 NULL as Fid,然後tableA跟tableB的結果就可以union了阿...

既然要合,當初何必分.
變成一堆Table Scan了.

miithome iT邦新手 5 級 ‧ 2013-12-09 12:43:44 檢舉

fillano您好,

  1. table1(footprint)跟table2(forum)的資料是不一樣的.
  2. 你的方法會將table A的結果在前面,而table B在後面,但是搜尋結果排列順序必須為:
    table1(footprint)的dPlacename -> table2(forum)的dTopic -> table1的dCity -> table2的dCity -> table1的dRemoveimg_footprint-> table2的dRemoveimg_footprint
fillano iT邦超人 1 級 ‧ 2013-12-09 13:31:13 檢舉
  1. 我沒有說他資料一樣阿,我只是說這個子查詢並不需要使用union
  2. 這樣喔,那你自求多福吧。如果table1跟table2沒有關連性,結果集也沒關係,那是不可能這樣交錯排序的。(如果table1只有3筆,table2卻有三萬筆,那你要怎麼用這個規則排序?)套句前面網友的話,請交代一下關連性。

把Table結構描述清楚,挑幾筆代表性的資料,另外將最後要達到的結果,好好描述一下.
放那種asdf bala bala的,不容易看懂得.
為什麼這類的話,我時常要說,問問題的人,好像都以為別人有特異
功能,可以看到他那裡的Table跟資料.
最前面講不重複,剛剛又講要重複.

miithome iT邦新手 5 級 ‧ 2013-12-09 16:24:28 檢舉

想說盡量把問題問的簡單一點,反而讓大家更是一頭霧水。
希望附上的圖片有人可以看的懂。

miithome iT邦新手 5 級 ‧ 2013-12-09 16:27:16 檢舉

其中要注意的是"forum"裡多了一個Fid欄位,還有不是"dPlacename",而是"dTopic",所以搜尋結果要多一個"Fid"欄位,及dTopic as dPlacename

你能不能除了圖以外,把Table結構,還有這些資料,用文字方式列出來.
還有到底要不要重複的部份說清楚.
列出來好轉入測試用,不然還要自己打字,有些地方還被遮住...

fillano iT邦超人 1 級 ‧ 2013-12-09 17:03:42 檢舉

先說一下我的理解:

  1. footprint跟forum沒有關連(雖然都有dRemovingimg_footprint這個欄位)
  2. Uid單純只是各個table的unique id
  3. footprint的Userid跟dCity可能跟其他table有關連
  4. forum的Fid、Userid跟dCity可能跟其他table有關連
  5. 搜尋結果要根據:footprint.dPlacename, forum.dTopic, footprint.dCity, forum.dCity, footprint.dRemovingimg_footprint, forum.dRemovingimg_footprint 為優先順序做排序

另外,你會需要用Fid是否是NULL來判斷搜尋結果來自footprint還是forum?(這樣才能用table跟Uid來判斷要到哪個頁面,顯示哪筆資料)

看不出有什麼關聯性
只想到這方式~搖頭
可是效能ㄚ...哭
select uid ,'' ,userid ,dcity ,dplacename ,dremoveing_footprint
from footprint
where dplacename like '%s%' and dremoveing_footprint not like '%s%'
union all
select uid ,'' ,userid ,dcity ,dplacename ,dremoveing_footprint
from footprint
where dplacename like '%s%' and dremoveing_footprint like '%s%'
union all
select uid ,fid ,userid ,dcity ,dplacename ,dremoveing_footprint
from forum
where dplacename like '%s%' and dremoveing_footprint not like '%s%'
union all
select uid ,fid ,userid ,dcity ,dplacename ,dremoveing_footprint
from forum
where dplacename like '%s%' and dremoveing_footprint like '%s%'
union all
select uid ,'' ,userid ,dcity ,dplacename ,dremoveing_footprint
from footprint
where dplacename not like '%s%' and dremoveing_footprint like '%s%'
union all
select uid ,fid ,userid ,dcity ,dplacename ,dremoveing_footprint
from forum
where dplacename not like '%s%' and dremoveing_footprint like '%s%';

miithome iT邦新手 5 級 ‧ 2013-12-09 17:35:14 檢舉

fillano你好,
Userid為會員名稱,與dCity其他欄位無關連,只與與"member" table有關連,但在此搜尋中無關。

"另外,你會需要用Fid是否是NULL來判斷搜尋結果來自footprint還是forum?(這樣才能用table跟Uid來判斷要到哪個頁面,顯示哪筆資料)"
是的,因為效能考量(不是一次顯示全部搜尋結果,會用到limit取第30至第40的資料(假設)),所以要先"只"取出Uid,Fid, 之後再利用Fid是否為null,再取全部其它欄位資料。
網頁顯示搜尋結果時,再以Fid是否為null來判斷來自footprint還是forum。

我幫不上忙
只能打打字
讓邦友們可以方便些
忙

<pre class="c" name="code">
Uid,Userid,dCity,dPlacename,dRemoveimg_footprint
1,周杰倫,台北,京王日式拉麵,今天來到台北的京王
2,劉德華,台北,山本日本料理,想要吃好吃的日本料理
3,謝金燕,台中,永康牛肉麵,找到一家好吃的牛肉麵
4,張學友,高雄,台北珍琳鐵板燒,不如吃拉麵
5,五月天,台南,王朝日式拉麵,跟在日本比起來


Uid,Fid,Userid,dCity,dPlacename,dRemoveimg_footprint
1,1,謝金燕,台北,分享好吃的拉麵店,之前比較過兩間拉麵店
2,2,五月天,台中,正宗川味牛肉麵,不輸給台北的牛肉麵
3,1,周杰倫,高雄,餐廳推薦,新新鐵板燒一樣好吃
4,4,周杰倫,台北,Pizza哪間好吃,台北市或新北市都可以
5,2,五月天,台中,正宗日本懷石料理,不像在台北吃日本拉麵
fillano iT邦超人 1 級 ‧ 2013-12-09 18:31:32 檢舉

下面那個csv的title,還需要把dPlacename改成dTopic

總裁 iT邦好手 1 級 ‧ 2013-12-09 20:30:52 檢舉

我只有一個小問題,是誰設計出這種table和流程的呀??...暈

fillano iT邦超人 1 級 ‧ 2013-12-09 23:10:38 檢舉

先這樣吧:

<pre class="c" name="code">
SELECT 
    B.Uid, 
    B.Fid,
    IF (B.Fid IS NULL, fp.dCity, f.dCity) dCity,
    IF (B.Fid IS NULL, fp.dPlacename, f.dTopic) dPlacename,
    IF (B.Fid IS NULL, fp.dRemoveimg_footprint, f.dRemoveimg_footprint) dRemoveimg_footprint 
FROM (
    SELECT Uid, Fid 
    FROM (
        (
            SELECT Uid, NULL AS Fid 
            FROM footprint 
            WHERE (`dPlacename` like '%拉麵%') OR (`dCity` like '%拉麵%') OR (`dRemoveimg_footprint` like '%拉麵%') 
            GROUP BY Uid
        ) UNION (
            SELECT Uid, Fid 
            FROM forum 
            WHERE (`dTopic` like '%拉麵%') OR (`dCity` like '%拉麵%') OR (`dRemoveimg_footprint` like '%拉麵%') 
            GROUP BY Uid
        )
    ) AS A
) AS B 
LEFT OUTER JOIN footprint fp 
ON B.Fid IS NULL AND B.Uid=fp.Uid 
LEFT OUTER JOIN forum as f 
ON B.Fid IS NOT NULL AND f.Uid=B.Uid AND f.Fid=B.Fid 
ORDER BY dCity, dPlacename, dRemoveimg_footprint
miithome iT邦新手 5 級 ‧ 2013-12-10 01:01:08 檢舉

Hi fillano,

非常感謝你的回覆,再請教一下那第30~40的limit條件應該放在哪裡?

fillano iT邦超人 1 級 ‧ 2013-12-10 07:24:47 檢舉

因為排序後做分頁才有意義,所以也只能加在最後阿。

fillano 大大
他是要請你把 limit 也加上去比較完整

順帶請 fillano 大大另外貼在回答區
這麼專業的答案
如果沒選為最佳答案
就太不仗義了
下雨

miithome iT邦新手 5 級 ‧ 2013-12-10 09:03:11 檢舉

試了一下,排列順序很奇怪,像是random的一樣........

fillano iT邦超人 1 級 ‧ 2013-12-10 09:21:24 檢舉

應antijava大要求,我先轉到回答了,也請你到那裏回應吧汗

4
fillano
iT邦超人 1 級 ‧ 2013-12-10 09:19:48

先這樣吧:

<pre class="c" name="code">
SELECT 
    B.Uid, 
    B.Fid,
    IF (B.Fid IS NULL, fp.dCity, f.dCity) dCity,
    IF (B.Fid IS NULL, fp.dPlacename, f.dTopic) dPlacename,
    IF (B.Fid IS NULL, fp.dRemoveimg_footprint, f.dRemoveimg_footprint) dRemoveimg_footprint 
FROM (
    SELECT Uid, Fid 
    FROM (
        (
            SELECT Uid, NULL AS Fid 
            FROM footprint 
            WHERE (`dPlacename` like '%拉麵%') OR (`dCity` like '%拉麵%') OR (`dRemoveimg_footprint` like '%拉麵%') 
            GROUP BY Uid
        ) UNION (
            SELECT Uid, Fid 
            FROM forum 
            WHERE (`dTopic` like '%拉麵%') OR (`dCity` like '%拉麵%') OR (`dRemoveimg_footprint` like '%拉麵%') 
            GROUP BY Uid
        )
    ) AS A
) AS B 
LEFT OUTER JOIN footprint fp 
ON B.Fid IS NULL AND B.Uid=fp.Uid 
LEFT OUTER JOIN forum as f 
ON B.Fid IS NOT NULL AND f.Uid=B.Uid AND f.Fid=B.Fid 
ORDER BY dCity, dPlacename, dRemoveimg_footprint
看更多先前的回應...收起先前的回應...
fillano iT邦超人 1 級 ‧ 2013-12-10 09:25:17 檢舉

排序的部份,這樣子查詢是把兩個查詢的union後的結果集,join各自的table後成為新的結果集,然後再排序的。排序並不會區分結果是來自footprint或是forum這兩個table。

fillano iT邦超人 1 級 ‧ 2013-12-10 09:28:09 檢舉

不然也許這樣就好了?

<pre class="c" name="code">
SELECT 
    B.Uid, 
    B.Fid,
    IF (B.Fid IS NULL, fp.dCity, f.dCity) dCity,
    IF (B.Fid IS NULL, fp.dPlacename, f.dTopic) dPlacename,
    IF (B.Fid IS NULL, fp.dRemoveimg_footprint, f.dRemoveimg_footprint) dRemoveimg_footprint 
FROM (
    SELECT Uid, Fid 
    FROM (
        (
            SELECT Uid, NULL AS Fid 
            FROM footprint 
            WHERE (`dPlacename` like '%拉麵%') OR (`dCity` like '%拉麵%') OR (`dRemoveimg_footprint` like '%拉麵%') 
            GROUP BY Uid
        ) UNION (
            SELECT Uid, Fid 
            FROM forum 
            WHERE (`dTopic` like '%拉麵%') OR (`dCity` like '%拉麵%') OR (`dRemoveimg_footprint` like '%拉麵%') 
            GROUP BY Uid
        )
    ) AS A
) AS B 
LEFT OUTER JOIN footprint fp 
ON B.Fid IS NULL AND B.Uid=fp.Uid 
LEFT OUTER JOIN forum as f 
ON B.Fid IS NOT NULL AND f.Uid=B.Uid AND f.Fid=B.Fid 
ORDER BY dPlacename, dCity, dRemoveimg_footprint

我猜他要的排序是
1.footprint - dPlacename 有關鍵字
2.forum - dPlacename 有關鍵字
3.footprint - dCity 有關鍵字
4.forum - dCity 有關鍵字
5.footprint - dRemoveimg_footprint 有關鍵字
6.forum - dRemoveimg_footprint 有關鍵字

可能得在 select 的時侯
多塞個用來排序的欄位
疑惑

miithome iT邦新手 5 級 ‧ 2013-12-10 10:31:58 檢舉

Hi fillano/antijava,

如果改成ORDER BY dPlacename, dCity, dRemoveimg_footprint排序也是不對。
排序正如antijava所說,必須以下排列:
1.footprint - dPlacename 有關鍵字
2.forum - dPlacename 有關鍵字
3.footprint - dCity 有關鍵字
4.forum - dCity 有關鍵字
5.footprint - dRemoveimg_footprint 有關鍵字
6.forum - dRemoveimg_footprint 有關鍵字

另外,如果limit放在最後,不是就失去了一開始只抓Uid, Fid的意義了嗎?

fillano iT邦超人 1 級 ‧ 2013-12-10 11:44:35 檢舉
  1. 如果碰到「都有」關鍵字的狀況,你要同時顯示多筆相同的資料嗎(這會增加四種可能性)
  2. 除了根據「有關鍵字」排序,還需要根據欄位內容排序嗎。

另外,這樣前面的SQL要整個推翻從頭來,我的之前理解不夠清楚。

fillano iT邦超人 1 級 ‧ 2013-12-10 11:46:31 檢舉

阿,小雨大回了,你參考他的SQL吧。

fillano提到:
如果碰到「都有」關鍵字的狀況

如果根據他第三個(台北)的例子來看
1.資料只要被挑出一次就好
2.排序仍按照那六個優先次序(以較高優先次序者為主)

什麼系統
怎麼會弄得這麼複雜
暈

我要發表回答

立即登入回答