SQL查询（在MySql中）(SQL query (in MySql))

大家好，尤其是SQL GURU，

我有一个表可能有很多记录（超过100万），其中包含简单数据以及保存注册时刻的时间戳（将CURRENT_TIMESTAMP定义为该字段的默认值）。

我想实现一个只扫描表一次的简单查询，并计算注册的记录数，比如最近24小时，最后7天和最后一个月。

尝试这个没有结果（虽然我应该得到几个1肯定的全部为零）：

SET @1 = CURRENT_TIMESTAMP() ; SET @2 = @1 - INTERVAL 24 HOUR ; SET @3 = @1 - INTERVAL 7 DAY ; SET @4 = @1 - INTERVAL 1 MONTH; select Registration_Timestamp , (case Registration_Timestamp when Registration_Timestamp> @2 THEN 1 else 0 end) as LAST_DAY , (case Registration_Timestamp when Registration_Timestamp> @3 THEN 1 else 0 end) as LAST_WEEK , (case Registration_Timestamp when Registration_Timestamp> @4 THEN 1 else 0 end) as LAST_MONTH from tbl_Dummy where Registration_Timestamp >= @4 ;

这当然不会给出总和，但是一旦我得到这个以显示1秒，我将用select count(LAST_DAY),count(LAST_WEEK),count(LAST_MONTH)来包装它，当然这将完成工作。

我的错误在哪里？

在此先感谢您的帮助。

Hello to all and specially SQL GURUs,

I have a table with potentially very many records (above 1 million) that contains simple data along with a timestamp that holds the moment of registration (defining CURRENT_TIMESTAMP as the default value for that field).

I want to implement a simple query that scans the table only once and counts the number of records that were register, say, the last 24 hours, the last 7 days and the last month.

Was trying this with no results (getting all zeros though I should get few 1's for sure):

SET @1 = CURRENT_TIMESTAMP() ; SET @2 = @1 - INTERVAL 24 HOUR ; SET @3 = @1 - INTERVAL 7 DAY ; SET @4 = @1 - INTERVAL 1 MONTH; select Registration_Timestamp , (case Registration_Timestamp when Registration_Timestamp> @2 THEN 1 else 0 end) as LAST_DAY , (case Registration_Timestamp when Registration_Timestamp> @3 THEN 1 else 0 end) as LAST_WEEK , (case Registration_Timestamp when Registration_Timestamp> @4 THEN 1 else 0 end) as LAST_MONTH from tbl_Dummy where Registration_Timestamp >= @4 ;

This is not going to give the sum of course, but once I get this to show 1s I will wrap it with select count(LAST_DAY),count(LAST_WEEK),count(LAST_MONTH) which will do the job of course.

Where is my error here?

Thanks in advance for any help.

最满意答案

您的CASE陈述略有出现：

CASE *expr1* WHEN *expr2* THEN *expr3* ELSE 0 END

将expr1与expr2比较，并在expr1 = expr2时返回expr3

您的第一个CASE实际上将Registration_Timestamp （时间戳）与Registration_Timestamp> @2的结果（1表示true，0表示false）进行比较，这不是您想要的。

你想要这种形式的CASE ：

CASE WHEN *expr1* THEN *expr2* ELSE 0 END

当expr1为true时，返回expr2

在你的第一个CASE ：

CASE WHEN Registration_Timestamp > @2 THEN 1 ELSE 0 END

另外

MySQL实际上已经为条件语件返回1和0。它不是非常便携，但您可以使用：

SELECT Registration_Timestamp > @2 AS LAST_DAY

注意

对于每个NOT NULL结果， COUNT(*expr*)将计数1，这将计算1和0相同，因此这里需要SUM() 。

或者您可以将CASE更改为：

CASE WHEN Registration_Timestamp > @2 THEN 1 END

当条件不满足时，它将返回NULL 。

Your CASE statements are slightly out:

CASE *expr1* WHEN *expr2* THEN *expr3* ELSE 0 END

Compares expr1 to expr2 and returns expr3 when expr1 = expr2

Your first CASE actually compares Registration_Timestamp (a timestamp) to the result of Registration_Timestamp> @2 (1 for true, 0 for false) which isn't what you want.

You want this form of CASE:

CASE WHEN *expr1* THEN *expr2* ELSE 0 END

Which returns expr2 when expr1 is true

In your first CASE:

CASE WHEN Registration_Timestamp > @2 THEN 1 ELSE 0 END

Alternatively

MySQL actually returns 1 and 0 already for conditionals. It's not hugely portable but you could use:

SELECT Registration_Timestamp > @2 AS LAST_DAY

Note

COUNT(*expr*) will count 1 for every NOT NULL result, which will count 1s and 0s the same, so you'd need SUM() here.

Or you could change your CASEs to:

CASE WHEN Registration_Timestamp > @2 THEN 1 END