postgresql - 带有逻辑运算符“AND”“OR”“NOT”的 PSQL Where 子句

Question

这是我在购物篮分析查询中的问题，客户可以选择他的购物篮并调用这样的搜索查询

select ti.* from transaction t join transaction_items ti on ti.unique_id = t.unique_id where ti.display_description  'ROMA TOMATO' AND 'BANANA' AND ('JALAPENO' OR 'ONION WHITE' AND ('SALSA' OR 'GUACAMOLE')),
        'VITAMINS' AND 'PRODUCE' OR ('POTATO' AND 'SHRIMP') AND NOT ('MEAT' OR 'FISH' OR 'PIE')

在上面的查询中，搜索了两个不同的购物篮。通过“，”我们可以将篮子分开。因此，我们必须按篮子提供数据组。'ROMA TOMATO' AND 'BANANA' AND ('JALAPENO' OR 'ONION WHITE' AND ('SALSA' OR 'GUACAMOLE')) 篮子一和篮子二的销售额'VITAMINS' AND 'PRODUCE' OR ('POTATO' AND 'SHRIMP') AND NOT 'MEAT' OR 'FISH' OR 'PIE'

transaction_item 表的样本数据

amt     qty  display_description     unique_id 
2.470   1   ROMA TOMATO "2704-7075-12-201111"
2.470   1   BANANA  "2704-7075-12-201111"
2.190   1   JALAPENO    "2704-7075-12-201111"
2.190   1   ONION   "2704-7075-12-201111"
2.190   1   SALSA   "2704-7075-12-201111"
0.880   1   GUACAMOLE   "2704-7075-12-201111"
2.860   4840    VITAMINS    "2704-7075-12-201111"
1.580   2   PRODUCE "2704-7075-12-201111"
3.560   1550    POTATO  "2704-7075-12-201111"
0.990   1   SHRIMP  "2704-7075-12-201111"
0.340   1   MEAT    "2704-7075-12-201111"
0.340   1   MEAT    "2704-7075-12-201111"
0.330   1   FISH    "2704-7075-12-201111"
0.330   1   FISH    "2704-7075-12-201111"
0.330   1   PIE "2704-7075-12-201111"
0.330   1   ABC "2704-7075-12-201111"
0.330   1   ABC "2704-7075-12-201111"
0.330   1   ABC "2704-7075-12-201111"
0.330   1   XYZ "2704-7075-12-201111"
0.990   1   XYZ "2704-7075-12-201111"
0.500   1   XYZ "2704-7075-12-201111"
0.500   1   XYZ "2704-7075-12-201111"
2.470   1   ROMA TOMATO "2704-7075-12-201112"
2.470   1   BANANA  "2704-7075-12-201112"
2.190   1   JALAPENO    "2704-7075-12-201112"
2.190   1   ONION   "2704-7075-12-201112"
2.190   1   SALSA   "2704-7075-12-201112"
0.880   1   GUACAMOLE   "2704-7075-12-201112"
2.860   4840    VITAMINS    "2704-7075-12-201112"
1.580   2   PRODUCE "2704-7075-12-201112"
3.560   1550    POTATO  "2704-7075-12-201112"
0.990   1   SHRIMP  "2704-7075-12-201112"
0.330   1   ABC "2704-7075-12-201112"
0.330   1   ABC "2704-7075-12-201112"
0.330   1   ABC "2704-7075-12-201112"
0.330   1   XYZ "2704-7075-12-201112"
0.990   1   XYZ "2704-7075-12-201112"
0.500   1   XYZ "2704-7075-12-201112"
0.500   1   XYZ "2704-7075-12-201112"

标签： postgresqlanalyticslogical-operators