filter的几种用法.docx
- 文档编号:18423260
- 上传时间:2023-08-16
- 格式:DOCX
- 页数:17
- 大小:73.22KB
filter的几种用法.docx
《filter的几种用法.docx》由会员分享,可在线阅读,更多相关《filter的几种用法.docx(17页珍藏版)》请在冰点文库上搜索。
filter的几种用法
列1日期的显示
程序(绑定手机日分析)
[CDATA[
SELECT
COUNT(DISTINCT(A.PHONE_NO))CNT,
SUBSTR(A.CREATE_DATE,0,10)YYYYMMDD
FROM
CAS_CUSTOMERA,
CAS_CUSTOMER_ITEMB,
CAS_CHANNELC,
CAS_DISTRICTD,
CAS_USERE,
CAS_EMPLOYEEF
WHERE
B.CUSTOMER_ID=A.ID
ANDA.CREATER_ID=E.ID
ANDE.ID=F.USER_ID
ANDF.CHANNEL_ID=C.ID
ANDC.DISTRICT_ID=D.ID
ANDB.INUSE=1
ANDSUBSTR(A.PHONE_NO,0,3)IN('134','135','136','137','138','139','150','151','152','157','158','159','188','187')
ANDTO_CHAR(A.CREATE_DATE,'YYYYMM')='{YEAR_MONTH}'
GROUPBY
SUBSTR(A.CREATE_DATE,0,10)
]]>
列2日期从xxxxx到xxxxx
程序(水电气酬金明细(月份))
[CDATA[
SELECTWCOUNT,ECOUNT,GCOUNT,ICOUNT,IMONEY,MONEY,WMONEY+EMONEY+GMONEY+IMONEYMONEY1,CARD_WATER+CARD_ELECTRICITY+CARD_GASCOUNT_ZONGFROM(
SELECT
(SELECTCOUNT(SUM_MONEY)FROMCAS_WATER_TRANSWHERESTATE='1'ANDTO_CHAR(STATE_TIME,'YYYYMMDD')BETWEEN'{BEGIN_TIME}'AND'{END_TIME}')WCOUNT,
(SELECTSUM(SUM_MONEY)FROMCAS_WATER_TRANSWHERESTATE='1'ANDTO_CHAR(STATE_TIME,'YYYYMMDD')BETWEEN'{BEGIN_TIME}'AND'{END_TIME}')WMONEY,
(SELECTCOUNT(SUM_MONEY)FROMCAS_ELECTRICITY_DEALWHERESTATE='1'ANDTO_CHAR(OP_TIME,'YYYYMMDD')BETWEEN'{BEGIN_TIME}'AND'{END_TIME}')ECOUNT,
(SELECTSUM(SUM_MONEY)FROMCAS_ELECTRICITY_DEALWHERESTATE='1'ANDTO_CHAR(OP_TIME,'YYYYMMDD')BETWEEN'{BEGIN_TIME}'AND'{END_TIME}')EMONEY,
(SELECTCOUNT(SUM_MONEY)FROMCAS_GAS_TRANSWHERESTATE='1'ANDTO_CHAR(STATE_TIME,'YYYYMMDD')BETWEEN'{BEGIN_TIME}'AND'{END_TIME}')GCOUNT,
(SELECTSUM(SUM_MONEY)FROMCAS_GAS_TRANSWHERESTATE='1'ANDTO_CHAR(STATE_TIME,'YYYYMMDD')BETWEEN'{BEGIN_TIME}'AND'{END_TIME}')GMONEY,
(SELECTCOUNT(BUY_MONEY)FROMCAS_SMART_CARD_TRANSWHERESTATE='1'ANDTO_CHAR(BUY_TIME,'YYYYMMDD')BETWEEN'{BEGIN_TIME}'AND'{END_TIME}')ICOUNT,
(SELECTSUM(BUY_MONEY)FROMCAS_SMART_CARD_TRANSWHERESTATE='1'ANDTO_CHAR(BUY_TIME,'YYYYMMDD')BETWEEN'{BEGIN_TIME}'AND'{END_TIME}')IMONEY,
(SELECTSUM(BUY_MONEY)*0.004FROMCAS_SMART_CARD_TRANSWHERESTATE='1'ANDTO_CHAR(BUY_TIME,'YYYYMMDD')BETWEEN'{BEGIN_TIME}'AND'{END_TIME}')MONEY,
(SELECTCOUNT(DISTINCT(CARD_ID))FROMCAS_WATER_TRANSWHERESTATE='1'ANDTO_CHAR(STATE_TIME,'YYYYMMDD')BETWEEN'{BEGIN_TIME}'AND'{END_TIME}')CARD_WATER,
(SELECTCOUNT(DISTINCT(CARD_ID))FROMCAS_ELECTRICITY_DEALWHERESTATE='1'ANDTO_CHAR(OP_TIME,'YYYYMMDD')BETWEEN'{BEGIN_TIME}'AND'{END_TIME}')CARD_ELECTRICITY,
(SELECTCOUNT(DISTINCT(CARD_ID))FROMCAS_GAS_TRANSWHERESTATE='1'ANDTO_CHAR(STATE_TIME,'YYYYMMDD')BETWEEN'{BEGIN_TIME}'AND'{END_TIME}')CARD_GAS
FROMDUAL)
]]>
列3下拉式查询显示(存在共有表格情况)
程序(练习,自制渠道显示)
[CDATA[
SELECT
A.NAME区县,
B.NAME渠道,
C.NAME店员,
C.LOGIN_NAME工号,
D.BANK_NAME开户名,
D.BANK_ACCOUNT账号
FROM
CAS.CAS_DISTRICTA,
CAS.CAS_CHANNELB,
CAS.CAS_USERC,
CAS.CAS_EMPLOYEED
WHERE
D.USER_ID=C.IDANDD.CHANNEL_ID=B.IDANDB.DISTRICT_ID=A.IDANDB.STATE='1']]>
列4下拉式查找显示(不存在共有表格情况)
[CDATA[
SELECT'1.统计口径'NAME,'0'VALUEFROMDUALUNIONALL
SELECT'2.页面计算','1'FROMDUALUNIONALL
SELECT'3.环境配置','2'FROMDUALUNIONALL
SELECT'4.同步数据','3'FROMDUAL
]]>
列5填表式查询
程序(登录明细)
[CDATA[
SELECT
A.LOGIN_TIMELOGIN_TIME,
A.LOGIN_HOSTLOGIN_HOST,
B.NAMENAME,
C.NAMEDISTRICT_NAME,
C.IDDISTRICT_ID
FROMCAS_LOGIN_HISTORYA,
CAS_USERB,
(
SELECTID,NAME
FROMCAS_DISTRICT
STARTWITHID='{CURR_DISTRICT_ID}'
CONNECTBYPRIORID=PARENT_ID
)C
WHEREB.ID=A.USER_ID
ANDB.DISTRICT_ID=C.ID
ANDTO_CHAR(A.LOGIN_TIME,'YYYYMMDD')BETWEEN'{START_DATE}'AND'{END_DATE}'
]]>
列6多选式查询
程序(用户管理)
[CDATA[
SELECTA.ID,A.NAME,A.LOGIN_NAME,
DECODE(A.IS_LOCKED,1,'锁定','正常')LOCK_STATUS,
A.LOGIN_COUNT,
A.LAST_LOGIN_TIME,
Trunc(A.LAST_LOGIN_TIME,'dd')LOGIN_TIME,
A.LAST_ACTIVE_TIME,
A.DISTRICT_ID,
B.NAMEDISTRICT_NAME
FROMCAS_USERA,
(
SELECTID,NAME
FROMCAS_DISTRICT
STARTWITHID='{CURR_DISTRICT_ID}'
CONNECTBYPRIORID=PARENT_ID
)B
WHEREA.DISTRICT_ID=B.ID
]]>
yyyy-MM-ddhh:
mm}
yyyy-MM-dd}
yyyy-MM-ddhh:
mm}
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- filter 用法