mysql 语句记录

SELECT
  *
FROM
  zx_cjcx_course AS a
  LEFT JOIN zx_cjcx_score AS b ON a.id = b.course_id
WHERE
a.academic_year_id='1'and
a.course_type='2'
总学分

-----------------------------
SELECT
  sum(a.credit) as zfs
FROM
  zx_cjcx_course AS a
  LEFT JOIN zx_cjcx_score AS b ON a.id = b.course_id
WHERE
a.academic_year_id='1'and
a.course_type='2'
and a.term='1'
and b.user_id='lvqi'
and b.score > 60

GROUP BY
b. user_id
-------------------------------------------------

SELECT
  sum(a.credit) AS zfs
FROM
  zx_cjcx_course AS a
  LEFT JOIN zx_cjcx_score AS b ON a.id = b.course_id
WHERE
  a.course_type = [ course_type ]
  AND a.term = [ id ]
  AND b.user_id = 'lvqi'
  AND b.score > 60
GROUP BY
  b.user_id

-----------------------------


总学分
SELECT
  sum(a.jidian) AS zxf
FROM
  zx_cjcx_score AS a
  LEFT JOIN zx_cjcx_course AS b ON b.id = a.course_id
WHERE
  b.academic_year_id = '2'
  AND b.course_type = '2'
  AND b.term = '1'
  AND a.user_id = 'lvqi'
  AND a.score <60
GROUP BY
  a.user_id
SELECT
  course_name,
  score,
  course_type,
  teacher_name,
  credit
FROM
  lt_cjcx_course AS a
  LEFT JOIN lt_cjcx_score AS b ON a.id = b.course_id
WHERE
  course_id = [ id ]
  AND student_id = '202002'
	
	---------------------------------------------------------------------------
	总学分
	SELECT * from zx_cjcx_course a LEFT JOIN zx_cjcx_score b on a.id = b.course_id
	WHERE a.course_type='1'and a.academic_year_id ='1'
	


SELECT
  sum(a.jidian) AS zxf
FROM
  zx_cjcx_score AS a
  LEFT JOIN zx_cjcx_course AS b ON b.id = a.course_id
WHERE
  b.academic_year_id = '2'
  AND b.course_type = '2'
  AND b.term = '1'
  AND a.user_id = 'lvqi'
  AND a.score <60
GROUP BY
  a.user_id
	
	折线图
------------------------------------------------------------
SELECT
'我的平均绩点' as name,

  avg(a.jidian) AS avgjd
	
FROM
  zx_cjcx_score AS a
  LEFT JOIN zx_cjcx_course AS b ON b.id = a.course_id
WHERE
  b.academic_year_id = '2'
  AND b.course_type = '2'
  AND b.term = '1'
  AND a.user_id = 'lvqi'
	 AND a.score >60
  
GROUP BY
  a.user_id
	
	 UNION ALL
	SELECT
'班级平均绩点' as name,

  avg(a.jidian) AS bjavgjd
	
FROM
  zx_cjcx_score AS a
  LEFT JOIN zx_cjcx_course AS b ON b.id = a.course_id
WHERE
  b.academic_year_id = '2'
  AND b.course_type = '2'
  AND b.term = '1'
 
	 AND a.score >60
  
	 UNION ALL
	 
	 SELECT
	'专业平均绩点' as name,
	SUM(c.avgjd) as bjavgjd
	 from
	 
	(SELECT
'专业平均绩点' as name,
-------------------------------------------------

  avg(a.jidian) AS avgjd
	
FROM
  zx_cjcx_score AS a
  LEFT JOIN zx_cjcx_course AS b ON b.id = a.course_id
WHERE
  b.academic_year_id = '2'
  AND b.course_type = '2'
  AND b.term = '1'
 
	 AND a.score >60
	 GROUP BY b.course_name)c
  
  
	-----------------------------
	分数列表
	SELECT
  a.course_name,
  b.score,
	a.id

FROM
  zx_cjcx_course AS a
  LEFT JOIN zx_cjcx_score AS b ON a.id = b.course_id
WHERE
  a.academic_year_id ='2'
  AND a.course_type = '2'
  AND a.term =1
  AND b.user_id = 'lvqi'

课程详情

SELECT
'课程名字' as a.course_name,
'老师名字' as a.teacher_name,
'课程成绩' as b.score,
'学分' as  a.credit
from zx_cjcx_course as a
  LEFT JOIN zx_cjcx_score AS b ON a.id = b.course_id
WHERE a.id='1'


-------------------------------------------------------------
SELECT
  a.course_name as '课程名字',
  a.teacher_name as '老师名字',
  b.score   as  '课程成绩',
 a.credit     as   '学分' 
from zx_cjcx_course as a
  LEFT JOIN zx_cjcx_score AS b ON a.id = b.course_id
WHERE a.id='1' and b.user_id = 'lvqi'

SELECT
  a.course_name AS '课程名字',
  a.teacher_name AS '老师名字',
  b.score AS '课程成绩',
  a.credit AS '学分'
FROM
  zx_cjcx_course AS a
  LEFT JOIN zx_cjcx_score AS b ON a.id = b.course_id
WHERE
  a.id = '1'
  AND b.user_id = 'lvqi'
---------------------借取条件判单-------------------------
SELECT
  bt.id,
  bb.bookno,
  bb.bookcallno,
  bb.bookname,
  bb.bookwriter,
  bb.bookpublish,
  bookdate,
  bb.isbn_no,
  bb.price,
  bt.typecode,
  bt.typename,
  bb.address,
  CASE
    bookstate
    WHEN 0 THEN '不可借'
    WHEN 1 THEN '可借'
  END AS bookstate
FROM
  lt_userbook_basic_zzx bb
  LEFT JOIN lt_userbook_type_zzx bt ON bb.typecode = bt.typecode
WHERE
  CONCAT(
    bookno,
    bookcallno,
    bookname,
    bookwriter,
    bookpublish,
    isbn_no,
    typename
  ) LIKE concat('%', [ name ], '%')
---------------------------------------------------------
借阅图书插入数据
INSERT INTO
  lt_user_lendbookinformation_zzx(
    bookno,
    bookcallno,
    bookname,
    username,
    operator,
    jstime,
    yhdate
  )
VALUES
  (
    [ bookno ],
    [ bookcallno ],
    [ bookname ],
    [ username ],
    '杨华 ',
    NOW(),
    [ yhdate ]
  )
-------------------------------------------------------------