mysql—获取此查询中数据计数的更好方法？

polhcujo 于 2021-06-24 发布在 Mysql

关注(0)|答案(3)|浏览(263)

这个问题在这里已经有答案了：

多个查询同一个表但在不同的列中mysql（4个答案）
两年前关门了。
我有一个藏书室( books )还有一张table，每次有人借书或带书回来，都会被记录下来( book_loans ).
基本上，我想看看在某个时间 checkout 的所有书籍，有多少：
属于“幻想”类型
有500多页
是精装书吗
我想一次完成这一切，我现在使用4个查询，有更好的方法吗？
附言： book_loans .bookid是的外键 books .id:

SELECT * FROM
(

    SELECT COUNT(*) FROM `books` AS bks
    INNER JOIN `book_loans` AS bs ON bks.id = bs.bookID
    WHERE bs.time = '2017-07-01 02:16:00' AND bs.signedout = 1 AND bks.genre = 'fantasy'

    UNION

    SELECT COUNT(*) FROM `books` AS bks
    INNER JOIN `book_loans` AS bs ON bks.id = bs.bookID
    WHERE bs.time = '2017-07-01 02:16:00' AND bs.signedout = 1 AND bks.pages > 500

    UNION

    SELECT COUNT(*) FROM `books` AS bks
    INNER JOIN `book_loans` AS bs ON bks.id = bs.bookID
    WHERE bs.time = '2017-07-01 02:16:00' AND bs.signedout = 1 AND bks.hardcover = 1

) x;

为此总共执行4个查询似乎效率很低。

sql mysql union select foreign-keys

来源：https://stackoverflow.com/questions/51216453/better-way-to-get-counts-of-data-in-this-query

3条答案

按热度按时间

iqxoj9l91#

SELECT SUM(bks.genre = 'fantasy') as fantasy,
       SUM(bks.pages > 500) as pages,
       SUM(bks.hardcover = 1) as hardcover
FROM `books` AS bks
INNER JOIN `book_loans` AS bs ON bks.id = bs.bookID
WHERE bs.time = '2017-07-01 02:16:00' AND bs.signedout = 1

赞(0）回复(0）举报 2021-06-24

kyxcudwk2#

SELECT CASE WHEN bks.genre = 'fantasy' THEN 'genre match' 
            WHEN bks.pages > 500 THEN 'page count met' 
            WHEN bks.hardcover = 1 THEN 'is hardcover'
       END AS condition
      , COUNT(*) 
FROM `books` AS bks
INNER JOIN `book_loans` AS bs ON bks.id = bs.bookID
WHERE bs.time = '2017-07-01 02:16:00' AND bs.signedout = 1 
  AND (bks.genre = 'fantasy' OR bks.pages > 500 OR bks.hardcover = 1)
GROUP BY condition;

这是具有类似结果的juergen d的替代方法；但我会和他的第一个一起去。与他不同的是，在这本书中，匹配多个条件的书只会包含在第一个条件的计数中。
但是，如果book\u loans表非常大，您仍然可以考虑在where子句中包含or条件。

赞(0）回复(0）举报 2021-06-24

fquxozlt3#

这应该做你需要的，如果你只是想总计数，其中所有3个是，如果你需要3个不同的总数，一个为每个或你需要一些不同的东西。

SELECT COUNT(*) FROM `books` AS bks
INNER JOIN `book_loans` AS bs ON bks.id = bs.bookID
WHERE bs.time = '2017-07-01 02:16:00' AND bs.signedout = 1 
AND (
    bks.genre = 'fantasy'
    OR bks.pages > 500
    OR bks.hardcover = 1
)

赞(0）回复(0）举报 2021-06-24

我来回答

mysql—获取此查询中数据计数的更好方法？

3条答案

相关问题

热门标签

最新问答