dsk920417 2013-04-02 23:55
浏览 19
已采纳

这个带有2个联合的sql可以最小化到没有联合吗? [关闭]

I have a query that I use 2 UNIONS to get the results that I need

It may be done using a CASE statement but I could not figure it out. I will appreciated it if some can can solve this problem

This is my current query please keep in mind that viewable_by can be only 1, 2 or 3 However, client_id, team_id and created_by will be a variable depending on the user those will change

SELECT view_name, view_id, viewable_by  FROM `phone_call_views`
WHERE status = 1 AND ( viewable_by = 1  AND client_id = 1)
UNION
SELECT view_name, view_id, viewable_by  FROM `phone_call_views`
WHERE status = 1 AND ( viewable_by = 2  AND team_id =5 AND client_id = 1)
UNION
SELECT view_name, view_id, viewable_by  FROM `phone_call_views`
WHERE status = 1 AND ( viewable_by = 3  AND created_by= 6)

This is my actual query with the variable in php

$db->getDataSet(' SELECT view_name, view_id, viewable_by  FROM `phone_call_views`
                                     WHERE status = 1 AND ( viewable_by = 1  AND client_id = '.CLIENT_ID.')
                                     UNION
                                     SELECT view_name, view_id, viewable_by  FROM `phone_call_views`
                                     WHERE status = 1 AND ( viewable_by = 2  AND team_id ='.TEAM_ID.' AND client_id = '.CLIENT_ID.')
                                     UNION
                                     SELECT view_name, view_id, viewable_by  FROM `phone_call_views`
                                     WHERE status = 1 AND ( viewable_by = 3  AND created_by= '.USER_ID.') '
                                    );
  • 写回答

2条回答 默认 最新

  • duandangqin0559 2013-04-02 23:59
    关注

    You can combine them like this:

    SELECT view_name, view_id, viewable_by  FROM `phone_call_views`
    WHERE status = 1 AND (
       (viewable_by = 1  AND client_id = 1) or 
       (viewable_by = 2  AND team_id =5 AND client_id = 1) or 
       (viewable_by = 3  AND created_by= 6)
    )
    

    You can also use SELECT .... WHERE IN statement to combine the OR statements.

    Like

    SELECT * FROM ... WHERE viewable_by IN (1,2,3)
    

    Will translate to

    SELECT * FROM ... WHERE viewable_by='1' or viewable_by='2' or viewable_by='3'
    

    But IN clauses I believe while logically equivalent to OR's are not at all the same when it comes to query execution

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题