鍍金池/ 問答/數(shù)據(jù)庫/ 問一道SQL查詢語句

問一道SQL查詢語句

問一道SQL查詢語句

有一張表：t_student
字段：
id
name
age
status (0,1,2,3,4,5,6)
teacher_id

問：如果teacher_id是空，就不查詢status=0
這種SQL語句怎么寫？

回答
編輯回答
凝雅
select * from test.t_student where (teacher_id ='' and status != '0') or teacher_id != '';
2017年2月25日 12:02
編輯回答
不二心
select * from t_student where teacher_id='' and status!=0
2017年8月18日 02:21
編輯回答
硬扛
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;
2017年8月23日 23:29