mysql的多表查询

 方法一：

select
  q.device_id,
  q.question_id,
  q.result
from
  question_practice_detail q,
  user_profile u
where
  q.device_id = u.device_id
  and university = '浙江大学'
order by
  q.question_id asc

方法二：

select
  q.device_id,
  q.question_id,
  q.result
from
  question_practice_detail q
  join user_profile u on q.device_id = u.device_id
  and university = '浙江大学'
order by
  q.question_id asc

方法三：

select
  device_id,
  question_id,
  result
from
  question_practice_detail
where
  device_id in (
    select
      device_id
    from
      user_profile
    where
      university = '浙江大学'
  )
order by
  question_id asc

 

select
    university,
    difficult_level,
    round(count(qpd.question_id) / count(distinct qpd.device_id), 4) as avg_answer_cnt
from question_practice_detail as qpd
left join user_profile as up on up.device_id=qpd.device_id
left join question_detail as qd on qd.question_id=qpd.question_id
group by university, difficult_level

SELECT t1.university,t3.difficult_level,COUNT(t2.result) / COUNT(DISTINCT t2.device_id) AS avg_answer_cnt FROM question_practice_detail AS t2 LEFT JOIN user_profile AS t1 ON t2.device_id = t1.device_id LEFT JOIN question_detail AS t3 ON t2.question_id = t3.question_id GROUP BY t1.university, t3.difficult_level HAVING t1.university = '山东大学'

 方法一：
SELECT "25岁以下" as age_cut,count(device_id)
FROM user_profile
WHERE age<25 OR age IS null
UNION ALL
SELECT "25岁及以上" as age_cut,count(device_id)
FROM user_profile
WHERE age>=25

方法二：
SELECT
CASE
WHEN age<25 or age is null THEN '25岁以下'
WHEN age>=25 THEN '25岁及以上'
END age_cut,count(*) as number
FROM user_profile
group by age_cut

方法三：

SELECT (CASE WHEN age < 25 THEN '25岁以下'              
             WHEN age IS Null THEN'25岁以下'              
             ELSE'25岁及以上' END) AS age_cut,COUNT(*) AS number        
       FROM user_profile        
       GROUP BY age_cut;

SELECT (CASE WHEN age >= 25 THEN '25岁及以上'
             ELSE'25岁以下' END) AS age_cut,COUNT(*) AS number
       FROM user_profile
       GROUP BY age_cut;

SELECT (CASE WHEN age < 25 THEN '25岁以下'
             WHEN age IS Null THEN'25岁以下'
             WHEN age >= 25 THEN '25岁及以上'
        END) AS age_cut,COUNT(*) AS number
       FROM user_profile
       GROUP BY age_cut;

方法四：

SELECT IF(age >= 25,'25岁及以上','25岁以下') AS age_cut,COUNT(*) AS number
       FROM user_profile
       GROUP BY age_cut;