douguan3470 2015-04-13 06:30
浏览 74
已采纳

在Mysql Query中添加Where子句?

I have this mysql query which in fact someone on Stackoverflow gave me. (It selects the two most frequent id's found together in the columns pic1,pic2 and pic3). It works great but now I want to add a WHERE clause to the query so that only rows in my table where a particular ID is present are selected. ie. WHERE pic1 =$loggedin_user OR pic2 = $loggedin_user OR pic3= $loggedin_user Can anyone tell me where the clause would go within the query below?

The Query

SELECT LEAST(p1, p2) AS p1, GREATEST(p1,p2) AS p2
FROM (
  SELECT pic1 AS p1, pic2 AS p2
  FROM mytable WHERE pic1 IS NOT NULL AND pic2 IS NOT NULL
  UNION ALL
  SELECT pic1 AS p1, pic3 AS p2
  FROM mytable WHERE pic1 IS NOT NULL AND pic3 IS NOT NULL
  UNION ALL
  SELECT pic2 AS p1, pic3 AS p2
  FROM mytable WHERE pic3 IS NOT NULL AND pic2 IS NOT NULL
) s
GROUP BY LEAST(p1, p2), GREATEST(p1,p2)
ORDER BY COUNT(*) DESC
LIMIT 1
  • 写回答

1条回答 默认 最新

  • dongxi8993 2015-04-13 07:52
    关注

    (Without checking the query in detail), it would go here:

    SELECT LEAST(p1, p2) AS p1, GREATEST(p1,p2) AS p2
    FROM (
      SELECT pic1 AS p1, pic2 AS p2
      FROM mytable WHERE pic1 IS NOT NULL AND pic2 IS NOT NULL
      UNION ALL
      SELECT pic1 AS p1, pic3 AS p2
      FROM mytable WHERE pic1 IS NOT NULL AND pic3 IS NOT NULL
      UNION ALL
      SELECT pic2 AS p1, pic3 AS p2
      FROM mytable WHERE pic3 IS NOT NULL AND pic2 IS NOT NULL
    ) s
    WHERE pic1 =$loggedin_user OR pic2 = $loggedin_user OR pic3= $loggedin_user
    GROUP BY LEAST(p1, p2), GREATEST(p1,p2)
    ORDER BY COUNT(*) DESC
    LIMIT 1
    

    This is the SELECT definition from the MySQL documentation

    SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 如何通过交互分析得出某高危患者对放疗获益更多
  • ¥15 相关性分析中,p<0.05, r=0.29,怎么评价相关性呢
  • ¥15 docker部署Mongodb后输入命令报错?
  • ¥15 将下列流程图转变成python程序代码
  • ¥15 我需要全国每个城市的最新小区名字等数据。
  • ¥15 开发一个小区生态的小程序
  • ¥15 如何解决Excel中dependent dropdown list 的问题
  • ¥15 MddBootstrapInitialize2失败
  • ¥15 LCD Flicker
  • ¥15 esp32在micropython环境下使用ssl/tls连接mqtt服务器出现以下报错Connected on 192.168.154.223发生意外错误: 5无法连接到 MQTT 代理,如何解决?