sql例子

1：

CREATE TABLE [T_Playes] (

[id] [int] IDENTITY (1, 1) NOT NULL ,

[city] [varchar] (20) COLLATE Japanese_CI_AS NULL ,

[username] [varchar] (20) COLLATE Japanese_CI_AS NULL ,

[birthday] [datetime] NULL ,

CONSTRAINT [PK_T_Playes] PRIMARY KEY  CLUSTERED 

(

[id]

)  ON [PRIMARY] 

) ON [PRIMARY]

GO

 

id city username birthday

1 beijing aaa 1981/11/05

2 beijing bbb 1985/04/06

3 shanghai ccc 1988/08/07

4 xian ddd 1986/05/09

5 shanghai eee 1991/04/07

6 beijing fff 1970/08/06

7 beijing ggg 1975/08/02

8 xian hhh 1969/08/03

9 shanghai iii 1968/05/07

10 xian jjj 1959/08/04

 

/*年龄大于25岁，且一个城市多余2个以上球员的城市名和球员数量*/

select city,count(city) from T_playes

where( year(getdate())-year(birthday))>25

  group by city

having count(city)>1