python – 使用SQLite进行复杂的SQL查询

我需要进行复杂的查询,我需要帮助.以下是我的具体示例：

id   |   Date   |  Validity

48   | 6-1-2009 |  notFound   
47   | 6-1-2009 |  valid   
46   | 6-1-2009 |  valid    
45   | 3-1-2009 |  invalid   
44   | 3-1-2009 |  invalid   
42   | 4-1-2009 |  notFound 
41   | 4-1-2009 |  notFound
48   | 4-1-2009 |  valid 

[这里是SQL.]

查询结果如下：

   Date   |  valid   |  invalid   |    notFound

 3-1-2009 |   0      |     2      |       0
 4-1-2009 |   1      |     2      |       2
 6-1-2009 |   3      |     2      |       3

我只能在SQLite中这样做吗？有人能帮我吗？我在Python工作.

我需要这个来生成一个折线图.例子是：line chart！

解决方法:

您的日期列无法正确排序,因为它不是以最重要的字段,即年份开头.

假设您将日期更改为具有正确的yyyy-mm-dd格式,您可以使用以下内容：

SELECT A.Date,
       (SELECT COUNT(*) FROM MyTable AS B
        WHERE B.Date <= A.Date
          AND B.Validity = 'valid') AS valid,
       (SELECT COUNT(*) FROM MyTable AS B
        WHERE B.Date <= A.Date
          AND B.Validity = 'invalid') AS invalid,
       (SELECT COUNT(*) FROM MyTable AS B
        WHERE B.Date <= A.Date
          AND B.Validity = 'notFound') AS notFound
FROM (SELECT DISTINCT Date FROM MyTable
      ORDER BY Date) AS A

【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！