如何使用配置单元在where语句中分组或子句

wvmv3b1j  于 2021-05-29  发布在  Hadoop
关注(0)|答案(3)|浏览(293)

我想问一下

(statement1 AND statement2 AND (statement3 OR statement4 ))

这是我的配置单元查询,我验证了它不工作,因为它只返回statement3,并且我知道statement4是真的

SELECT 
  cid,
  SUM(count) AS total_count
FROM
  count_by_day
WHERE
  time >= 1435536000
  AND time < 1436140800
  AND(
    cid = '4eb3441f282d4d657a000016'
    OR cid = '14ebe153121a863462300043d'
  )
GROUP BY
  cid

有人能告诉我怎么了吗?谢谢

vsnjm48y

vsnjm48y1#

总是学会在关系数据库中使用union而不是or。试试看工会能否解决你的问题。

select cols
from table
where statement1 AND statement2 AND statement3

union all

select cols
from table
where statement1 AND statement2 AND statement4
ergxz8rk

ergxz8rk2#

count是真正的变量名吗?再检查一遍。
还要检查时间是否是数字类型,可能是bigint。如果不是像这样把它转换成一个大整数:

WHERE cast(time as bigint) >= 1435536000 AND cast(time as bigint) < 1436140800

尝试将or语句更改为in语句。

SELECT 
 cid,
 SUM(count) AS total_count
FROM
count_by_day
WHERE time >= 1435536000 AND time < 1436140800
AND cid in('4eb3441f282d4d657a000016','14ebe153121a863462300043d')
GROUP BY
cid;

每次尝试一个更改,这样您就知道修复方法是什么。

puruo6ea

puruo6ea3#

查询1:

Select A,B,C
From 
                Table1 t1
                Join 
                Table2 t2
                On 
                                T1.field1=T2.field1 OR 
                                T1.field2 = T2.field2

查询2:

Select A,B,C
From 
                Table1 t1
                Join 
                Table2 t2
                On 
                                T1.field1=T2.field1 

Union 

Select A,B,C
From 
                Table1 t1
                Join 
                Table2 t2
                On 
                                T1.field2 = T2.field2

相关问题