按日期计算SqlClient数

Question

我无法向谷歌阐明我的问题，以找出如何做到这一点，我希望有人能帮助我。

这是我的出发点。

SqlCommand command = new SqlCommand("SELECT BOOK_ID, BOOK_DATE, Sum(BOOK_COUNT) " +
                                    "FROM BOOKS " +
                                    "WHERE (BOOK_ID = '1' OR " +
                                           "BOOK_ID = '2' OR " +
                                           "BOOK_ID = '3' )  " +
                                    "AND STATUS  = '0'    " +
                                    "GROUP BY BOOK_ID, BOOK_DATE ", cn);

SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
    int id = (int)reader[0]; 
    DateTime date = (DateTime)reader[1]; 
    int count = (int)reader[2];

    if (id == 1)
    {
       /*...*/
    }

    if (id == 2)
    {
       /*...*/
    }

    if (id == 3)
    {
       /*...*/
    }
 }

 reader.Close();

DBSCA

​

​

我想按日期分类，如果日期和id是相同的，那就数一数书.如下所示：

BOOK_DATE  BOOK_ID  BOOK_COUNT  
2014.11.17     1        1         
2014.11.21     2        1           
2014.11.25     2        2           
2014.11.26     1        2           
2014.11.26     3        2           
2014.11.27     3        1           
2014.11.28     3        1           

如果有人能帮我的话，谢谢，我为糟糕的英语道歉！

Answer 1

Group by日期时间字段的日期部分：

SqlCommand command = new SqlCommand("SELECT BOOK_ID, DATEADD(DAY, 0, DATEDIFF(DAY, 0, BOOK_DATE)), Sum(BOOK_COUNT) " +
                                    "FROM BOOKS " +
                                    "WHERE (BOOK_ID = '1' OR " +
                                           "BOOK_ID = '2' OR " +
                                           "BOOK_ID = '3' )  " +
                                    "AND STATUS  = '0'    " +
                                    "GROUP BY BOOK_ID, DATEADD(DAY, 0, DATEDIFF(DAY, 0, BOOK_DATE)) 
ORDER BY 2", cn);

Answer 2

ORDER BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, BOOK_DATE)), Sum(BOOK_COUNT)

DATEADD(dd，0，DATEDIFF(DAY，0，BOOK_DATE))只获取记录的日期分段。您还需要使用此函数进行分组。

感谢@marc_s的建议

Answer 3

告诉我这个。Convert BOOK_DATE to Date，然后在Group by中使用

SELECT BOOK_ID,
       CONVERT(DATE, BOOK_DATE),
       Sum(BOOK_COUNT)
FROM   BOOKS
WHERE   BOOK_ID IN (1,2,3)
       AND STATUS = 0
GROUP  BY BOOK_ID,
          CONVERT(DATE, BOOK_DATE)