SELECT * FROM Joined WHERE Year(JoinTime)=Year(GetDate()) and Month(JoinTime)=Month(GetDate()) and Day(JoinTime)=Day(GetDate()) and Price=(SELECT top 1 Price FROM Joined group by price,Year(JoinTime),Month(JoinTime),Day(JoinTime) HAVING Count(Price)=1 and Year(JoinTime)=Year(GetDate()) and Month(JoinTime)=Month(GetDate()) and Day(JoinTime)=Day(GetDate()) order by price asc)结果是出来了. 不过我想应该还有更好的办法. 期待高手指点.
select mobile,min(price) as price,jointime from(select * from tablename group bymobile,price,jointime having count(*)=1)a group by mobile,jointime where convert(char(10),jointime,120)='2007-09-14'
/*测试*/ create table Ndata( ID int, Mobile varchar(100), Price money, JoinTime Datetime )insert into Ndata select 1,'0001',321,'2007-1-1' union all select 2,'0002',3213,'2007-1-3' union all select 3,'0031',3213,'2007-1-3' union all select 4,'0031',3213,'2007-9-14' /*语句*/ select JoinTime, '价格'=min(Price) from Ndata where datediff(dd,JoinTime,getdate())=0 group by JoinTime having count(JoinTime)=1/*结果*/ 2007-09-14 00:00:00.000 3213.0000
declare @a table(Mobile varchar,Price money,JoinTime Datetime) insert @a select 'a',100,'2007-08-01' union select 'a',10,'2007-08-01' union select 'b',100,'2007-08-01' union select 'b',10,'2007-08-01' union select 'a',10,'2007-08-02' union select 'b',10,'2007-08-02' select mobile,min(price) from @a where jointime='2007-08-01' group by mobile/*(所影响的行数为 6 行)mobile ------ --------------------- a 10.0000 b 10.0000(所影响的行数为 2 行) */
WHERE Year(JoinTime)=Year(GetDate())
and Month(JoinTime)=Month(GetDate())
and Day(JoinTime)=Day(GetDate())
and Price=(SELECT top 1 Price FROM Joined
group by price,Year(JoinTime),Month(JoinTime),Day(JoinTime)
HAVING Count(Price)=1
and Year(JoinTime)=Year(GetDate())
and Month(JoinTime)=Month(GetDate())
and Day(JoinTime)=Day(GetDate())
order by price asc)结果是出来了. 不过我想应该还有更好的办法. 期待高手指点.
create table Ndata(
ID int,
Mobile varchar(100),
Price money,
JoinTime Datetime
)insert into Ndata select 1,'0001',321,'2007-1-1'
union all select 2,'0002',3213,'2007-1-3'
union all select 3,'0031',3213,'2007-1-3'
union all select 4,'0031',3213,'2007-9-14'
/*语句*/
select JoinTime,
'价格'=min(Price)
from Ndata
where datediff(dd,JoinTime,getdate())=0
group by JoinTime
having count(JoinTime)=1/*结果*/
2007-09-14 00:00:00.000 3213.0000
insert @a
select 'a',100,'2007-08-01'
union
select 'a',10,'2007-08-01'
union
select 'b',100,'2007-08-01'
union
select 'b',10,'2007-08-01'
union
select 'a',10,'2007-08-02'
union
select 'b',10,'2007-08-02'
select mobile,min(price) from @a where jointime='2007-08-01' group by mobile/*(所影响的行数为 6 行)mobile
------ ---------------------
a 10.0000
b 10.0000(所影响的行数为 2 行)
*/