Get number of times keyword is present

Lets say I have the below data, the last column keyword is nothing but the combination of the other 4 cols.

╔════╦══════╦════════════╦═════════╦════════════╦════════════════════════════════╗
║ ID ║ Name ║    Add1    ║  Add2   ║    Add3    ║            Keyword             ║
╠════╬══════╬════════════╬═════════╬════════════╬════════════════════════════════╣
║  1 ║ John ║ W Brown St ║ Edison  ║ Washington ║ JohnW Brown StEdisonWashington ║
║  2 ║ Paul ║ E High Rd  ║ Peapack ║ New Jersey ║ PaulE High RdPeapackNew Jersey ║
║  3 ║ John ║ Greams Rd  ║ Peapack ║ Washington ║ JohnGreams RdPeapackWashington ║
╚════╩══════╩════════════╩═════════╩════════════╩════════════════════════════════╝

Now user can enter any of the text fields Name, Add1, Add2, Add3 and click on search and it should search in all the fields.

For this, I am trying this,

SELECT *, COUNT(ID) AS FREQUENCY FROM TABA WHERE ID IN
(
 (SELECT ID FROM TABA WHERE KEYWORD LIKE '%WASHINGTON%')
 UNION ALL
 (SELECT ID FROM TABA WHERE KEYWORD LIKE '%JOHN%')
 UNION ALL
 (SELECT ID FROM TABA WHERE KEYWORD LIKE '%PEAPACK%')
)
GROUP BY ID
ORDER BY FREQUENCY

Expected Output:

╔════╦══════╦════════════╦═════════╦════════════╦═══════════╗
║ ID ║ Name ║    Add1    ║  Add2   ║    Add3    ║ Frequency ║
╠════╬══════╬════════════╬═════════╬════════════╬═══════════╣
║  3 ║ John ║ Greams Rd  ║ Peapack ║ Washington ║         3 ║
║  1 ║ John ║ W Brown St ║ Edison  ║ Washington ║         2 ║
║  2 ║ Paul ║ E High Rd  ║ Peapack ║ New Jersey ║         1 ║
╚════╩══════╩════════════╩═════════╩════════════╩═══════════╝

But I am getting all the frequency values as 1. What am I doing wrong?

---

The part ... WHERE ID IN ( ...) ... will just do a 'yes or no' check, but you want counting. That has to be done with a JOIN.

Try this:

SELECT TABA.*, COUNT(sub.ID) AS FREQUENCY FROM TABA JOIN 
(
 (SELECT ID FROM TABA WHERE KEYWORD LIKE '%WASHINGTON%')
 UNION ALL
 (SELECT ID FROM TABA WHERE KEYWORD LIKE '%JOHN%')
 UNION ALL
 (SELECT ID FROM TABA WHERE KEYWORD LIKE '%PEAPACK%')
) sub ON sub.ID=TABA.ID
GROUP BY TABA.ID
ORDER BY FREQUENCY

---

Why not to do a simplified version without that subqueries:

SELECT  ID, Name, Add1, Add2, Add3, COUNT(*) AS FREQUENCY 
  FROM TABA 
 WHERE ID IN
     (SELECT ID 
        FROM TABA 
        WHERE KEYWORD LIKE '%WASHINGTON%'
           OR KEYWORD LIKE '%JOHN%'
           OR KEYWORD LIKE '%PEAPACK%')
GROUP BY ID, Name, Add1, Add2, Add3
ORDER BY 6

'Order by 6' means the sixth element on the select scope which is frequency

---

SELECT TABA.*, sub.FREQUENCY  
FROM
(
  SELECT ID, COUNT(*) as FREQUENCY
  FROM 
  (
   (SELECT ID FROM TABA WHERE KEYWORD LIKE '%WASHINGTON%')
   UNION ALL
   (SELECT ID FROM TABA WHERE KEYWORD LIKE '%JOHN%')
   UNION ALL
   (SELECT ID FROM TABA WHERE KEYWORD LIKE '%PEAPACK%')
  ) a
  GROUP BY ID
) sub 
INNER JOIN TABA ON sub.ID=TABA.ID
ORDER BY FREQUENCY DESC

SQL小提琴

链接地址: http://www.djcxy.com/p/76062.html

上一篇: Symfony2中有多个动态防火墙和CAS服务器

下一篇: 获取关键字存在的次数