SQL的分组问题(100分)

J

jiangxiancheng

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-22

• #1

我用的是MSSQL 2000，一个表中的一列可能取值为0, 1, 2
我现在要一条语句，统计为一条记录。
如数据为
0
1
0
1
1
2
统计结果为
为0的值有 为1的值有 为2的值有
2 3 1
这条语句如何写。请教，先谢过了。

 

T

TMXYB

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-22

• #2

select xxx,count(*) as the_count from the_db
group by xxx

结果：
xxx the_count
0 2
1 3
2 1

传输结果时，传输fieldbyname('the_count').asinteger 即可．

 

J

jiangxiancheng

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-22

• #3

上面误会了。我想做成一条记录。我用在一个分组查询中。

 

A

Admy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-22

• #4

你的结果表结构是不固定的，仅用SQL查询语句恐怕做不出来

 

D

DawnWoo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-22

• #5

CREATE TABLE DataCount (Cnt_0 AS INTEGER,Cnt_1 AS INTEGER,Cnt_2 AS INTEGER)
FOR x=1 TO 3
INSERT INTO DataCount (SELECT COUNT(Cnt_x) FROM DataCount WHERE Cnt_x=x)
END
----------
是不是这个意思？

 

J

jiangxiancheng

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-22

• #6

to Admy
可能取值只有0, 1, 2
to DawnWoo
希望用一句SQL语句就搞定。

 

U

ugvanxk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-23

• #7

select sum(case when field=0 then 1 else 0 end) as 为0 的，
sum(case when field=1 then 1 else 0 end) as 为1的，
sum(case when field=2 then 1 else 0 end) as 为2 的
from table

 

Q

QuickSilver

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-23

• #8

Select (select count(*) from table1 where num=0) as A,
(select count(*) from table1 where num=1) as B,
(select count(*) from table1 where num=2) as C

 

D

dluming

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-23

• #9

楼上的完全正确。这种方式可以用来统计各部门的人数多少，其实就是交叉表的问题。

 

D

DawnWoo

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-24

• #10

to jiangxiancheng: 我改了一下,看是否符合你的要求?

//这个是不要先建表?
//CREATE TABLE DataCount (Cnt_0 AS INTEGER,Cnt_1 AS INTEGER,Cnt_2 AS INTEGER)

INSERT INTO DataCount
(Cnt_0,Cnt_1,Cnt_2)
VALUES (SELECT COUNT(Cnt_0) FROM DataCount WHERE Cnt_0=0,
SELECT COUNT(Cnt_0) FROM DataCount WHERE Cnt_0=0,
SELECT COUNT(Cnt_0) FROM DataCount WHERE Cnt_0=0)
----------

 

J

jiangxiancheng

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-10-24

• #11

接受答案了.