Web development: Advanced syntax in SQL Server databases

1, Date related

1. Filter out the year

SELECT YEAR(CalendarDate)  FROM TABLE1

2. Add one day to the value of the StartTime column and return the result

[Commentary].

Before modification, November 30, 2023 11:04:15.040
Revised 2023-12-01 11:04:15.040.

SELECT CAST(DATEADD(DAY, 1, StartTime) AS DATETIME) FROM TABLE

[Note] In SQL, do not input hours, minutes, and seconds. The default time, minutes, and seconds is 00:00:00.

3. Between statement

SELECT * FROM table WHERE Time BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59';

2, Data type related

1. Convert AGE to a decimal number with precision of 10 and scale of 2

SELECT cast(AGE as decimal(10,2)) FROM TABLE1

2. Convert StartTime to date data type

SELECT  CONVERT(date,StartTime) FROM TABLE1

3. Convert the value of the StartTime column to string format and return it in the form of yyyy mm dd

SELECT  CONVERT(varchar(10),StartTime, 23) FROM TABLE1

3, Condition judgment related

If AGE is empty, return 0

SELECT  ISNULL(AGE ,0)  FROM TABLE1

If sex is' 1', Then return to' Male; If sex is' 2', Then return to' Female; Otherwise, return' Other

SELECT(CASE sex
    WHEN '1' THEN 'male'
    WHEN '2' THEN 'female'
    ELSE 'other'
    END
             )  FROM TABLE1

3. If AGE equals 13, return the value of AGE; If the class is equal to 2, return the value of the class; Otherwise, return the string' Other

SELECT(CASE 
    WHEN AGE=13 THEN AGE
    WHEN CLASS=2 THEN CLASS
    ELSE 'other'
    END
             )  FROM TABLE1

4. Multiple methods for sub queries

[Scenario Description] .

Student Table
Student ID, Student Name, Student Class (stu_id stuname stu_class)
Score table
Score ID student ID passed (score_id stu_id ispass).

Requirement: Return the student name and passing field, with the filtered data requiring ispass; one .

1. Use the linked table method to query and implement. (Recommended).

SELECT s.stu_name, sc.ispass
FROM Student s
INNER JOIN Score sc ON s.stu_id = sc.stu_id
WHERE sc.ispass = 1;

2. Implement using the in syntax. (Recommended).

SELECT stu_name, '1' AS ispass
FROM Student
WHERE stu_id IN (
    SELECT stu_id
    FROM Score
    WHERE ispass = 1
);

3. Implement using nested methods. (Not recommended: complex and inefficient).

WITH TEMP AS(
SELECT stu_name, (
SELECT ispass
FROM Score
WHERE Score.stu_id = Student.stu_id
) AS 'ispass'
FROM Student
)
SELECT * FROM TEMP WHERE ispass=1

4. Implement using the existing syntax. (Not recommended).

SELECT stu_name, '1' AS ispass
FROM Student s
WHERE EXISTS (
    SELECT 1
    FROM Score sc
    WHERE sc.stu_id = s.stu_id AND sc.ispass = 1
);

4, Temporary Table

(1) Defined a public expression called CTE, which can be directly manipulated in the future

WITH CTE AS (
    SELECT * FROM TABLE1 
)
SELECT * FROM CTE;

(2) Multiple temporary tables

WITH TEMP1 AS(
	--SQL1
),TEMP2 AS(
	--SQL2
)
SELECT * FROM TEMP2

5, Query data processing

1. STUFF concatenation result, concatenate the column of the queried Name with commas

[Annotation] STUFF (original string, index of replaced element, length of replacement, symbol of replacement)
The following example shows that the original string is in the Name column, and the first element needs to be replaced with an empty symbol (i.e. deleted).

SELECT STUFF((SELECT  ',' +Name
          FROM TABLE1
          where ID='001'
          FOR XML PATH('')), 1, 1, '') AS Name

2. Advanced usage of aggregate functions

[Incorrect Writing] The Group field and Select field are inconsistent (I want to display Score).

SELECT Name, Age, Score
FROM PERSON
GROUP BY Name, Age;

[Method 1] Take the MAX value .

SELECT Name, Age, MAX(SCORE) AS Score
FROM PERSON
GROUP BY Name, Age;

[Method 2] Temporary Table Query .

WITH TEMP AS(
    SELECT Name,Age,(SELECT SCORE FROM SCORETABLE WHERE NAME=NAME) AS Score
    FROM PERSON
    GROUP BY Name,Age
)
SELECT * FROM TEMP WHERE Score>60