我由具有類似值的列名要為了“值1”空間“值2”在同一列中。 例如。
| 第一列 |
|---|
| 值1 值2 |
| NSPP02 2111 |
| NSPP02 2110 |
| NSPP01 2111 |
我想先按 value2 排序，然后按 value1 排序。ASC 按值 2，然后按值 1 ASC。
我使用聚合如下：
select
pdc.semester as Semester,
count(pdc.CNIC) as TotalRegistrations,
count(s.studentid) as TotalRegistered,
count(case when r.Grade is not null then 'Appeared' end) as Appeared,
count(case when r.Grade='f' then 'fail' end) as Failed,
count(case when r.grade <>'f' then 'pass' end) as Passed
from
PDC_PreRegistration pdc
left join
Students s on pdc.Semester = s.CurSemester and s.nic = pdc.CNIC
left join
studentresultnet r on s.studentid = r.studentid and s.CurSemester = r.Semester
left join
semester se on se.Semester = r.Semester
where
pdc.semester in (select CurSemester from students where batch = 'PD2110')
group by
pdc.Semester
order by
semester desc
此查詢的結果如下：
| 學期 | 注冊總數 | 注冊總數 | 出現 | 失敗的 | 通過 |
|---|---|---|---|---|---|
| NSPP02 2111 | 121 | 81 | 0 | 0 | 0 |
| NSPP02 2110 | 37 | 35 | 24 | 1 | 23 |
| NSPP01 2111 | 76 | 42 | 0 | 0 | 0 |
我希望結果是這樣的：
| 學期 | 注冊總數 | 注冊總數 | 出現 | 失敗的 | 通過 |
|---|---|---|---|---|---|
| NSPP02 2110 | 37 | 35 | 24 | 1 | 23 |
| NSPP01 2111 | 76 | 42 | 0 | 0 | 0 |
| NSPP02 2111 | 121 | 81 | 0 | 0 | 0 |
ASC 按值 2，然后按值 1 ASC。
學期列的示例序列如下：
- NSPP01 2110
- NSPP02 2110
- NSPP01 2111
- NSPP02 2111
- NSPP01 2112
- NSPP02 2112
- NSPP01 2201
- NSPP02 2201
- NSPP01 2202
- NSPP02 2202
- NSPP01 2203
- NSPP02 2203
- 等等
檢查此螢屏截圖：
查詢及其結果的螢屏截圖
uj5u.com熱心網友回復：
我不確定您的原因或列背后的邏輯，但理想情況下，您將這些作為 2 個單獨的列。它使這樣的事情變得更容易。
話雖如此，我認為有兩種方法可以解決這個問題。
如果您的 2 個值的長度永遠不會改變，即值 NSPP02 將始終包含 5 個字符，而 2203 個值將始終包含 4 個字符，那么您可以執行以下操作：
SELECT LEFT(COLUMN_1,5) AS VALUE 1
SELECT RIGHT(COLUMN_1,4) AS VALUE 2
ORDER BY VALUE 2, VALUE 1
或者，如果每個值中的字符數有增加的空間，那么您需要使用諸如 SUBSTRING 函式之類的東西。有一些不錯的細節在這里：
uj5u.com熱心網友回復：
您實際上可以先拆分學期列SORT，然后您可以取消這些輔助列以獲得所需的輸出：
SELECT Semester, TotalRegistrations, TotalRegistered, Appeared, Failed, Passed
FROM(
SELECT pdc.semester as Semester,count(pdc.CNIC) as
TotalRegistrations,count(s.studentid) as TotalRegistered,
count(case when r.Grade is not null then 'Appeared' end) as Appeared,
count(case when r.Grade='f' then 'fail' end) as Failed,
count(case when r.grade <>'f' then 'pass' end) as Passed,
TRIM(LEFT(pdc.semester, CHARINDEX(' ', pdc.semester) - 1)) as frst,
TRIM(RIGHT(pdc.semester, LEN(pdc.semester) - CHARINDEX(' ', pdc.semester))) as sec
FROM PDC_PreRegistration pdc
LEFT JOIN Students s on pdc.Semester=s.CurSemester and s.nic=pdc.CNIC
LEFT JOIN studentresultnet r on s.studentid=r.studentid and s.CurSemester=r.Semester
LEFT JOIN semester se on se.Semester=r.Semester
WHERE pdc.semester
IN (select CurSemester from students where batch='PD2110')
GROUP BY pdc.Semester
)
ORDER BY sec, frst
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/363740.html
標籤：sql 查询语句 sql-order-by 短信
下一篇：對子查詢進行計數