关于SQL结果集合并的问题。。。posegresql

SELECT 
    a.activeTime,
    b.shareTime,
    c.joinTime,

    a.todayActiveCount , 
    b.todayShareCount , 
    c.todayJoinCount
FROM (
(
SELECT
    to_char(ull.timestamp, 'yyyy-MM-dd') activeTime,
    COUNT(Distinct ull.open_id) todayActiveCount
FROM
    mico_app.user_login_log ull
WHERE
    to_char(ull.timestamp, 'yyyy-MM-dd') BETWEEN '2018-11-01' AND '2018-12-06'
GROUP BY
    activeTime
ORDER BY
    activeTime DESC
) a
FULL JOIN
(
SELECT
    to_char(ushl.share_time, 'yyyy-MM-dd') shareTime,
    COUNT(Distinct ushl.open_id) todayShareCount
FROM
    mico_app.user_share_log ushl
WHERE
    to_char(ushl.share_time, 'yyyy-MM-dd') BETWEEN '2018-11-01' AND '2018-12-05'
GROUP BY
    shareTime
ORDER BY
    shareTime DESC
) b
ON a.activeTime = b.shareTime
FULL JOIN
(
SELECT
    to_char(use.join_time, 'yyyy-MM-dd') joinTime,
    COUNT(*) todayJoinCount
FROM
    mico_app.user use
WHERE
    to_char(use.join_time, 'yyyy-MM-dd') BETWEEN '2018-11-01' AND '2018-12-05'
GROUP BY
    joinTime
ORDER BY
    joinTime DESC
) c
    ON c.joinTime = b.shareTime
)
    GROUP BY a.activeTime , b.shareTime ,c.joinTime ,a.todayActiveCount, b.todayShareCount , c.todayJoinCount

这个结果集我想 要下下面这种结果。。。有什么操作方法吗 或者把以上查询结果用JAVA实现成下面的效果。。。大佬有方法吗

|time |todayActiveCount|todayShareCount|todayJoinCount
|:----- |:----- |----- |
|2018-11-23 | 0 | 0 | 4 |
|2018-11-27 | 0 | 0 | 1 |
|2018-11-29 | 0 | 2 | 0 |
|2018-11-30 | 0 | 0 | 4 |
|2018-12-01 | 0 | 2 | 0 |
|2018-12-02 | 0 | 2 | 0 |
|2018-12-03 | 0 | 0 | 2 |
|2018-12-05 | 8 | 2 | 0 |

可能值没填对，。
意思就是时间聚合到一个字段里， 然后后面的计数有值则用，无值补0

重新建立视图查询

select activeTime, sum(nvl(todayActiveCount, 0))
from (

    select activeTime, todayActiveCount
      from (你的语句）

                union
                select shareTime, todayShareCount
      from (你的语句）
                union
                select joinTime, todayJoinCount
                from (你的语句)
                ）
     group by activeTime

SELECT 
    coalesce(a.activeTime,coalesce(b.shareTime,c.joinTime)) as time,  -- 加了这个
    --a.activeTime , b.shareTime , c.joinTime,

    coalesce(a.todayActiveCount,0) todayActiveCount, coalesce(b.todayShareCount,0) todayShareCount, coalesce(c.todayJoinCount,0) todayJoinCount
FROM (
(
SELECT
    to_char(ull.timestamp, 'yyyy-MM-dd') activeTime,
    COUNT(Distinct ull.open_id) todayActiveCount
FROM
    mico_app.user_login_log ull
GROUP BY
    activeTime
) a
FULL JOIN
(
SELECT
    to_char(ushl.share_time, 'yyyy-MM-dd') shareTime,
    COUNT(Distinct ushl.open_id) todayShareCount
FROM
    mico_app.user_share_log ushl
GROUP BY
    shareTime
) b
ON a.activeTime = b.shareTime
FULL JOIN
(
SELECT
    to_char(use.join_time, 'yyyy-MM-dd') joinTime,
    COUNT(*) todayJoinCount
FROM
    mico_app.user use
GROUP BY
    joinTime
) c
    ON c.joinTime = b.shareTime
)
    GROUP BY a.activeTime , b.shareTime ,c.joinTime ,a.todayActiveCount, b.todayShareCount , c.todayJoinCount
    ORDER BY time

没想到这样就解决了= =

然后日期可能重复，时间紧迫，用笨办法聚合一下就行。

public List<Map<String, Object>> unionList(List<Map<String, Object>> list , String... value) {
        List<Map<String, Object>> inList = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> map = new HashMap<>();
            for (int j = 0; j < value.length; j++) {
                map.put(value[j], list.get(i).get(value[j]));
            }
            inList.add(map);
        }

        List<Map<String, Object>> countList = new ArrayList<>();
        for (int i = 0; i < inList.size(); i++) {
            String time = inList.get(i).get(value[0]).toString();
            int flag = 0;
            for (int j = 0; j < countList.size(); j++) {
                String time_ = countList.get(j).get(value[0]).toString();
                if (time.equals(time_)) {
                    for (int k = 1; k < value.length; k++) {
                        int sum = Integer.parseInt(inList.get(i).get(value[k]).toString()) + Integer.parseInt(countList.get(j).get(value[k]).toString());
                        countList.get(j).put(value[k], sum + "");
                    }
                    flag = 1;
                    continue;
                }
            }
            if (flag == 0) {
                countList.add(inList.get(i));
            }
        }
        return countList;
    }

// 第一个String参数是聚合的主键
unionList(businessDataDao.getUserBusinessData(start, end),
"times", "todayActiveCount", "todayShareCount", "todayJoinCount");