SQL 中是否有可选的预处理语句用于不同的特定查询级别

本文介绍了SQL 中是否有可选的预处理语句用于不同的特定查询级别的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

假设我有一个查询，返回我的数据库中单个产品的每月总销售额:

Suppose I have a query that returns the total monthly sales of individual products in my database:

product = input("Enter product column to search")
milkOptions = input("Enter milkOptions column to search")
size = input("Enter size to search")

import sqlite3

conn=sqlite3.connect("system.db")
cur=conn.cursor()
sql ="""
select sum(quantity*price), strftime("%m-%Y", orderDate) 
as month 
from customerOrders
WHERE product = ? and milkOptions = ? and size = ?
group by orderDate"""

有没有一种方法可以在不创建许多类似查询的情况下查询各种级别的特异性.例如，如果我输入 Espresso 作为产品，省略 Milkoptions 条目，并输入 small 作为 size，我是否可以退回所有 Small Espresso 销售额.反之亦然，用于省略和输入其他选项.

Is there a way to query various levels of specificity without creating many similar queries. For instance, If i enter Espresso for the product, leave out the milkoptions entry, and enter small for size, could I return all Small Espresso sales. Vice versa for leaving out and entering other options.

这是我的数据库:

+---------+-----------+--------+-------------+------------+----------+-------+------------+
| orderid |  product  |  size  | milkOptions | orderDate  | quantity | price | customerid |
+---------+-----------+--------+-------------+------------+----------+-------+------------+
|       1 | Espresso  | Small  | Soya        | 2019-10-29 |        1 | 1.0   |        1   |
|       2 | Cappucino | Small  | SemiSkimmed | 2019-10-29 |        1 | 1.0   |        1   |
|       3 | Cappucino | Small  | SemiSkimmed | 2019-10-29 |        1 | 1.0   |        1   |
|       4 | Cappucino | Medium | SemiSkimmed | 2019-10-29 |        1 | 1.0   |        1   |
+---------+-----------+--------+-------------+------------+----------+-------+------------+

推荐答案

为每一列传递一个额外的标志参数 0 或 1，例如:

Pass an additional flag parameter 0 or 1 for each of the columns, like:

WHERE 
  (product = ? OR 0 = ?)  
  AND 
  (milkOptions = ? OR 0 = ?) 
  AND 
  (size = ? OR 0 = ?)

当您为 product 的标志参数传递 0 时(并且 anything 对于列 product 不为 null>) 条件将评估为 TRUE，这相当于没有为 product 列设置条件.
当您为 product 的标志参数传递 1 和为 product 列传递 'someproduct' 时那么条件等价于 product = 'someproduct'.

When you pass 0 for the product's flag parameter (and anything not null for the column product) the condition will evaluate to TRUE which is equivalent to not setting a condition for the column product.
When you pass 1 for the product's flag parameter and 'someproduct' for the column product then the condition is equivalent to product = 'someproduct'.

这篇关于SQL 中是否有可选的预处理语句用于不同的特定查询级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Product