1,617
社区成员
发帖
与我相关
我的任务
分享
SELECT T1.PEOPLE,
(SELECT COUNT(*) FROM TEST T2 WHERE T2.商家核单人 = T1.PEOPLE),
(SELECT COUNT(*) FROM TEST T3 WHERE T3.客户核单人 = T1.PEOPLE),
(SELECT NVL(SUM(T4.商家核对金额), 0)
FROM TEST T4
WHERE T4.商家核单人 = T1.PEOPLE),
(SELECT NVL(SUM(T5.客户核对金额), 0)
FROM TEST T5
WHERE T5.客户核单人 = T1.PEOPLE)
FROM (SELECT DISTINCT 商家核单人 PEOPLE
FROM TEST
UNION
SELECT DISTINCT 客户核单人 PEOPLE
FROM TEST) T1
[/quote]
谢谢高手的指导!辛苦!
唯一ID 核单人 核对订单时间 核对金额 类型
10001 张三 2014-01-01 100 商家核单
10002 张三 2014-01-01 200 客户核单
10003 李四 2014-01-02 300 商家核单
请问高手,我把这个表重新设计成这样,得到我要的结果是不是效率高点?
[/quote]
恩,这样设计更直观,建议用这种方法设计表。
开会去了。SELECT T1.PEOPLE,
(SELECT COUNT(*) FROM TEST T2 WHERE T2.商家核单人 = T1.PEOPLE),
(SELECT COUNT(*) FROM TEST T3 WHERE T3.客户核单人 = T1.PEOPLE),
(SELECT NVL(SUM(T4.商家核对金额), 0)
FROM TEST T4
WHERE T4.商家核单人 = T1.PEOPLE),
(SELECT NVL(SUM(T5.客户核对金额), 0)
FROM TEST T5
WHERE T5.客户核单人 = T1.PEOPLE)
FROM (SELECT DISTINCT 商家核单人 PEOPLE
FROM TEST
UNION
SELECT DISTINCT 客户核单人 PEOPLE
FROM TEST) T1
[/quote]
谢谢高手的指导!辛苦!
唯一ID 核单人 核对订单时间 核对金额 类型
10001 张三 2014-01-01 100 商家核单
10002 张三 2014-01-01 200 客户核单
10003 李四 2014-01-02 300 商家核单
请问高手,我把这个表重新设计成这样,得到我要的结果是不是效率高点?
SELECT T1.PEOPLE,
(SELECT COUNT(*) FROM TEST T2 WHERE T2.商家核单人 = T1.PEOPLE),
(SELECT COUNT(*) FROM TEST T3 WHERE T3.客户核单人 = T1.PEOPLE),
(SELECT NVL(SUM(T4.商家核对金额), 0)
FROM TEST T4
WHERE T4.商家核单人 = T1.PEOPLE),
(SELECT NVL(SUM(T5.客户核对金额), 0)
FROM TEST T5
WHERE T5.客户核单人 = T1.PEOPLE)
FROM (SELECT DISTINCT 商家核单人 PEOPLE
FROM TEST
UNION
SELECT DISTINCT 客户核单人 PEOPLE
FROM TEST) T1