Tag:mysql
Article From:https://segmentfault.com/q/1010000012140101
Question:

Ask a SQL query statement

There is a table: t_student
Field:
id
name
age
status (0,1,2,3,4,5,6)
teacher_id

Q: if teacher_id is empty, it will not query status=0.
How does this SQL statement write?

Answer 0:
select * from test.t_student where (teacher_id ='' and status != '0') or teacher_id != '';

Answer 1:
select * from t_student where teacher_id='' and status!=0

Answer 2:
WITH T AS (
  SELECT 1 AS ID, 'A' AS NAME, 21 AS AGE, 0 AS STATUS, 1 AS TEACHERID FROM DUAL UNION ALL 
  SELECT 2 AS ID, 'B' AS NAME, 20 AS AGE, 1 AS STATUS, 1 AS TEACHERID FROM DUAL UNION ALL
  SELECT 3 AS ID, 'C' AS NAME, 21 AS AGE, 2 AS STATUS, 2 AS TEACHERID FROM DUAL UNION ALL
  SELECT 4 AS ID, 'D' AS NAME, 22 AS AGE, 3 AS STATUS, 3 AS TEACHERID FROM DUAL UNION ALL
  SELECT 5 AS ID, 'E' AS NAME, 23 AS AGE, 0 AS STATUS, NULL AS TEACHERID FROM DUAL 
)
SELECT * FROM T
WHERE (TEACHERID IS NULL AND STATUS <> 0) OR TEACHERID IS NOT NULL;

Link of this Article: Ask a SQL query statement

Leave a Reply

Your email address will not be published. Required fields are marked *