时间戳

首页 > 解决方案 > SQL 语法查询 order by

sql - SQL 语法查询 order by

问题描述

SELECT TCID, START_TIME, RESULT, 
       cast(START_TIME as date) as m_date,
       max(cast(START_TIME as time)) as max_time
FROM jenkins_result.JENKINS_RESULT
WHERE TCID = 'A330506'
GROUP BY TCID, m_date;

这是我的数据：

ID TCID START_DATE RESULT
1545240 A435727 2020-11-08 03:11:43 PASS
1545334 A435727 2020-11-08 03:19:53 PASS
1547439 A435727 2020-11-09 03:11:52 PASS
1547621 A435727 2020-11-09 03:20:05 通过
1548388 A435727 2020-11-09 07:51:29 通过
1558801 A435727 2020-11-12 00:11:10 通过
1561899 A435727 2020-11-12 08:48:59 通过

我想像这样获得每个 TCID 跟踪日期的结果

ID TCID START_DATE 结果
1545334 A435727 2020-11-08 03:19:53 通过
1548388 A435727 2020-11-09 07:51:29 通过
1561899 A435727 2020-11-12 08:48:59 通过

但结果是这样的：

1545240 A435727 2020-11-08 03:11:43 通过 2020-11-08 03:19:53
1547439 A435727 2020-11-09 03:11:52 通过 2020-11-09 07:51:29
1558801 A435- 11-12 00:11:10 通过 2020-11-12 08:48:59

def connect_cli_server(self):
    connect_success = 0
    if self.ssh_client is None:
        self.ssh_client = paramiko.SSHClient()
        self.ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy)
        for cnt in range(self.retry_cnt):
            try:
                self.ssh_client.connect(self.ip, 22, self.id, self.pw, timeout=self.time_out,
                                        banner_timeout=self.banner)
                connect_success = 1
                break
            except:
                if cnt < 10:
                    time.sleep(random.uniform(0.1, 0.3))
                if 10 <= cnt < 20:
                    time.sleep(random.uniform(0.1, 1))
                else:
                    time.sleep(random.uniform(0.5, 1.5))
                continue
        if not connect_success:
            try:
                self.connect_cli_server_thru_remote_server()
            except Exception as error:
                print(error)
                return False
        return True

    def send_command(self, ssh_client, command):
    chan = ssh_client.get_transport().open_session()
    chan.get_pty()
    fileobject = chan.makefile()
    chan.exec_command(command)
    byteoutput = fileobject.read()
    convetedstring = byteoutput.decode("UTF-8")
    return convetedstring

标签： sql

解决方案

你可以试试这个：

SELECT TCID
      ,START_TIME
      ,RESULT
      ,cast(START_TIME as date) as m_date
      ,max(cast(START_TIME as time)) as max_time
FROM jenkins_result.JENKINS_RESULT
WHERE TCID='A330506'
GROUP BY TCID
        ,START_TIME
        ,RESULT
        ,cast(START_TIME as date)
ORDER BY TCID
        ,m_date;

这应该与此相同：

SELECT DISTINCT TCID
               ,START_TIME
               ,RESULT
               ,cast(START_TIME as date) as m_date
               ,max(cast(START_TIME as time)) OVER() as max_time
FROM jenkins_result.JENKINS_RESULT
WHERE TCID='A330506'
ORDER BY TCID
        ,m_date;

或者如果您需要获取每个MAX值TCID：

SELECT DISTINCT TCID
               ,START_TIME
               ,RESULT
               ,cast(START_TIME as date) as m_date
               ,max(cast(START_TIME as time)) OVER(PARTITION BY TCID) as max_time
FROM jenkins_result.JENKINS_RESULT
WHERE TCID='A330506'
ORDER BY TCID
        ,m_date;

推荐阅读