基于一个查询中的三个可能条件的SQL SELECT

Question

我是SQL新手。目前正在进行课程的第二个挑战“用Python和Javascript进行cs50web编程”。我的任务是建立一个书评网站，在搜索页面上，用户可以通过标题、作者或isbn进行搜索。我现在已经设置了这个SQL，但是它看起来有点难看。我的问题是，有没有更优雅的方法来达到同样的效果？理想的情况是在单个SQL查询中

    page = request.form.get("page")
    searchText = request.form.get("searchText")
    bookAttr = request.form.get("bookAttr")
    likesearchText = "%" + searchText + "%"

    # Search results fixed to 10 per page

    if bookAttr == "isbn":
        rows = db.execute("SELECT isbn, title, author, year FROM books WHERE isbn LIKE :isbn LIMIT :start OFFSET :off",
                          {"isbn": likesearchText, "start": 10, "off": int(page) * 10}).fetchall()
    elif bookAttr == "title":
        rows = db.execute("SELECT isbn, title, author, year FROM books WHERE title LIKE :title LIMIT :start OFFSET :off",
                          {"title": likesearchText, "start": 10, "off": int(page) * 10}).fetchall()
    else:
        rows = db.execute("SELECT isbn, title, author, year FROM books WHERE author LIKE :author LIMIT :start OFFSET :off",
                          {"author": likesearchText, "start": 10, "off": int(page) * 10}).fetchall()

Answer 1

你可以这样做：

SELECT isbn, title, author, year
FROM books
WHERE isbn LIKE :isbn OR title LIKE :title OR author LIKE :author;

如果其中两个参数为NULL或空字符串，则该方法可以正常工作

Answer 2

这实际上不是SQL解决方案。。。但是您是否考虑过动态设置value列？类似这样的（未测试）：

page = request.form.get('page')
searchText = request.form.get('searchText')
bookAttr = request.form.get('bookAttr')
# Avoid SQL injection vulnerability by checking the parameter value
if bookAttr != 'isbn' and bookAttr != 'title' and bookAttr != 'author':
    raise ValueError('Unsupported bookAttr: ' + bookAttr)
rows = db.execute('SELECT isbn, title, author, year FROM books WHERE '
                  + bookAttr
                  + ' LIKE :searchText LIMIT :start OFFSET :off',
                  {'searchText': '%' + searchText + '%', 'start': 10,
                  'off': int(page) * 10}).fetchall()