1, under Oracle query methods
SELECT heji. Xm AS name
That heji. The government AS a score
Number of subjects, and count (*)
, listagg (kc | | cj, ', ') WITHIN GROUP (ORDER BY kc) the AS scores in all the subjects
The FROM (
The SELECT xm, SUM (cj) AS zf
The FROM (
SELECT 'zhang' AS xm, '4 - physical AS kc, 93 AS cj FROM dual UNION ALL
SELECT 'zhang' AS xm, '5 - chemical AS kc, 92 AS cj FROM dual UNION ALL
SELECT 'zhang' AS xm, '6 - creatures AS kc, 92 AS cj FROM dual UNION ALL
SELECT 'zhang' AS xm, '1 - language AS kc, 95 AS cj FROM dual UNION ALL
SELECT 'zhang' AS xm, '2 - mathematics AS kc, 90 AS cj FROM dual UNION ALL
SELECT 'zhang' AS xm, '3 - English AS kc, 98 AS cj FROM dual UNION ALL
SELECT 'li si AS xm,' 7 - history AS kc, 97 AS cj FROM dual UNION ALL
SELECT 'li si AS xm,' 8 - geography AS kc, 96 AS cj FROM dual UNION ALL
SELECT 'li si AS xm,' 1 - language AS kc, 90 AS cj FROM dual UNION ALL
SELECT 'li si AS xm,' 2 - mathematics AS kc, 93 AS cj FROM dual UNION ALL
SELECT 'li si AS xm,' 3 - English AS kc, 94 AS cj FROM dual
)
GROUP BY xm
) heji,
(
SELECT 'zhang' AS xm, '4 - physical AS kc, 93 AS cj FROM dual UNION ALL
SELECT 'zhang' AS xm, '6 - creatures AS kc, 92 AS cj FROM dual UNION ALL
SELECT 'zhang' AS xm, '5 - chemical AS kc, 92 AS cj FROM dual UNION ALL
SELECT 'zhang' AS xm, '1 - language AS kc, 95 AS cj FROM dual UNION ALL
SELECT 'zhang' AS xm, '2 - mathematics AS kc, 90 AS cj FROM dual UNION ALL
SELECT 'zhang' AS xm, '3 - English AS kc, 98 AS cj FROM dual UNION ALL
SELECT 'li si AS xm,' 7 - history AS kc, 97 AS cj FROM dual UNION ALL
SELECT 'li si AS xm,' 8 - geography AS kc, 96 AS cj FROM dual UNION ALL
SELECT 'li si AS xm,' 1 - language AS kc, 90 AS cj FROM dual UNION ALL
SELECT 'li si AS xm,' 2 - mathematics AS kc, 93 AS cj FROM dual UNION ALL
SELECT 'li si AS xm,' 3 - English AS kc, 94 AS cj FROM dual
) feke
WHERE heji. Xm=feke. Xm
GROUP BY heji. Xm, heji. Zf
2, I'm used to the query methods:
declare @ TB table (xm varchar (10), kc varchar (10), cj varchar (10))
Insert into @ TB (xm, kc, cj)
SELECT 'zhang' AS xm, '4 - physical AS kc, 93 AS cj UNION ALL
SELECT 'zhang' AS xm, '5 - chemical AS kc, 92 AS cj UNION ALL
SELECT 'zhang' AS xm, '6 - creatures AS kc, 92 AS cj UNION ALL
SELECT 'zhang' AS xm, '1 - language AS kc, 95 AS cj UNION ALL
SELECT 'zhang' AS xm, '2 - mathematics AS kc, 90 AS cj UNION ALL
SELECT 'zhang' AS xm, '3 - English AS kc, 98 AS cj UNION ALL
SELECT 'li si AS xm,' 7 - history AS kc, 97 AS cj UNION ALL
SELECT 'li si AS xm,' 8 - geography AS kc, 96 AS cj UNION ALL
SELECT 'li si AS xm,' 1 - language AS kc, 90 AS cj UNION ALL
SELECT 'li si AS xm,' 2 - mathematics AS kc, 93 AS cj UNION ALL
SELECT 'li si AS xm,' 3 - English AS kc, 94 AS cj
SELECT the name=xm
, total score=SUM (the convert (float, cj))
, number of subjects=COUNT (*)
, score=stuff ((select ', '+ kc + cj from @ TB where xm=a.x m order by kc
For XML path (' ')
), 1, 1, '
)
The FROM @ TB a
GROUP BY xm
Two methods of query result set is the same, as follows:
Wrote in the last: my question is how to improve the efficiency of the line used to this kind of transfer list query, not how to implement the transfer line column query, in addition, this code is only auxiliary describe my problem, not the problem itself, my production environment, kc column value is not fixed (unlike the above have fixed a few courses), used in the code FROM the table variable @ TB behind the words, in a production environment is a complex query result set, rather than a table,
This is my first time to ask questions of the impression, hope to get answer, the answer away before saving scores, just use this time, I can as soon as possible to post,
CodePudding user response:
Precisely, the slow sure not because stuff, this action is only string function operation, not particularly slow,Slow, mainly is:
1. The stuff inside got subquery
2. The for XML path
3. The xm indexed field? No index is not surprising that slow,
4. @ TB situation is a result set, should be cached in the temporary table, then add xm temporary table index, and query the temporary table,
If you are using sqlserver2017 or higher, can use the new function string_agg to simplify:
SELECT name=xm
, total score=SUM (the convert (float, cj))
, number of subjects=COUNT (*)
, the score=string_agg (kc + cj, ', ')
The FROM @ TB a
GROUP BY xm
Never felt poor than orcle used to () under the condition of the same hardware, as long as the optimization of good, is the same good with,
In addition, there is no way, you can use a space, in time, to make statements, result generated on a regular basis, or generate trigger user query results directly watch must be slow,
Such as: student examination, certainly not every day, to generate a report immediately when I input the result,