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

 

idcityusernamebirthday

1beijingaaa1981/11/05

2beijingbbb1985/04/06

3shanghaiccc1988/08/07

4xianddd1986/05/09

5shanghaieee1991/04/07

6beijingfff1970/08/06

7beijingggg1975/08/02

8xianhhh1969/08/03

9shanghaiiii1968/05/07

10xianjjj1959/08/04

 

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

select city,count(city) from T_playes

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

 group by city

having count(city)>1