选择具有超过4列匹配值的玩家
问题描述：
I want to select all players in a table that has 10 columns, where player is the key, if they have more than 4 matching columns. for say, if col1, col2, col3, and col4 match, then list all users with the same columns but if col1, col5, col4, and col8 match also list list all users with the same columns. So basically if the player has at least 4 matching columns with other players no matter if it col1 = col1 or col8 = col8 display the users with matching columns
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>logs</title>
<meta name="GENERATOR" content="HeidiSQL 9.4.0.5125">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style type="text/css">
thead tr {background-color: ActiveCaption; color: CaptionText;}
th, td {vertical-align: top; font-family: "Tahoma", Arial, Helvetica, sans-serif; font-size: 8pt; padding: 3px; }
table, td {border: 1px solid silver;}
table {border-collapse: collapse;}
thead .col0 {width: 148px;}
thead .col1 {width: 300px;}
thead .col2 {width: 239px;}
thead .col3 {width: 173px;}
thead .col4 {width: 133px;}
thead .col5 {width: 107px;}
thead .col6 {width: 231px;}
thead .col7 {width: 268px;}
thead .col8 {width: 281px;}
</style>
</head>
<body>
<table caption="logs (7 rows)">
<thead>
<tr>
<th class="col0">user</th>
<th class="col1">gpuid</th>
<th class="col2">cpuid</th>
<th class="col3">winkey</th>
<th class="col4">desktopname</th>
<th class="col5">windowsuser</th>
<th class="col6">bios</th>
<th class="col7">processor</th>
<th class="col8">video</th>
</tr>
</thead>
<tbody>
<tr>
<td class="col0">test1</td>
<td class="col1">4d73e26e4a60043efcab3f4c2f084fec7b930c80784f2e5affabdab4d68f36f1</td>
<td class="col2">13-1970169159-1818588270-1231384169</td>
<td class="col3">00331-10000-00001-AA429</td>
<td class="col4">TRUIX-DESKTOP</td>
<td class="col5">Truix</td>
<td class="col6">ALASKA - 1072009</td>
<td class="col7">Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz</td>
<td class="col8">NVIDIA GeForce GTX 1070</td>
</tr>
<tr>
<td class="col0">test2</td>
<td class="col1">4d73e26e4a60043efcab3f4c2f084fec7b930c80784f2e5affabdab4d68f36f1</td>
<td class="col2">13-1970169159-1818588270-1231384169</td>
<td class="col3">00331-10000-00001-AA429</td>
<td class="col4">TRUIX-DESKTOP</td>
<td class="col5">Truix</td>
<td class="col6">ALASKA - 1072009</td>
<td class="col7">Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz</td>
<td class="col8">NVIDIA GeForce GTX 1070</td>
</tr>
<tr>
<td class="col0">test3</td>
<td class="col1">4d73e26e4a60043efcab3f4c2f084fec7b930c80784f2e5affabdab4d68f36f1</td>
<td class="col2">13-1970169159-1818588270-1231384169</td>
<td class="col3">00331-10000-00001-AA429</td>
<td class="col4">TRUIX-DESKTOP</td>
<td class="col5">Truix</td>
<td class="col6">ALASKA - 1072009</td>
<td class="col7">Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz</td>
<td class="col8">NVIDIA GeForce GTX 1070</td>
</tr>
<tr>
<td class="col0">test4</td>
<td class="col1">d08b4d14ba5a5d296e228b193d7ec25af31a002b949ceb84e603b6e32f5cee80</td>
<td class="col2">13-1970169159-1818588270-1231384169</td>
<td class="col3">00330-80000-00000-AA369</td>
<td class="col4">DESKTOP-E5L9057</td>
<td class="col5">Thom</td>
<td class="col6">ALASKA - 1072009</td>
<td class="col7">Intel(R) Core(TM) i7-3770K CPU @ 3.50GHz</td>
<td class="col8">NVIDIA GeForce GTX 1080 Ti</td>
</tr>
<tr>
<td class="col0">test5</td>
<td class="col1">74421b33c2ececc0e9873acdc72f0f72dcbc6064b2fedea9590b28088b730e8f</td>
<td class="col2">13-1970169159-1818588270-1231384169</td>
<td class="col3">00330-80000-00000-AA682</td>
<td class="col4">DESKTOP-412N9PE</td>
<td class="col5">Skyler</td>
<td class="col6">ALASKA - 1072009</td>
<td class="col7">Intel(R) Core(TM) i5-4460 CPU @ 3.20GHz</td>
<td class="col8">AMD Radeon R9 200 Series</td>
</tr>
<tr>
<td class="col0">test6</td>
<td class="col1">9fc2b8069c6c7cf3ff0ba01e93feeab14783345485a913e69462ee40d329f454</td>
<td class="col2">6-1752462657-1145913699-1769238117</td>
<td class="col3">00326-30000-00001-AA636</td>
<td class="col4">DESKTOP-5PQDFP3</td>
<td class="col5">Aiden</td>
<td class="col6">HPQOEM - 1072009</td>
<td class="col7">AMD A6-3650 APU with Radeon(tm) HD Graphics</td>
<td class="col8">NVIDIA GeForce GTX 750 Ti</td>
</tr>
<tr>
<td class="col0">truix</td>
<td class="col1">4d73e26e4a60043efcab3f4c2f084fec7b930c80784f2e5affabdab4d68f36f1</td>
<td class="col2">13-1970169159-1818588270-1231384169</td>
<td class="col3">00331-10000-00001-AA429</td>
<td class="col4">TRUIX-DESKTOP</td>
<td class="col5">Truix</td>
<td class="col6">ALASKA - 1072009</td>
<td class="col7">Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz</td>
<td class="col8">NVIDIA GeForce GTX 1070</td>
</tr>
</tbody>
</table>
</body>
</html>and I would want to return
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>logs</title>
<meta name="GENERATOR" content="HeidiSQL 9.4.0.5125">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style type="text/css">
thead tr {background-color: ActiveCaption; color: CaptionText;}
th, td {vertical-align: top; font-family: "Tahoma", Arial, Helvetica, sans-serif; font-size: 8pt; padding: 3px; }
table, td {border: 1px solid silver;}
table {border-collapse: collapse;}
thead .col0 {width: 148px;}
thead .col1 {width: 300px;}
thead .col2 {width: 239px;}
thead .col3 {width: 173px;}
thead .col4 {width: 133px;}
thead .col5 {width: 107px;}
thead .col6 {width: 231px;}
thead .col7 {width: 268px;}
thead .col8 {width: 281px;}
</style>
</head>
<body>
<table caption="logs (4 rows)">
<thead>
<tr>
<th class="col0">user</th>
<th class="col1">gpuid</th>
<th class="col2">cpuid</th>
<th class="col3">winkey</th>
<th class="col4">desktopname</th>
<th class="col5">windowsuser</th>
<th class="col6">bios</th>
<th class="col7">processor</th>
<th class="col8">video</th>
</tr>
</thead>
<tbody>
<tr>
<td class="col0">test1</td>
<td class="col1">4d73e26e4a60043efcab3f4c2f084fec7b930c80784f2e5affabdab4d68f36f1</td>
<td class="col2">13-1970169159-1818588270-1231384169</td>
<td class="col3">00331-10000-00001-AA429</td>
<td class="col4">TRUIX-DESKTOP</td>
<td class="col5">Truix</td>
<td class="col6">ALASKA - 1072009</td>
<td class="col7">Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz</td>
<td class="col8">NVIDIA GeForce GTX 1070</td>
</tr>
<tr>
<td class="col0">test2</td>
<td class="col1">4d73e26e4a60043efcab3f4c2f084fec7b930c80784f2e5affabdab4d68f36f1</td>
<td class="col2">13-1970169159-1818588270-1231384169</td>
<td class="col3">00331-10000-00001-AA429</td>
<td class="col4">TRUIX-DESKTOP</td>
<td class="col5">Truix</td>
<td class="col6">ALASKA - 1072009</td>
<td class="col7">Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz</td>
<td class="col8">NVIDIA GeForce GTX 1070</td>
</tr>
<tr>
<td class="col0">test3</td>
<td class="col1">4d73e26e4a60043efcab3f4c2f084fec7b930c80784f2e5affabdab4d68f36f1</td>
<td class="col2">13-1970169159-1818588270-1231384169</td>
<td class="col3">00331-10000-00001-AA429</td>
<td class="col4">TRUIX-DESKTOP</td>
<td class="col5">Truix</td>
<td class="col6">ALASKA - 1072009</td>
<td class="col7">Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz</td>
<td class="col8">NVIDIA GeForce GTX 1070</td>
</tr>
<tr>
<td class="col0">truix</td>
<td class="col1">4d73e26e4a60043efcab3f4c2f084fec7b930c80784f2e5affabdab4d68f36f1</td>
<td class="col2">13-1970169159-1818588270-1231384169</td>
<td class="col3">00331-10000-00001-AA429</td>
<td class="col4">TRUIX-DESKTOP</td>
<td class="col5">Truix</td>
<td class="col6">ALASKA - 1072009</td>
<td class="col7">Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz</td>
<td class="col8">NVIDIA GeForce GTX 1070</td>
</tr>
</tbody>
</table>
</body>
</html></div>
答
If I read your question correctly, you want to find records that share the same value across more than 4 columns within the same table. Since GROUP BY and COUNT() would function as a composite key, it would only find exact matches of all of the columns grouped.
I think as mentioned by @Orangepill in the comments. It would be simplest to add up the resulting matching column values, in relation to a join on the same table to compare the differing values too, and then limit the result of the matched values using DISTINCT and HAVING.
SELECT DISTINCT
m.`user`,
m.gpuid,
m.cpuid,
m.winkey,
m.desktopname,
m.windowsuser,
m.bios,
m.processor,
m.video
FROM (
SELECT p.*, (
(p.gpuid = p2.gpuid) +
(p.cpuid = p2.cpuid) +
(p.winkey = p2.winkey) +
(p.desktopname = p2.desktopname) +
(p.windowsuser = p2.windowsuser) +
(p.bios = p2.bios) +
(p.processor = p2.processor) +
(p.video = p2.video)
) AS matches
FROM `table` AS p
LEFT JOIN `table` AS p2
ON p.`user` != p2.`user`
HAVING matches > 4
) AS m
Results http://sqlfiddle.com/#!9/180d5f/5
| user | gpuid | cpuid | winkey | desktopname | windowsuser | bios | processor | video |
|-------|------------------------------------------------------------------|-------------------------------------|-------------------------|---------------|-------------|------------------|------------------------------------------|-------------------------|
| test2 | 4d73e26e4a60043efcab3f4c2f084fec7b930c80784f2e5affabdab4d68f36f1 | 13-1970169159-1818588270-1231384169 | 00331-10000-00001-AA429 | TRUIX-DESKTOP | Truix | ALASKA - 1072009 | Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz | NVIDIA GeForce GTX 1070 |
| test3 | 4d73e26e4a60043efcab3f4c2f084fec7b930c80784f2e5affabdab4d68f36f1 | 13-1970169159-1818588270-1231384169 | 00331-10000-00001-AA429 | TRUIX-DESKTOP | Truix | ALASKA - 1072009 | Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz | NVIDIA GeForce GTX 1070 |
| truix | 4d73e26e4a60043efcab3f4c2f084fec7b930c80784f2e5affabdab4d68f36f1 | 13-1970169159-1818588270-1231384169 | 00331-10000-00001-AA429 | TRUIX-DESKTOP | Truix | ALASKA - 1072009 | Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz | NVIDIA GeForce GTX 1070 |
| test1 | 4d73e26e4a60043efcab3f4c2f084fec7b930c80784f2e5affabdab4d68f36f1 | 13-1970169159-1818588270-1231384169 | 00331-10000-00001-AA429 | TRUIX-DESKTOP | Truix | ALASKA - 1072009 | Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz | NVIDIA GeForce GTX 1070 |