PLSQL整理

--删除重复数据只留一条

delete from t_kpi_province_m where id not in (

select max(id) from t_kpi_province_m t group by t.指标

)

--商品数量

SELECT 商品名称,SUM（商品总量）剩余数量 FROM

（SELECT * FROM AAA

UNION ALL

SELECT 商品名称,-出库数量 FROM BBB）A GROUP BY 商品名称

--查连胜超过两次的球队

select max(nn.team) team, min(nn.y) B, max(nn.y) + 1 E

from (select n2.team, n2.y

from (select * from nba) n1

join (select * from nba) n2 on n1.team = n2.team

where n1.y = n2.y + 1) nn

group by (nn.y - rownum)

order by B

--oracle rownum

rownum是oracle预处理字段，默认标序是1，只有记录集已经满足条件后才会进行后续编号。由于第一条记录rownum默认是1，

而你的条件是rownum>=6 对第一条记录比较它的rownum肯定不大于6 所以不满足条件 oracle舍弃第一条记录将数据库第二条记录标序为1再进行比较

肯定都不满足rownum>=6 这样循环也就是说由于没有记录满足rownum>=6所以记录一直被舍弃，rownum一直是1 。

排序方法：

select * from (

select a1.*, rownum rwn from emp a1 where rownum <=10

) where rwn >= 6;

或者

select * from (

select qx.*,row_number() over(order by qx.empno) rwn from emp qx

) where rwn between 6 and 10赞同15| 评论(3)

/**

*declare自定义赋值方法

*以scott账户为例

*2012/9/11 11:17:13

declare

n_num number;

--变量赋值

v_char varchar2(30):='gmd';

--常量

c_num constant number:=50;

--属性类型数据

c_type emp.ename%type;

r_row dept%rowtype;

b_boolean boolean := true;

--自定义数据类型

type my_type is record(

mystr varchar2(30),

mysuser varchar2(30),

myname varchar2(30)

);

c_my_type my_type;

begin

--对boolean类型数据赋值

if b_boolean = true then

dbms_output.put_line('真值');

else

dbms_output.put_line('假值');

end if;

n_num:=50;

dbms_output.put_line(n_num);

select ename into v_char from emp where emp.empno=7369;

dbms_output.put_line(v_char);

--给%type类型数据赋值

select ename into c_type from emp where emp.empno=7499;

dbms_output.put_line(c_type);

--给%rowtype类型数据赋值

select * into r_row from dept where deptno=10;

dbms_output.put_line(r_row.deptno||r_row.dname||r_row.loc);

--给自定义数据类型赋值

select ename,job,mgr into c_my_type from emp where emp.empno=7369;

dbms_output.put_line(c_my_type.mystr||c_my_type.mysuser||c_my_type.myname);

end;

/**

*case用法

*2012/9/11 13:54:29

declare

n_num number:=15;

begin

--第一种case用法

case n_num

when 10 then

dbms_output.put_line('第一个case');

when 20 then

dbms_output.put_line('第二个case');

else

dbms_output.put_line('没了');

end case;

--第二种case用法

case

when n_num<10 then

dbms_output.put_line('小于10');

when n_num>10 and n_num<20 then

dbms_output.put_line('大于10小于20');

else

dbms_output.put_line('未知数');

end case;

end;

/**

*隐式游标

*2012/9/11 13:54:29

declare

begin

update emp set ename='牛郎' where empno=7369;

if sql%found then

dbms_output.put_line(sql%rowcount);

elsif sql%notfound then

dbms_output.put_line('没有影响');

end if;

commit;

end;

/**

*显式游标

*2012/9/11 13:54:29

DECLARE

s_int NUMBER(8) := 10;

r_row dept%ROWTYPE;

CURSOR my_cursor(

deptno NUMBER,

dname VARCHAR2,

loc VARCHAR2) IS

SELECT *

FROM dept

WHERE deptno = deptno

AND dname = dname

AND loc = loc;

BEGIN

OPEN my_cursor(s_int, 'ACCOUNTING', 'NEW YORK');

LOOP

FETCH my_cursor INTO r_row;

exit WHEN my_cursor%NOTFOUND;

dbms_output.Put_line(r_row.deptno);

dbms_output.Put_line(r_row.dname);

dbms_output.Put_line(r_row.loc);

END LOOP;

CLOSE my_cursor;

END;

/**

*更新游标，指向单表的游标

*2012/9/11 13:54:29

declare

cursor mycursor is

select * from dept for update;

begin

for myrow in mycursor

loop

dbms_output.put_line(myrow.loc);

--select length(dname) from dept where deptno=10;

--update dept set dname=myrow.loc||'1' where current of mycursor;

--注意的是 current of mycursor

update dept set dname = substr(myrow.dname,1,length(myrow.dname)-2) where current of mycursor;

end loop;

commit;

end;

更新游标注意点

1.通过游标更新行 for update【of 列名】必须和where current of cursor 配套使用。

2.【of 列名】主要用在指向多表关联的游标中，单表的游标默认锁定。

2.of后是执行要进行锁定表的列【任意一列即可】

3.游标指向关联查询的结果集时，必须指明进行锁定的表【要进行更新的表】，

如果未指定锁定表，将无法更新数据。

4.如果指定了其中A表的锁，更新B表，出错【01410 无效的rowid】。

--变量声明赋值

declare

v_char varchar2(30);

v_char1 varchar2(30);

begin

select t.procedure_name into v_char from werrorlog t where id =2724194;

dbms_output.put_line(v_char);

select regexp_substr(v_char||1234,'[0-9]+' ) into v_char1 from dual;--用正则表达式取出数字

dbms_output.put_line(v_char1);

end;

--base from sniper table werrorlog

DECLARE

CURSOR mycur IS

SELECT * FROM werrorlog; --DECLARE CURSOR

myrecord werrorlog%ROWTYPE; --DECLARE A RECORD ,AND THE RECORD PROPERTY IS FROM THE TABLE BOOKS

BEGIN

OPEN mycur; --OPEN CURSOR

FETCH mycur INTO myrecord;--FETCH ONE Record TO RECORD FROM CURSOR

WHILE mycur%FOUND LOOP -- LOOP WHEN HAS RECORDS

dbms_output.put_line(myrecord.id||','||myrecord.procedure_name);

FETCH mycur INTO myrecord;--FETCH ONE Record TO RECORD FROM CURSOR AGAIN

END LOOP; --END LOOP

CLOSE mycur;--CLOSE CURSOR

END;

sourceforge.com

--去重复,删除时间段精确到分钟

delete from t_kpi_city_m t where t.类型='GSM'

AND (T.指标='GSM载频个数'

or T.指标='GSM小区个数'

or T.指标='GSM基站个数')

and to_char(t.月份,'yyyy-mm-dd')='2012-08-01'

and to_char(t.创建日期,'mi')='33';

--修表语句

delete from t_kpi_city_m t where t.指标 in ('GSM无线利用率',''

and to_char(t.月份,'yyyy-mm-dd')='2012-08-01'

and to_char(t.创建日期,'hh24')='11'

delete from t_kpi_city_m t

where to_char(t.月份,'yyyy-mm-dd')='2012-08-01'

and to_char(t.创建日期,'hh24')='11'

--动态建表

create or replace procedure proc_test

(

table_name in varchar2, --表名

field1 in varchar2, --字段名

datatype1 in varchar2, --字段类型

field2 in varchar2, --字段名

datatype2 in varchar2 --字段类型

) as

str_sql varchar2(500);

begin

str_sql:=’create table ’||table_name||’(’||field1||’ ’||datatype1||’,’||field2||’ ’||datatype2||’)’;

execute immediate str_sql; --动态执行DDL语句

exception

when others then

null;

end ;

--动态sql执行

execute proc_test('dinya_test','id','number(8) not null','name','varchar2(100)');

其实就是存储过程的方式，存储过程调用见java调用存储过程

--查看表占用的空间

select s.*,bytes/1024/1024 from user_segments s

create table poieva

(

MONTH date,

城市 VARCHAR2(10),

区域 VARCHAR2(50),

poiid INTEGER,

名称 VARCHAR2(100),

主要类别 VARCHAR2(50),

次要类别 VARCHAR2(50),

细要类别 VARCHAR2(50),

公众 VARCHAR2(10),

教育 VARCHAR2(10),

家园 VARCHAR2(10),

集团 VARCHAR2(10),

类型 VARCHAR2(50),

投产时间 date,

网龄 number(8),

寿命指示 VARCHAR2(50),

阶段名称 VARCHAR2(50),

ap数量 number(8),

运营AP数量 number(8),

室内放装AP数量 number(8),

室内合路AP数量 number(8),

大功率AP数量 number(8),

基站型AP数量 number(8),

脱管AP数量 number(8),

脱管率 NUMBER(20,12),

工程中AP数量 number(8),

建设规模 number(8),

建设规模指示 VARCHAR2(50),

建设规模说明 VARCHAR2(50),

网络流量 NUMBER(20,12),

下行流量 NUMBER(20,12),

上行流量 NUMBER(20,12),

流量规模 number(8),

流量规模指示 VARCHAR2(50),

流量规模说明 VARCHAR2(50),

全省排名 VARCHAR2(50),

行业排名 VARCHAR2(50),

城市行业排名 VARCHAR2(50),

城市排名 VARCHAR2(50),

平均每日流量 NUMBER(20,12),

平均ap每日流量 NUMBER(20,12),

有效ap平均日流量 NUMBER(20,12),

流量密度 number(8),

流量密度指示 VARCHAR2(50),

流量密量说明 VARCHAR2(50),

超忙ap数 number(8),

超闲ap数 number(8),

巨流型ap数量 number(8),

大流量Ap数量 number(8),

中流量ap数量 number(8),

小流量ap数量 number(8),

微流量ap数量 number(8),

零流量ap数量 number(8),

空流量AP数量 number(8),

闲置系数 number(8),

闲置程度 number(8),

闲置率 NUMBER(20,12),

闲置情况指示 VARCHAR2(50),

闲置情况说明 VARCHAR2(50),

服务天数 number(8),

平均每设备每日服务时长 number(8),

热点可用率 NUMBER(20,12),

热点可用率评价 VARCHAR2(50),

热点可用率预示 number(8),

平均ap网管缺勤天数 NUMBER(20,12),

平均ap出勤率 NUMBER(20,12),

ap出勤率评价 VARCHAR2(50),

ap出勤率预示 NUMBER(20,12),

部署CMCC数量 number(8),

部署AUTO数量 number(8),

公众产品部署率 VARCHAR2(50),

部署EDU数量 VARCHAR2(50),

EDU部署率 NUMBER(20,12),

部属集团产品数量 number(8),

部属家园产品数量 number(8),

估计每日上网人数 NUMBER(20,12),

WLANTYPE VARCHAR2(20)

)

excel数据导入oracle

控制面板-管理工具-数据源(odbc)-增加数据源

plsql tool-odbc导入

create table snipertest as

select x.city_name1 city_name,x.ne_name,to_char(x.FIRST_RESULT,'yyyy-mm') 月份,

round(avg(总码资源利用率),2) 总码资源利用率

from

(SELECT /*+ ordered*/

c.city_name1,

TCC_NE_SNAP.USERLABEL NE_NAME,

TPC_UTRANCELL_NE.FIRST_RESULT FIRST_RESULT,

round(decode(((NVL(TPC_UTRANCELL_NE.BRUUL,0) +NVL(TPC_UTRANCELL_NE.BRUDL,0) )*0.75),0,0,

(NVL(TPC_UTRANCELL_NE.TSNBRASSNBRUUL,0)+NVL(TPC_UTRANCELL_NE.TSNBRASSNBRUDL,0))/

((NVL(TPC_UTRANCELL_NE.BRUUL,0) +NVL(TPC_UTRANCELL_NE.BRUDL,0) )*0.75))*100,2) 总码资源利用率,

c.city_name1||to_char(tpc_utrancell_ne.FIRST_RESULT,'yyyy-mm-dd hh24') a

FROM npmdb.TPC_UTRANCELL_NE@npmdb TPC_UTRANCELL_NE,npmdb.TCC_NE_SNAP@npmdb TCC_NE_SNAP,npmdb.region_city@npmdb c

WHERE (tcc_ne_snap.NE_TYPE = 9300)

AND TCC_NE_SNAP.COMPRESS_DATE=TPC_UTRANCELL_NE.COMPRESS_DATE

AND TCC_NE_SNAP.NE_ID=TPC_UTRANCELL_NE.NE_ID

AND TCC_NE_SNAP.NE_TYPE=TPC_UTRANCELL_NE.NE_TYPE

and TCC_NE_SNAP.City_Id=c.city_id

AND tpc_utrancell_ne.FIRST_RESULT >= to_date('2012-08-01','yyyy-mm-dd')

AND tpc_utrancell_ne.FIRST_RESULT < to_date('2012-08-31','yyyy-mm-dd')+1)X,

(select /*+ ordered*/

from

(select /*+ ordered*/

ne_name,FIRST_RESULT,max(ne_name||gentime) a

from

(SELECT replace(replace(dataapp.SOA_GET_NENAME@rcounttonpm(UTRANCELL.NE_ID, UTRANCELL.NE_TYPE),'辽宁省-',''),'市','') NE_NAME,

to_char(UTRANCELL.FIRST_RESULT,'yyyy-mm-dd hh24') gentime,

to_char(UTRANCELL.FIRST_RESULT,'yyyy-mm-dd') FIRST_RESULT,

round((TSNBRASSNBRUUL_MAX/0.75) / 100,2) 总码资源利用率,

dense_rank () over (partition by to_char(UTRANCELL.FIRST_RESULT,'yyyy-mm-dd'),

dataapp.SOA_GET_NENAME@rcounttonpm(UTRANCELL.NE_ID, UTRANCELL.NE_TYPE)

order by (TSNBRASSNBRUUL_MAX/0.75) / 100 Desc) dr

FROM npmdb.TPA_UTRANCELL_SUM@npmdb UTRANCELL

WHERE utrancell.NE_TYPE =10003

and (1 = 1)

AND utrancell.Sum_Level = 0

AND SV_CAT_ID = -1

AND SV_ID = -1

AND utrancell.FIRST_RESULT >= to_date('2012-08-01','yyyy-mm-dd')

and utrancell.FIRST_RESULT < to_date('2012-08-31','yyyy-mm-dd')+1)

where dr=1

group by ne_name,FIRST_RESULT)

where x.a=y.a

group by x.city_name1 ,x.ne_name,to_char(x.FIRST_RESULT,'yyyy-mm')

异常存储过程创建：

CREATE OR REPLACE PROCEDURE prc_err_log

(

i_procedure_name Varchar2

,i_err_msg Varchar2

)

v_sqlcode Varchar(10);

v_sqlerrm Varchar(1000);

Begin

v_sqlcode:=Sqlcode;

v_sqlerrm:=Sqlerrm;

Insert Into wErrorLog Values(seq_id.nextval,i_procedure_name,i_err_msg,v_sqlcode,v_sqlerrm,Sysdate);

commit;

Exception

WHEN OTHERS

Then

commit;

END;

--是够关联2G

update T_UCELL_CARD_M set 是否关联2G=

(case

when 关联2G小区的流量 is not null and 月份=trunc(add_months(sysdate,-1),'mm') then '是'

when 关联2G小区的流量 is null and 月份=trunc(add_months(sysdate,-1),'mm') then '否'

end)

where 月份=trunc(add_months(sysdate,-1),'mm')

--更新覆盖半径

update T_UCELL_CARD_M set 覆盖半径=

(case

when 是否室内='是' and 月份=trunc(add_months(sysdate,-1),'mm') then 0.05

when 是否室内='否'

and (覆盖区域划分 like '%市辖村%' or 覆盖区域划分 like '%县辖村%')

and 月份=trunc(add_months(sysdate,-1),'mm') then 1

when 是否室内='否'

and (覆盖区域划分 like '%城区%' or 覆盖区域划分 like '%市辖镇%'

or 覆盖区域划分 like '%县城区%' or 覆盖区域划分 like '%县辖镇%')

and 月份=trunc(add_months(sysdate,-1),'mm') then 0.4

end)

where 月份=trunc(add_months(sysdate,-1),'mm')

--打开执行计划

set autotrace traceonly

--执行sql

select count(*) from emp;

/**t_ucell_card_m 通过“网管流量”算“数据热度指数” 逻辑是网管流量〉avg（网管流量）*2，数据热度指数=5

网管流量<avg（网管流量）*2 and 网管流量〉avg（网管流量）/2，数据热度指数=3

网管流量<avg（网管流量）/2，数据热度指数=1

**/

update t_ucell_card_m set 数据热度指数=

(case

when 网管流量>(select avg(网管流量)*2 平均网管流量 from t_ucell_card_m )*2 then 5

when 网管流量<(select avg(网管流量)*2 平均网管流量 from t_ucell_card_m )*2 and 网管流量>(select avg(网管流量)*2 平均网管流量 from t_ucell_card_m )/2 then 3

when 网管流量<(select avg(网管流量)*2 平均网管流量 from t_ucell_card_m )/2 then 1

end)

where to_char(月份,'yyyy-mm')='2012-08'

/**

t_cell_poi_month_relation增加三个字段：站址,小区流量，数据热度指数

u_cell_card_m 中

站址,总流量/1024/当月天数小区流量,数据热度指数

**/

UPDATE T_CELL_POI_MONTH_RELATION T1

SET (T1.站址, T1.小区流量, T1.数据热度指数) =

(SELECT 站址,

总流量 / 1024 /TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,0)), 'dd')) 小区流量,

数据热度指数

FROM T_UCELL_CARD_M M

WHERE M.LAC = T1.LAC AND M.CI = T1.CI )

where to_char(t1.month,'yyyy-mm-dd')='2012-08-01' ;

UPDATE T_CELL_POI_MONTH_RELATION T1

SET (T1.站址, T1.小区流量, T1.数据热度指数) =

(SELECT 站址,

总流量 / 1024 /TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,0)), 'dd')) 小区流量,

数据热度指数

FROM T_UCELL_CARD_M M

WHERE M.LAC = T1.LAC AND M.CI = T1.CI )

where to_char(t1.month,'yyyy-mm-dd')='2012-08-01'

and exists (SELECT 站址,

总流量 / 1024 /TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,0)), 'dd')) 小区流量,

数据热度指数

FROM T_UCELL_CARD_M M

WHERE M.LAC = T1.LAC AND M.CI = T1.CI ) ;

多列變一行

a 表中有三条记录如下：

M N
1 开1
101 开2
10101 开3

如何用一条SQL将a表中的三条数据变成一条插入b表，如下：

M1 N1 M2 N2 M3 N3
1 开1 101 开2 10101 开3

WITH A AS (SELECT 1 M,'開1' N FROM DUAL
UNION
SELECT 101 M,'開2' N FROM DUAL
UNION
SELECT 10101 M,'開3' N FROM DUAL
)
SELECT
MAX(DECODE(C.SEQ, 1, C.M)) AS M1,
MAX(DECODE(C.SEQ, 1, C.N)) AS N1,
MAX(DECODE(C.SEQ, 2, C.M)) AS M2,
MAX(DECODE(C.SEQ, 2, C.N)) AS N2,
MAX(DECODE(C.SEQ, 3, C.M)) AS M3,
MAX(DECODE(C.SEQ, 3, C.N)) AS N3
FROM
(
SELECT A.M,
A.N,
ROW_NUMBER () OVER(PARTITION BY 1 ORDER BY A.M) AS SEQ
FROM A
) C

一列變一行

例2：

如下格式：

Col
aaaa
bbbb
cccc
....
转换后
col
aaaa,bbbb,cccc,....

with a as (select 'aaaa' co from dual
union
select 'bbbb' co from dual
union
select 'cccc' co from dual
)

方法一：
select substr(max(sys_connect_by_path(co,'->')),3) rm
from (select a.*,rownum rn from a)
start with rn=1
connect by rn-1=prior rn

方法二：

create or replace function ff (P_CO VARCHAR2)
return varchar2 is
v_co varchar2(400);
begin
for c1 in (select co from A WHERE CO=P_CO ) LOOP
v_co:=v_co||','||C1.CO;
END LOOP;
RETURN V_CO;
END ;
select MAX(FF(CO) FROM A;

一列變多行

WITH A AS (SELECT 'A' CD FROM DUAL
UNION
SELECT 'B' CD FROM DUAL
UNION
SELECT 'C' CD FROM DUAL
UNION
SELECT 'D' CD FROM DUAL
UNION
SELECT 'E' CD FROM DUAL
UNION
SELECT 'F' CD FROM DUAL
UNION
SELECT 'G' CD FROM DUAL
UNION
SELECT 'H' CD FROM DUAL
UNION
SELECT 'I' CD FROM DUAL
)
select
max(decode(mod(rownum, 5), 1, CD, null)) ID1,
max(decode(mod(rownum, 5), 2, CD, null)) ID2,
max(decode(mod(rownum, 5), 3, CD, null)) ID3,
max(decode(mod(rownum, 5), 4, CD, null)) ID4,
max(decode(mod(rownum, 5), 0, CD, null)) ID5
from a
group by ceil(rownum / 5)
ID1 ID2 ID3 ID4 ID5
--- --- --- --- ---
A B C D E
F G H I

SQL> WITH A AS (SELECT 1 CD1,0 CD2 FROM DUAL
2 UNION
3 SELECT 2 CD1,0 CD2 FROM DUAL
4 UNION
5 SELECT 3 CD1,0 CD2 FROM DUAL
6 UNION
7 SELECT 4 CD1,0 CD2 FROM DUAL
8 UNION
9 SELECT 5 CD1,0 CD2 FROM DUAL
10 )
11 SELECT * FROM (SELECT MAX(DECODE(RN,1,CD1,NULL)) ID1,
12 MAX(DECODE(RN,2,CD1,NULL)) ID2,
13 MAX(DECODE(RN,3,CD1,NULL)) ID3,
14 MAX(DECODE(RN,4,CD1,NULL)) ID4,
15 MAX(DECODE(RN,5,CD1,NULL)) ID5
16 FROM (SELECT CD1,CD2,ROW_NUMBER()OVER( ORDER BY CD1) RN FROM A)
17 UNION
18 SELECT MAX(DECODE(RN,1,CD2,NULL)) ID1,
19 MAX(DECODE(RN,2,CD2,NULL)) ID2,
20 MAX(DECODE(RN,3,CD2,NULL)) ID3,
21 MAX(DECODE(RN,4,CD2,NULL)) ID4,
22 MAX(DECODE(RN,5,CD2,NULL)) ID5
23 FROM (SELECT CD1,CD2,ROW_NUMBER()OVER( ORDER BY CD1) RN FROM A))
24 ORDER BY ID1 DESC
25 ;

ID1 ID2 ID3 ID4 ID5
---------- ---------- ---------- ---------- ----------
1 2 3 4 5

select m7.厂家,

MSC厂家数量,

MSS厂家数量,

MGW厂家数量,

HLR厂家数量,

BSC厂家数量,

RNC厂家数量,

nvl(MSC厂家数量, 0) + nvl(MSS厂家数量, 0) + nvl(MGW厂家数量, 0) +

nvl(HLR厂家数量, 0) + nvl(BSC厂家数量, 0) + nvl(RNC厂家数量, 0) as 总量

from (select distinct (厂家)

from (select vendor as 厂家

from rmw2.rms_msc

where stateflag = 0

group by vendor

union all

select vendor_id as 厂家

from rmw2.rms_mss

where stateflag = 0

group by vendor_id

union all

select vendor_id as 厂家

from rmw2.rms_mgw

where stateflag = 0

group by vendor_id

union all

select vendor_id as 厂家

from rmw2.rms_hlr

where stateflag = 0

group by vendor_id

union all

select vendor_id as 厂家

from rmw2.rms_bsc

where stateflag = 0

group by vendor_id

union all

select vendor_id as 厂家

from rmw2.rms_rnc

where stateflag = 0

group by vendor_id)

where length(厂家) > 1) m7

left join (select a.vendor as 厂家, count(a.vendor) as msc厂家数量

from rmw2.rms_msc a

where a.stateflag = 0

group by a.vendor) m1

on m1.厂家 = m7.厂家

left join (select b.vendor_id as 厂家, count(b.vendor_id) as mss厂家数量

from rmw2.rms_mss b

where b.stateflag = 0

group by b.vendor_id) m2

on m2.厂家 = m7.厂家

left join (select c.vendor_id as 厂家, count(c.vendor_id) as mgw厂家数量

from rmw2.rms_mgw c

where c.stateflag = 0

and c.vendor_id not in ('2')

group by c.vendor_id) m3

on m3.厂家 = m7.厂家

left join (select d.vendor_id as 厂家, count(d.vendor_id) as hlr厂家数量

from rmw2.rms_hlr d

where d.stateflag = 0

group by d.vendor_id) m4

on m4.厂家 = m7.厂家

left join (select e.vendor_id as 厂家, count(e.vendor_id) as bsc厂家数量

from rmw2.rms_bsc e

where e.stateflag = 0

group by e.vendor_id) m5

on m5.厂家 = m7.厂家

left join (select f.vendor_id as 厂家, count(f.vendor_id) as rnc厂家数量

from rmw2.rms_rnc f

where f.stateflag = 0

group by f.vendor_id) m6

on m6.厂家 = m7.厂家

posted @ 2012-10-22 21:36 Mr-sniper 阅读(357) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部