×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

最后我是这么写的

表Requests:
Request Status
0000001 O
0000002 F
0000003 F
0000004 O
0000005 O

要求查询出以下结果:
Num of Requests, Open, Finished
5, 3, 2

用Oracle:
select
count(*) as 'Number of Requests',
sum(decode(status, 'O', 1, 0)) as Open,
sum(decode(status, 'F', 1, 0)) as Finished
from
Requests

用MySQL:
select
count(*) as 'Number of Requests',
sum(case status when "O" then 1 else 0 END) as Open,
sum(case status when "F" then 1 else 0 END) as Finished
from
Requests

再次感谢小旋子,飞鹰战士,Sunshine,卷云溶月和长得象熊的狐狸。排名不分先后,欢迎下次继续捧场。
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / 这个SQL怎么写?
    简单的说,有这么个表:

    Request Status
    0000001 0
    0000002 1
    0000003 1
    0000004 0
    0000005 0

    要求查询出以下结果:
    Num of Requests, Open, Finished
    5, 3, 2

    这个SQL应该怎么写?分别在Oracle和MySQL下的?应该是一样的?

    谢。
    • 逻辑上好象不同吧!
    • 能一句能写出来吗?
      • 上面有个字写错了,应该是“逻辑上不通吧”。你所举的例子,我还看不出所以然来。能再说明一下吗?
        • 恩,是这样,5个Request,状态0表示open,状态1表示finished,然后。。。
      • select count(*) as 'Num of Requests', (select count(*) from Request Status where Status =0) 'open', (select count(*) from Request Status where Status =1) 'Finished' from Request Status
        • 恩,原来select可以这样嵌套,我这就去试试。先谢了!
        • 报告,MySQL不通,有没有其它的办法?
          • 卷云溶月的主意非常好....不过得找一下'decode' 在MySQL下的替换...
          • select count(request), count(request)-sum(status), sum(status) from (select request, case when status='O' then 0 when status='F' then 1 end as status from tbl) as a ........
            这是SQL Server中的语法,不过我想MySQL中应该有相应的case语句。
            • 感谢,下午再试再报。
              • 我在access中试了,下面的sql可以达到目的。
                SELECT Count(Table1.request) AS eOfCount, (select Count(Table1.request) from table1 where table1.status="o") AS statuso,(select Count(Table1.request) from table1 where table1.status="f") AS statusf FROM Table1;
    • count(request), count(request)-sum(status), sum(status)
      • 惭愧,没能帮上忙,还是Sunshine高!
        • 不高,1.645m.
        • 那也得谢谢您捧场牙
      • 你简直是高手高手杲杲手!可事实上我的Status不是0和1,而是'O'和'F',那可怎么办呢?
        • 我已经给你答案了...不过Sunshine确实很有些小聪明,根据0,1玩数字游戏.PFPF...
          • 口黑口黑,所以我说她是杲杲手。
            • 我当然知道可以嵌套,不过我贼懒而已。
          • 不过只是0或1的情况下,Sunshine的速度确实比飞鹰战士快一些。
            • 确实如此...
        • In Oracle, you can use 'decode' to convert 'O'/'F' to 0/1. I don't know mysql.
          • lilyba's idea is very good.
            • 小丫头真聪明
              • 谢谢夸奖。尽管已经习惯了,听着还是那么中听。
    • Oracle下可以用Decode,据我所知MySql下没有
      如果用decode,应该类似:
      Select sum(decode(status,0,1,1,1,0)) as 'Number of Requests',
      Sum(decode(status,0,1,0)) as Open,
      sum(decode(status,1,1,0)) as Finished
      From TableName
    • 最后我是这么写的
      表Requests:
      Request Status
      0000001 O
      0000002 F
      0000003 F
      0000004 O
      0000005 O

      要求查询出以下结果:
      Num of Requests, Open, Finished
      5, 3, 2

      用Oracle:
      select
      count(*) as 'Number of Requests',
      sum(decode(status, 'O', 1, 0)) as Open,
      sum(decode(status, 'F', 1, 0)) as Finished
      from
      Requests

      用MySQL:
      select
      count(*) as 'Number of Requests',
      sum(case status when "O" then 1 else 0 END) as Open,
      sum(case status when "F" then 1 else 0 END) as Finished
      from
      Requests

      再次感谢小旋子,飞鹰战士,Sunshine,卷云溶月和长得象熊的狐狸。排名不分先后,欢迎下次继续捧场。