Shulou(Shulou.com)06/01 Report--

1. The actual demand is as follows:

Description: in the above table, a student has multiple courses and grades. Now you would like to have a SQL statement that combines the course name and grade, as follows:

Description: use a field to represent all the courses and grades of this student, including all the course names and exam scores.

2. Problem analysis.

To solve this problem, you can use the method of creating function to merge the two fields, but this is too troublesome, because it is the use of Oracle10g, so you can use the function wmsys.wm_concat () to achieve this function.

3. Solution

SQL:

The first step is to write a view view_ly_sjdxpt_jwcj and merge the two fields

SQL Code:

Createorreplaceviewview_ly_sjdxpt_jwcj as

Select

Xh hm

Kcmc | | zscj as kc_cj

From dcuser.ly_xs_bzks_cjb b

Results:

The second step is to write another view and merge the KC_CJ fields using wmsys.wm_concat ()

SQL Code:

Createorreplaceviewview_ly_sjdxpt_jwcj_kz asselect hm,wmsys.wm_concat (kc_cj) as kccj from view_ly_sjdxpt_jwcj groupby hm

Results:

Step 3: put it into the sql you actually need to use:

SQL Code:

Select distinct

"academic performance" dxlb

Xh hm

(select lxdh fromdcuser.LY_XS_BZKS_XSJBXX where xh=b.xh) sjhm

To_char (sysdate,'yyyyMMddHH24miss') crsj

Xm | | 'Hello, students. Your grades in each subject this semester are as follows:' | | c.kccj as nr

'' bz

'' fssj

Fromdcuser.ly_xs_bzks_cjb b

Leftouterjoin DCUSER_KZ.VIEW_LY_SJDXPT_JWCJ_KZ c on b.xh=c.hm

Results:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.