求一sql语句

Question

原始表：售货人   商品   数量
--------------------
A        牙膏    1
A        洗衣粉  2
A        脸盆    5要求：写一条sql语句实现下面的效果售货人   商品                  数量
-----------------------------------------------
A        牙膏、洗衣粉、脸盆    8

Answer 1

10G
select 售货人,WMSYS.WM_CONCAT(商品) 商品,sum(数量)  数量
   from table_a
   group by 售货人

Answer 2

10G以下，没有WMSYS.WM_CONCAT,要用sys_connect_by_path来代替有，参考此贴写法http://topic.csdn.net/u/20080505/11/a0958b42-d938-465f-972a-0f61a2969c97.html?seed=491226048

Answer 3

通用方法如下：DROP TABLE tt;
create table tt (saler VARCHAR2(10),product  VARCHAR2(10),num int); CREATE OR REPLACE FUNCTION str_to_number(p_str VARCHAR2) RETURN NUMBER AS
r NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'select '||p_str||' from dual' INTO r;
  RETURN r;
END;
/ 
INSERT INTO TT VALUES ('A', '牙膏', 1);
INSERT INTO TT VALUES ('A', '洗衣粉', 2);
INSERT INTO TT VALUES ('A', '脸盆', 5);
COMMIT;SELECT SALER,
       LTRIM(SYS_CONNECT_BY_PATH(PRODUCT, ','), ',') products,
       STR_TO_NUMBER(LTRIM(SYS_CONNECT_BY_PATH(NUM, '+'), '+')) sum_num
  FROM (SELECT SALER,
               PRODUCT,
               NUM,
               ROW_NUMBER() OVER(PARTITION BY SALER ORDER BY NULL) RN,
               ROW_NUMBER() OVER(PARTITION BY SALER ORDER BY NULL) - 1 RN1
          FROM TT) WHERE CONNECT_BY_ISLEAF = 1
          START WITH rn1=0
CONNECT BY PRIOR RN = RN1;

Answer 4

提示我：ORA-00904: "CONNECT_BY_ISLEAF": 无效的标识符

Answer 5

connect_by_isleaf也是10g提供的，9i无法使用。9i/10g都可用更改如下:SELECT SALER,
       LTRIM(SYS_CONNECT_BY_PATH(PRODUCT, ','), ',') PRODUCTS,
       STR_TO_NUMBER(LTRIM(SYS_CONNECT_BY_PATH(NUM, '+'), '+')) SUM_NUM
  FROM (SELECT SALER,
               PRODUCT,
               NUM,
               ROW_NUMBER() OVER(PARTITION BY SALER ORDER BY NULL) RN,
               ROW_NUMBER() OVER(PARTITION BY SALER ORDER BY NULL) - 1 RN1,
               CASE
                 WHEN ROW_NUMBER()
                  OVER(PARTITION BY SALER ORDER BY NULL) = COUNT(1)
                  OVER(PARTITION BY SALER ORDER BY NULL) THEN
                  'x'
               END FLAG
          FROM TT)
 WHERE FLAG = 'x'
 START WITH RN1 = 0
CONNECT BY PRIOR RN = RN1;

Answer 6

 楼主用的不是10G的oracle,版本不对,所以没有这个函数:CONNECT_BY_ISLEAF
 9i用:
select saler,
       max(decode(rn,1,product))||','||max(decode(rn,2,product))||','||max(decode(rn,3,product))
       ,sum(num)
from
(
select  saler,product,num,row_number() over(partition by saler order by saler)  rn from tt
)
group by salerRESULT:
1 A 牙膏,洗衣粉,脸盆 8

Answer 7

DROP TABLE tt;
create table tt (saler VARCHAR2(10),product  VARCHAR2(10),num int); INSERT INTO TT VALUES ('A', '牙膏', 1);
INSERT INTO TT VALUES ('A', '洗衣粉', 2);
INSERT INTO TT VALUES ('A', '脸盆', 5);
INSERT INTO TT VALUES ('B', '牙膏', 1);
INSERT INTO TT VALUES ('B', '洗衣粉', 5);
COMMIT;SQL:
select saler, 
      max(decode(rn,1,product)) ¦ ¦',' ¦ ¦max(decode(rn,2,product)) ¦ ¦',' ¦ ¦max(decode(rn,3,product)) 
      ,sum(num) 
from 
( 
select  saler,product,num,row_number() over(partition by saler order by saler)  rn from tt 
) 
group by saler RESULT:
A 牙膏,洗衣粉,脸盆 8
B 牙膏,洗衣粉, 6

Answer 8

这个的确写错了。9i/10g都可用更改如下:DROP TABLE tt;
create table tt (saler VARCHAR2(10),product  VARCHAR2(10),num int); CREATE OR REPLACE FUNCTION str_to_number(p_str VARCHAR2) RETURN NUMBER AS
r NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'select '||p_str||' from dual' INTO r;
  RETURN r;
END;
/ 
INSERT INTO TT VALUES ('A', '牙膏', 1);
INSERT INTO TT VALUES ('A', '洗衣粉', 2);
INSERT INTO TT VALUES ('A', '脸盆', 5);
INSERT INTO TT VALUES ('b', '牙膏', 1);
INSERT INTO TT VALUES ('b', '洗衣粉', 3);
INSERT INTO TT VALUES ('b', '脸盆', 5);
COMMIT;
 SELECT SALER,
       LTRIM(SYS_CONNECT_BY_PATH(PRODUCT, ','), ',') PRODUCTS,
       STR_TO_NUMBER(LTRIM(SYS_CONNECT_BY_PATH(NUM, '+'), '+')) SUM_NUM,
       FLAG
  FROM (SELECT SALER,
               PRODUCT,
               NUM,
               ROW_NUMBER() OVER(PARTITION BY SALER ORDER BY NULL) RN,
               ROW_NUMBER() OVER(PARTITION BY SALER ORDER BY NULL) - 1 RN1,
               CASE
                 WHEN ROW_NUMBER()
                  OVER(PARTITION BY SALER ORDER BY NULL) = COUNT(1)
                  OVER(PARTITION BY SALER ORDER BY NULL) THEN
                  'x'
               END FLAG
          FROM TT)
 WHERE FLAG = 'x'
 START WITH RN1 = 0
CONNECT BY PRIOR RN = RN1
       AND PRIOR SALER = SALER;
 

Answer 9

重复的品名可以用distinct把它先去掉,如:
select * from 
(
select distinct a,b,c from test
)