MSSQL?fbirthday是DATETIME 型?select fempname
from t_emp
where fbirthday between cast(cast(year(fbirthday) as varchar(4))+'-09-01' as datetime)
and cast(cast(year(fbirthday) as varchar(4))+'-12-15' as datetime)
from t_emp
where fbirthday between cast(cast(year(fbirthday) as varchar(4))+'-09-01' as datetime)
and cast(cast(year(fbirthday) as varchar(4))+'-12-15' as datetime)
select * from TableName where (datepart(month,BIRTHDAY)>9 and datepart(month,BIRTHDAY)<12)
or (datepart(month,BIRTHDAY)=12 and datepart(day,BIRTHDAY)<=15)别的数据库也有类似写法,以后别把这个当难题!
这些人
然后是列表:和我一样大的人数: 生日
1 1921-10-8
1 1921-10-8
0 1978-10-2
1 1978-10-5
1 1978-10-5
0 1981-10-5
这是一个CRM中的客户关系模块,我怎么用一个语句实现不了呢?
和我一样大的人数 生日 比我大的人数
各位帮忙了,分数绝对不会少的了,我还有统计查询分析都没有做呀,没有办法了,各位GG跟JJ多帮帮忙呀
select *
from TableName
where to_char(BIRTHDAY,'mm-dd') between '09-01' and '12-15'
select count(*) from [table] where substring(convert(varchar(32), birthday, 102), 6, 5) between '09.01' and '12-15' and substring(convert(varchar(32), birthday, 102), 1, 4) < '1971'
多加一个where条件就是了.
假设客户数据为
客户ID 客户生日
1 1975-9-1
2 1976-8-27
3 1981-10-9
4 1975-9-3
5 1977-9-20
6 1981-10-9
7 1981-10-10
即要求的打印列表为
客户ID 客户出生日期 与之年龄相同的客户人数 比其年龄大的客户人数
1 1975-9-1 0 0
4 1975-9-3 0 1
5 1977-9-20 0 2
3 1981-10-9 1 3
6 1981-10-9 1 3
7 1981-10-10 0 5
客户2 1976-8-27由于不满足9-1--12-15的条件所以不能在列表中请问用一条语句如何实现这个列表,还有一个半小时要下班了,完不成的话明天要来加班,可是明天我有课要上呀!惨呀惨
(select count(*)
from customer
where birthday = x.birthday) countOfSameBirthdy,
(select count(*)
from customer
where birthday > x.birthday) countOfOlder
from customer x
where to_char(birthday,'MM-DD') between '09-01' and '12-15'
(select count(*)
from 客户数据
where 客户id<>a.客户Id
and 客户生日=a.客户生日) as 与之年龄相同的客户人数,
(select count(*)
from 客户数据
where ((datepart(month,客户生日)>9 and datepart(month,客户生日)<12)
or (datepart(month,客户生日)=12 and datepart(day,客户生日)<=15))
and 客户生日<a.客户生日
) as 比其年龄大的客户人数
from 客户数据 a
where (datepart(month,客户生日)>9 and datepart(month,客户生日)<12)
or (datepart(month,客户生日)=12 and datepart(day,客户生日)<=15)
select cust_id, birthday,
(select count(*) - 1
from customer
where birthday = x.birthday) countOfSameBirthdy,
(select count(*) -1
from customer
where birthday > x.birthday) countOfOlder
from customer x
where to_char(birthday,'MM-DD') between '09-01' and '12-15'
(SELECT COUNT(*) From Orders WHERE Orders.OrderDate=x.OrderDate) AS 相同生日 ,
(SELECT COUNT(*) From Orders WHERE Orders.OrderDate>x.Orderdate ) AS 大於生日的人數
From Orders x
WHERE ORderDate between cast(cast(year(OrderDate) as varchar(4))+'-09-01' as datetime)
and cast(cast(year(Orderdate) as varchar(4))+'-12-15' as datetime)