选择具有超过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>

写回答
好问题 0 提建议
追加酬金
关注问题
分享
邀请回答
编辑收藏删除结题
收藏举报

1条回答默认最新

dpqmu84646 2017-11-21 06:19

关注

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 |

本回答被题主选为最佳回答 , 对您是否有帮助呢?

报告相同问题？

关注问题

选择具有超过4列匹配值的玩家 mysql php sql
2017-11-21 03:57

回答 1 已采纳 If I read your question correctly, you want to find records that share the same value across more
SQL选择具有匹配值的行 php sql
2012-10-22 16:40

回答 3 已采纳 You need to use a self-join: SELECT a.master_record_id FROM tablename a JOIN tablename b USING (m
从具有匹配值php的多维数组中获取特定数组 php
2018-08-03 11:17

回答 4 已采纳 One method is to create a flat array of the userid and use array_intersect to get the matching ful
何谓大数据？
2021-05-29 00:49

hzbooks的博客 1大数据简史dashuj有很多方法可以描述历史。在描述计算机科学历史的一部分时，一种方法是用技术来描述它，另一种方法是从组织的角度来描述它。我们将从市场营销的角度来描述大数据的简要历史。0...
如何获取与MySQL数据库的多列值匹配的值 mysql php
2018-03-16 11:15

回答 5 已采纳 One option uses aggregation to check the status values: SELECT book_id FROM db_user GROUP BY book
获取匹配列及其值MySQL mysql php
2014-06-23 16:20

回答 2 已采纳 Consider the following... DROP TABLE IF EXISTS my_table; CREATE TABLE my_table (id INT NOT NULL
pandas怎么在表格实现列匹配的功能 python 有问必答
2021-10-20 22:44

回答 2 已采纳 def handle(series: pd.Series): if df2.get('student_name').get(series.name): series['sig
大数据实训整体解决方案
2020-03-18 10:16

筱程技术的博客第一章 大数据发展背景 1.1 国家政策  2017年1月工业和信息化部正式发布了《大数据产业发展规划（2016－2020年）》，明确了“十三五”时期大数据产业的发展思路、原则和目标，将引导大数据产业持续健康发展，有力...
antd的日期选择器value值无法设置前端
2022-08-01 16:10

回答 1 已采纳 https://ant.design/components/date-picker-cn/#components-date-picker-demo-format 看看这个例子试试 const
java string数组值的匹配 java
2023-01-09 17:45

回答 2 已采纳 string类型比较是否相等要用它的equal方法，而不是==
golang csv如果匹配列值则删除重复项
2016-08-22 19:04

回答 3 已采纳 to add to @Kul's answer: // i'm pretty sure struct{} is smaller in memory than bool: known := mak
云计算与大数据技术应用第四章
2020-12-22 19:28

m0_53544880的博客世界数据中心（World Data Center）是国际科学联合会下设的科学数据组织，有40多个学科数据中心，分属四个数据中心群：WDC-A 美国、WDC-B 前苏联、WDC-C 欧洲和日本、WDC-D 中国。 1988年，中国加入WDC，并建立...
mysql - 列数与行的值计数不匹配 mysql php sql
2015-03-11 09:06

回答 3 已采纳 You're entering too few, or too many values. Count the number of columns in your table, and check
高校大数据专业教学实训资源解决方案
2020-03-12 10:30

筱程技术的博客高校大数据专业教学实训资源解决方案第一章 大数据发展背景 1.1 国家政策  2017年1月工业和信息化部正式发布了《大数据产业发展规划（2016－2020年）》，明确了“十三五”时期大数据产业的发展思路、原则和...
腾讯游戏许振文：王者荣耀实时大数据平台黑科技解密
2020-11-12 08:09

中生代技术的博客 ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍许振文腾讯游戏增值服务部后台开发组组长读完需要20分钟速读仅需 5 分钟从事游戏大数据相关领域 8 年多，负责游戏数据分析平台 ...
没有解决我的问题, 去提问

悬赏问题

¥15 安卓adb backup备份应用数据失败
¥15 eclipse运行项目时遇到的问题
¥15 关于#c##的问题：最近需要用CAT工具Trados进行一些开发
¥15 南大pa1 小游戏没有界面，并且报了如下错误，尝试过换显卡驱动，但是好像不行
¥15 没有证书，nginx怎么反向代理到只能接受https的公网网站
¥50 成都蓉城足球俱乐部小程序抢票
¥15 yolov7训练自己的数据集
¥15 esp8266与51单片机连接问题(标签-单片机|关键词-串口)（相关搜索：51单片机|单片机|测试代码）
¥15 电力市场出清matlab yalmip kkt 双层优化问题
¥30 ros小车路径规划实现不了，如何解决？(操作系统-ubuntu)

码龄粉丝数原力等级 --

选择具有超过4列匹配值的玩家

1条回答默认最新

码龄粉丝数原力等级 --

悬赏问题

选择具有超过4列匹配值的玩家

1条回答 默认 最新

悬赏问题

1条回答默认最新