help needed - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

help needed

help needed# Database - 数据库

s*n2004-02-19 08:02

1 楼

I got a sql problem:
here is a database table ------
id(PK) name
1 mark
2 mike
3 mary
4 mike
5 mary
how can I select then name "mary" and "mike" out because they appear twice?
Thanks a lot!

j*n2004-02-19 08:02

2 楼

the table have different id, can be restrict by a "where id = xxxx"
eg.
select * from table_name where id=2 // the first mike
select * from table_name where id=4 // second mike

【在 s*********n 的大作中提到】

: I got a sql problem:
: here is a database table ------
: id(PK) name
: 1 mark
: 2 mike
: 3 mary
: 4 mike
: 5 mary
: how can I select then name "mary" and "mike" out because they appear twice?
: Thanks a lot!

n*a2004-02-19 08:02

3 楼

If you want any name that appears more than once,
select name from table_name group by name having count(*) > 1
select name from table_name group by name having count(distinct id) > 1
If you want name that appears exactly twice
select name from table_name group by name having count(*) = 2
select name from table_name group by name having count(distinct id) = 2

【在 s*********n 的大作中提到】

s*n2004-02-19 08:02

4 楼

this is exactly what I want. thanks.

twice?

【在 n********a 的大作中提到】

: If you want any name that appears more than once,
: select name from table_name group by name having count(*) > 1
: select name from table_name group by name having count(distinct id) > 1
: If you want name that appears exactly twice
: select name from table_name group by name having count(*) = 2
: select name from table_name group by name having count(distinct id) = 2