SQL statistics by time, how SQL queries query the total number of times in that period by hour

Of course; Just use the row to the column.

Static writing. select unit,sum(case when year(time)=2005 and datepart(q,time)=1 then value else 0 end) as 2005 a quarter,sum(case when year(time)=2005 and datepart(q,time)=2 then value else 0 end) as 2005 a quarter ,..

from table

group by unit.

When displayed vertically;

select unit, case when year(time)=2005 and datepart(q,time)=1 then'First quarter of 2005'

when when year = 2005 and datepart(q, time) = 2

then 'Second quarter of 2005'

.end as shows the season, and sum(value) as shows the value.

from table

group by unit,case when year(time)=2005 and datepart(q,time)=1 then'First quarter of 2005'

when when year = 2005 and datepart(q, time) = 2

then 'Second quarter of 2005'

.end

select unit, year+'Section'+datepart(q, time)+'Quarter'Quarter, sum(value) total.

from the table name.

group by unit, year(time)+'Section'+datepart(q, time)+'Quarter'

order by 1,2

1. Create a Junit test.

2. Use the setup() and teardown() methods.

3. Generate a session in two methods respectively, open a transaction, commit a transaction and close a session First test the local SQL query, that is, the commonly used SQL statement executes SQL through the createsqlquery method of the session, and loads the object to be queried.

4. The method of conditional query, Hibernate uses the method of :stuname to occupy the parameters in the SQL and passes"The name of the parameter","parameters") to assign a value.

5. The corresponding query result.

This can be done with the group by statement.

For example, the test table contains the following data:

To find out the sum of CNTs for each hour of the day on February 25, you can use the following statement:

with t as

select number rn from master..spt_values where type='p' and number<=23)

select hours,sum(isnull(cnt,0)) quantity from t left join test on ,begin date,12,2)

as int) group by

Query Result:

Use the job scheduled task execution statement to define the execution interval to 1 hour, and the query condition is that the year, month, and day are equal to the system time at that time.

The hour of the field is equal to the current hour hour(field)=hour(getdate()).