mysql - 我想根据 schcd、entry_date 和 mobile_no 在单行中显示数据

Question

我写了下面的查询

SELECT distcd,blkcd,schcd,entry_date,mobile_no,
class1,class2,class3,class4,class5,class6,class7,class8,class9,class10,
class11,class12 
FROM adhoc_studentatt_details_prv 
WHERE SCHCD='20130100101' AND entry_date='2019-05-01 00:00:00' and mobile_no='9801355122'

请帮帮我。

Answer 1

您需要 MAX() 和 GROUP BY，以获得您想要的输出。

它将按 distcd、blkcd、schcd、entry_date、mobile_no 对列进行分组，并在一行中返回结果。

SELECT distcd,
    blkcd,
    schcd,
    entry_date,
    mobile_no,
    MAX(class1) AS class1,
    MAX(class2) AS class2,
    MAX(class3) AS class3,
    MAX(class4) AS class4,
    MAX(class5) AS class5,
    MAX(class6) AS class6,
    MAX(class7) AS class7,
    MAX(class8) AS class8,
    MAX(class9) AS class9,
    MAX(class10) AS class10,
    MAX(class11) AS class11,
    MAX(class12) AS class12
FROM adhoc_studentatt_details_prv
WHERE SCHCD = '20130100101'
    AND entry_date = '2019-05-01 00:00:00'
    AND mobile_no = '9801355122'
GROUP BY distcd, blkcd, schcd, entry_date, mobile_no