SQL SERVER

CASE WHEN

  • Case column name when conditional value 1 then option 1… Else The default value is end
  • Case when select 1 from case when select 1 from case when select 1 from case when select 1 from case when select 1 from case Else The default value is end

CSDN CASE

COUNT

The COUNT() function returns the number of rows matching the specified condition. W33SCHOOL COUNT

CAST

The argument to the CAST() function is an expression containing the source value and target data type separated by the AS keyword. SQL CAST

DATE

DATEDIFF

The function returns the time between two dates. DATEDIFF (datepart, startdate, enddate) DATEDIFF (MONTH, startdate, GETDATE ()) = 0: return to time for the MONTH

GETDATE()

The GETDATE() function returns the current time and date from SQL Server.

DATEADD

The DATEADD() function adds or subtracts a specified interval from a date. DATEADD(datePart,number,date) number is the number of intervals that you want to add

DATEPART

The DATEPART() function returns a separate part of the date/time, such as year, month, day, hour, minute, etc. DATEPART(YYYY,date) returns the year part of date

The date format
years yy, yyyy
Day of the year dy, y
quarter qq, q
month mm, m
weeks wk, ww
day dd, d
week dw, w
hours hh
minutes mi, n
seconds ss, s

GROUP BY

Note: For each item queried, group by adds an item

Sum

SUM( ) AS Name; SUM does not count as a query entry;

JOIN

JOIN/ INNER JOIN:

The INNER JOIN keyword returns rows if there is at least one match in the table.

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name
Copy the code

RIGHT/ LEFT OUTER JOIN:

The keyword returns all rows from the right/left table (table_name1), even if there are no matching rows in the left/right table (table_name2).

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name
Copy the code

FULL JOIN:

The FULL JOIN keyword returns rows whenever one of the tables has a match.

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2 
ON table_name1.column_name=table_name2.column_name
Copy the code

Tips

  1. Nicknames have the scope of use, you can directly change the column name

SQL View

1. The SQL statement in table 1 is pulled to table 2. 2