请教一个SQL语句

carefree_fish 2010-12-21 04:36:27

想做一个统计查询，按照日期分组，显示出每天数据出错和设备出错分别有多少个记录数。

数据如下：
RQ BZ
2010-11-24 SJBF
2010-11-24 SJ
2010-11-24 sj
2010-11-24 zx
2010-11-24 ZX
2010-11-25 sj16.17
2010-11-24 sJ14.48起
2010-12-3 wsj 7.47-10.30
2010-12-17 zx到离
2010-12-7 到 ZX
………………

其中BZ里面只要包含SJ的，无论大小写都是数据出错，BZ里面包含ZX，无论大小写都是设备出错。
想要一个这样的表
RQ BZ count
2010-11-24 数据出错 5
2010-11-24 设备出错 2
2010-11-25 数据出错 13
2010-11-25 设备出错 6
……………………

...全文

128 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

carefree_fish 2010-12-22

打赏
举报

[Quote=引用 3 楼 gelyon 的回复:]
SQL code

SELECT rq,'数据出错' bz,Count(*) cnt FROM tab WHERE regexp_like(bz,'sj','i') GROUP BY rq
UNION
SELECT rq,'设备出错' bz,Count(*) cnt FROM tab WHERE regexp_like(bz,'zx','i') GROUP BY rq
[/Quote]

执行SELECT rq,'数据出错' bz,Count(*) cnt FROM tab WHERE regexp_like(bz,'sj','i') GROUP BY rq
，报出错，无效的关系运算符

cuidch2010 2010-12-21

打赏
举报

谢谢分享

xiaohu1983000 2010-12-21

打赏
举报

还是这个通用：
SELECT rq,'数据出错' bz,Count(*) cnt FROM tab WHERE regexp_like(bz,'sj','i') GROUP BY rq
UNION
SELECT rq,'设备出错' bz,Count(*) cnt FROM tab WHERE regexp_like(bz,'zx','i') GROUP BY rq

心中的彩虹 2010-12-21

打赏
举报

[Quote=引用 6 楼 wkc168 的回复:]
引用 5 楼 wkc168 的回复:
引用楼主 carefree_fish 的回复:
想做一个统计查询，按照日期分组，显示出每天数据出错和设备出错分别有多少个记录数。

数据如下：
RQ BZ
2010-11-24 SJBF
2010-11-24 SJ
2010-11-24 sj
2010-11-24 zx
2010-11-24 ZX
2010-11-25 sj16.17
……
[/Quote]



--这个正确

select rq,

decode(sign(regexp_instr(bz,'sj|sJ|Sj|SJ'))||sign(regexp_instr(bz,'zx|ZX|Zx|zX')),'10','数据出错','01','设备出错','其他') bz,

count(*)

from tb

group by rq,decode(sign(regexp_instr(bz,'sj|sJ|Sj|SJ'))||sign(regexp_instr(bz,'zx|ZX|Zx|zX')),'10','数据出错','01','设备出错','其他')

order by rq



SQL> with tb as(

  2  select date '2010-11-24' rq, 'SJBF' bz from dual union all

  3  select date '2010-11-24' rq, 'SJ' bz from dual union all

  4  select date '2010-11-24' rq, 'sj' bz from dual union all

  5  select date '2010-11-24' rq, 'zx' bz from dual union all

  6  select date '2010-11-24' rq, 'ZX' bz from dual union all

  7  select date '2010-11-25' rq, 'sj16.17' bz from dual union all

  8  select date '2010-11-24' rq, 'sJ14.48起' bz from dual union all

  9  select date '2010-12-3' rq, 'wsj 7.47-10.30' bz from dual union all

 10  select date '2010-12-17' rq, 'zx到 离' bz from dual union all

 11  select date '2010-12-7' rq, '到 ZX' bz from dual)

 12  select rq,

 13  decode(sign(regexp_instr(bz,'sj|sJ|Sj|SJ'))||sign(regexp_instr(bz,'zx|ZX|Zx|zX')),'10','数据出错','01','设备出错','其他') bz,

 14  count(*)

 15  from tb

 16  group by rq,decode(sign(regexp_instr(bz,'sj|sJ|Sj|SJ'))||sign(regexp_instr(bz,'zx|ZX|Zx|zX')),'10','数据出错','01','设备出错','其他')

 17  order by rq

 18  /

 

RQ          BZ         COUNT(*)

----------- -------- ----------

2010-11-24  设备出错           2

2010-11-24  数据出错           4

2010-11-25  数据出错           1

2010-12-3   数据出错           1

2010-12-7   设备出错           1

2010-12-17  设备出错           1

 

6 rows selected

心中的彩虹 2010-12-21

打赏
举报

[Quote=引用 5 楼 wkc168 的回复:]
引用楼主 carefree_fish 的回复:
想做一个统计查询，按照日期分组，显示出每天数据出错和设备出错分别有多少个记录数。

数据如下：
RQ BZ
2010-11-24 SJBF
2010-11-24 SJ
2010-11-24 sj
2010-11-24 zx
2010-11-24 ZX
2010-11-25 sj16.17
2010-11-24 sJ14.48起
……
[/Quote]









SQL> 

SQL> with tb as(

  2  select date '2010-11-24' rq, 'SJBF' bz from dual union all

  3  select date '2010-11-24' rq, 'SJ' bz from dual union all

  4  select date '2010-11-24' rq, 'sj' bz from dual union all

  5  select date '2010-11-24' rq, 'zx' bz from dual union all

  6  select date '2010-11-24' rq, 'ZX' bz from dual union all

  7  select date '2010-11-25' rq, 'sj16.17' bz from dual union all

  8  select date '2010-11-24' rq, 'sJ14.48起' bz from dual union all

  9  select date '2010-12-3' rq, 'wsj 7.47-10.30' bz from dual union all

 10  select date '2010-12-17' rq, 'zx到 离' bz from dual union all

 11  select date '2010-12-7' rq, '到 ZX' bz from dual)

 12  select rq,

 13  decode(sign(regexp_instr(bz,'sj|SJ'))||sign(regexp_instr(bz,'zx|ZX')),'10','数据出错','01','设备出错','其他') bz,

 14  count(*)

 15  from tb

 16  group by rq,decode(sign(regexp_instr(bz,'sj|SJ'))||sign(regexp_instr(bz,'zx|ZX')),'10','数据出错','01','设备出错','其他')

 17  /

 

RQ          BZ         COUNT(*)

----------- -------- ----------

2010-12-7   设备出错           1

2010-11-24  设备出错           2

2010-11-24  其他              1

2010-12-3   数据出错           1

2010-12-17  设备出错           1

2010-11-24  数据出错           3

2010-11-25  数据出错           1

 

7 rows selected

心中的彩虹 2010-12-21

打赏
举报

[Quote=引用楼主 carefree_fish 的回复:]
想做一个统计查询，按照日期分组，显示出每天数据出错和设备出错分别有多少个记录数。

数据如下：
RQ BZ
2010-11-24 SJBF
2010-11-24 SJ
2010-11-24 sj
2010-11-24 zx
2010-11-24 ZX
2010-11-25 sj16.17
2010-11-24 sJ14.48起
2010-12-3 wsj 7.47-10.30……
[/Quote]





select rq,

decode(sign(regexp_instr(bz,'sj|SJ'))||sign(regexp_instr(bz,'zx|ZX')),'10','数据出错','01','设备出错','其他') bz,

count(*)

from tb

group by rq,decode(sign(regexp_instr(bz,'sj|SJ'))||sign(regexp_instr(bz,'zx|ZX')),'10','数据出错','01','设备出错','其他')

真的不知道取什么名 2010-12-21

打赏
举报



with tb as(

select to_date('2010-11-24','yyyy-mm-dd') RQ,'SJBF' BZ from dual union all

select to_date('2010-11-24','yyyy-mm-dd'),'SJ' from dual union all

select to_date('2010-11-24','yyyy-mm-dd'),'sj' from dual union all

select to_date('2010-11-24','yyyy-mm-dd'),'Sj' from dual union all

select to_date('2010-11-24','yyyy-mm-dd'),'zx' from dual union all

select to_date('2010-11-24','yyyy-mm-dd'),'ZX' from dual union all

select to_date('2010-11-25','yyyy-mm-dd'),'sf16.17' from dual union all

select to_date('2010-11-24','yyyy-mm-dd'),'sJ14.48起' from dual union all

select to_date('2010-11-03','yyyy-mm-dd'),'wsj 7.47-10.30' from dual union all

select to_date('2010-12-17','yyyy-mm-dd'),'zx到 离' from dual union all

select to_date('2010-12-07','yyyy-mm-dd'),'到 ZX' from dual)

--以上为提供数据的语句

select rq,(case when instr(upper(bz),'SJ')>0 then '数据出错'

when instr(upper(bz),'ZX')>0 then '设备出错' else null end),

count(*) cnt

from tb

group by rq,(case when instr(upper(bz),'SJ')>0 then '数据出错'

when instr(upper(bz),'ZX')>0 then '设备出错' else null end)





RQ                  (CASEWHE        CNT

------------------- -------- ----------

2010-12-07 00:00:00 设备出错          1

2010-11-24 00:00:00 设备出错          2

2010-11-25 00:00:00                 1

2010-12-17 00:00:00 设备出错          1

2010-11-24 00:00:00 数据出错          5

2010-11-03 00:00:00 数据出错          1



已选择6行。

gelyon 2010-12-21

打赏
举报



SELECT rq,'数据出错' bz,Count(*) cnt FROM tab WHERE regexp_like(bz,'sj','i') GROUP BY rq

UNION 

SELECT rq,'设备出错' bz,Count(*) cnt FROM tab WHERE regexp_like(bz,'zx','i') GROUP BY rq

Oraclefans_ 2010-12-21

打赏
举报



-------简化下

SQL> with tablea as(

  2  select date '2010-11-24' rq, 'SJBF' bz from dual union all

  3  select date '2010-11-24' rq, 'SJ' bz from dual union all

  4  select date '2010-11-24' rq, 'sj' bz from dual union all

  5  select date '2010-11-24' rq, 'zx' bz from dual union all

  6  select date '2010-11-24' rq, 'ZX' bz from dual union all

  7  select date '2010-11-25' rq, 'sj16.17' bz from dual union all

  8  select date '2010-11-24' rq, 'sJ14.48起' bz from dual union all

  9  select date '2010-12-3' rq, 'wsj 7.47-10.30' bz from dual union all

 10  select date '2010-12-17' rq, 'zx到 离' bz from dual union all

 11  select date '2010-12-7' rq, '到 ZX' bz from dual

 12  )

 13  select rq, t, count(*)

 14    from (select rq,

 15                 case

 16                   when instr(upper(bz), 'SJ') > 0 then

 17                    '数据出错'

 18                   when instr(upper(bz), 'ZX') > 0 then

 19                    '设备出错'

 20                   else

 21                    '正常'

 22                 end t

 23            from tablea)

 24   group by rq, t

 25  /



RQ          T              COUNT(*)

----------- ------------ ----------

2010-11-25  数据出错              1

2010-12-17  设备出错              1

2010-11-24  数据出错              4

2010-11-24  设备出错              2

2010-12-3   数据出错              1

2010-12-7   设备出错              1



6 rows selected



SQL>

Oraclefans_ 2010-12-21

打赏
举报





SQL> 

SQL> with tablea as(

  2  select date '2010-11-24' rq, 'SJBF' bz from dual union all

  3  select date '2010-11-24' rq, 'SJ' bz from dual union all

  4  select date '2010-11-24' rq, 'sj' bz from dual union all

  5  select date '2010-11-24' rq, 'zx' bz from dual union all

  6  select date '2010-11-24' rq, 'ZX' bz from dual union all

  7  select date '2010-11-25' rq, 'sj16.17' bz from dual union all

  8  select date '2010-11-24' rq, 'sJ14.48起' bz from dual union all

  9  select date '2010-12-3' rq, 'wsj 7.47-10.30' bz from dual union all

 10  select date '2010-12-17' rq, 'zx到 离' bz from dual union all

 11  select date '2010-12-7' rq, '到 ZX' bz from dual

 12  )

 13  select rq,

 14         case

 15           when t = 1 then

 16            '数据出错'

 17           when t = 2 then

 18            '设备出错'

 19           else

 20            '正常'

 21         end,

 22         count(*)

 23    from (select rq,

 24                 case

 25                   when instr(upper(bz), 'SJ') > 0 then

 26                    1

 27                   when instr(upper(bz), 'ZX') > 0 then

 28                    2

 29                   else

 30                    0

 31                 end t

 32            from tablea)

 33   group by rq,

 34            case

 35              when t = 1 then

 36               '数据出错'

 37              when t = 2 then

 38               '设备出错'

 39              else

 40               '正常'

 41            end

 42  /



RQ          CASEWHENT=1THEN'数据出错'WHENT   COUNT(*)

----------- ------------------------------ ----------

2010-11-25  数据出错                                1

2010-12-17  设备出错                                1

2010-11-24  数据出错                                4

2010-11-24  设备出错                                2

2010-12-3   数据出错                                1

2010-12-7   设备出错                                1



6 rows selected



SQL>