This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

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

Ad by

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

最后我是这么写的

xanada(㊣流水)

表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，卷云溶月和长得象熊的狐狸。排名不分先后，欢迎下次继续捧场。

(#569158@0)
Last Updated: 2002-6-10
This post has been archived. It cannot be replied.

Report

Replies, comments and Discussions:

工作学习 / IT技术讨论 / 这个SQL怎么写？ -xanada(㊣流水); 2002-6-10 {253} (#568909@0)
简单的说，有这么个表：

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

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

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

谢。

逻辑上好象不同吧！ -dreamree(小旋子); 2002-6-10 (#568912@0)
能一句能写出来吗？ -xanada(㊣流水); 2002-6-10 (#568914@0)

上面有个字写错了，应该是“逻辑上不通吧”。你所举的例子，我还看不出所以然来。能再说明一下吗？ -dreamree(小旋子); 2002-6-10 (#568917@0)

恩，是这样，5个Request，状态0表示open，状态1表示finished，然后。。。 -xanada(㊣流水); 2002-6-10 (#568928@0)

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 -lazycod(飞鹰战士); 2002-6-10 (#568930@0)

恩，原来select可以这样嵌套，我这就去试试。先谢了！ -xanada(㊣流水); 2002-6-10 (#568935@0)
报告，MySQL不通，有没有其它的办法？ -xanada(㊣流水); 2002-6-10 (#568952@0)

卷云溶月的主意非常好....不过得找一下'decode' 在MySQL下的替换... -lazycod(飞鹰战士); 2002-6-10 (#568957@0)
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 ........ -dreamree(小旋子); 2002-6-10 {61} (#568964@0)
这是SQL Server中的语法，不过我想MySQL中应该有相应的case语句。

感谢，下午再试再报。 -xanada(㊣流水); 2002-6-10 (#568966@0)

我在access中试了，下面的sql可以达到目的。 -lilyba(Sunshine); 2002-6-10 {214} (#568972@0)
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) -lilyba(Sunshine); 2002-6-10 (#568919@0)

惭愧，没能帮上忙，还是Sunshine高！ -dreamree(小旋子); 2002-6-10 (#568927@0)

不高，1.645m. -lilyba(Sunshine); 2002-6-10 (#568932@0)
那也得谢谢您捧场牙 -xanada(㊣流水); 2002-6-10 (#568945@0)

你简直是高手高手杲杲手！可事实上我的Status不是0和1，而是'O'和'F'，那可怎么办呢？ -xanada(㊣流水); 2002-6-10 (#568934@0)

我已经给你答案了...不过Sunshine确实很有些小聪明,根据0,1玩数字游戏.PFPF... -lazycod(飞鹰战士); 2002-6-10 (#568938@0)

口黑口黑，所以我说她是杲杲手。 -xanada(㊣流水); 2002-6-10 (#568941@0)

我当然知道可以嵌套，不过我贼懒而已。 -lilyba(Sunshine); 2002-6-10 (#568956@0)

不过只是0或1的情况下，Sunshine的速度确实比飞鹰战士快一些。 -dreamree(小旋子); 2002-6-10 (#568943@0)

确实如此... -lazycod(飞鹰战士); 2002-6-10 (#568954@0)

In Oracle, you can use 'decode' to convert 'O'/'F' to 0/1. I don't know mysql. -cloud2001(卷云溶月); 2002-6-10 (#568947@0)

lilyba's idea is very good. -cloud2001(卷云溶月); 2002-6-10 (#568955@0)

小丫头真聪明 -xanada(㊣流水); 2002-6-10 (#568959@0)

谢谢夸奖。尽管已经习惯了，听着还是那么中听。 -lilyba(Sunshine); 2002-6-10 (#568961@0)

Oracle下可以用Decode，据我所知MySql下没有 -hugefox(长得象熊的狐狸); 2002-6-10 {215} (#568969@0)
如果用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
最后我是这么写的 -xanada(㊣流水); 2002-6-10 {622} (#569158@0)
表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，卷云溶月和长得象熊的狐狸。排名不分先后，欢迎下次继续捧场。

@Las Vegas

最后我是这么写的

Replies, comments and Discussions:

More Topics