获取关键字存在的次数
可以说我有以下数据，最后一列关键字不过是其他4列的组合。
╔════╦══════╦════════════╦═════════╦════════════╦════════════════════════════════╗
║ 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 ║
╚════╩══════╩════════════╩═════════╩════════════╩════════════════════════════════╝
现在用户可以输入任何文本字段名称，Add1，Add2，Add3并点击搜索，它应该搜索所有字段。
为此，我正在尝试这个，
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
预期产出：
╔════╦══════╦════════════╦═════════╦════════════╦═══════════╗
║ 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 ║
╚════╩══════╩════════════╩═════════╩════════════╩═══════════╝
但是我将所有频率值都设为1.我做错了什么？
该部分... WHERE ID IN ( ...) ...只会做'是或否'检查，但你要数数。 这必须通过JOIN完成。
尝试这个：
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
为什么不做一个没有这个子查询的简化版本：
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
'按6排序'表示在频率选择范围内的第六个元素
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/76061.html