时间戳

首页 > 解决方案 > 请帮助使用 GROUP BY 构建 SQL SELECT 请求

sql - 请帮助使用 GROUP BY 构建 SQL SELECT 请求

问题描述

我有以下表格：

callrecord table
callid, calltypekey, requeuecount
123     1            -1
123     2            0
123     3            1
234     1            0
234     3            1
435     3            0
435     1            1
567     1            -1
678     1            0

calltypes table
calltypekey, calltypename
1            name1
2            name2
3            name3

以下 SQL 请求工作正常：

SELECT callid, min(requeuecount) as minreq, max(requeuecount) as maxreq
FROM callrecord 
GROUP BY callid
HAVING count(callid) > 1

并产生以下结果：

callid, minreq, maxreq 
123     -1      1
234     0       1
435     0       1

我想添加对应于 min(requeuecount) 记录的 calltypekey 值，即结果应该如下：

callid, minreq, maxreq, minCTK, maxCTK 
123     -1      1       1       3
234     0       1       1       3
435     0       1       3       1

甚至连名字：

callid, minreq, maxreq, minname, maxname 
123     -1      1       name1    name3
234     0       1       name1    name3
435     0       1       name3    name1

这可能吗？

标签： sqlinformix

解决方案

请试一试。

select t1.callid, t1.minreq, t1.maxreq, t2.Name as minname, t3.Name as maxname from (
SELECT callid, min(requeuecount) as minreq, max(requeuecount) as maxreq
FROM callrecord 
GROUP BY callid
HAVING count(callid) > 1) as t1
left join 
    (select c.callid, ct.Name, c.requeuecount from callrecord c
        inner join calltypes ct on ct.calltypekey = c.calltypekey)  t2 on t2.callid = t1.callid and t2.requeuecount = t1.minreq
left join 
    (select c.callid, ct.Name, c.requeuecount from callrecord c
        inner join calltypes ct on ct.calltypekey = c.calltypekey)  t3 on t3.callid = t1.callid and t3.requeuecount = t1.maxreq

推荐阅读