brokerid zc date
001 1000.00 20100401
001 1000.00 20100402
002 2000.00 20100403
002 2000.00 20100404需要的结果
brokerid zc date
001 1000.00 20100401002 2000.00 20100403
001 1000.00 20100401
001 1000.00 20100402
002 2000.00 20100403
002 2000.00 20100404需要的结果
brokerid zc date
001 1000.00 20100401002 2000.00 20100403
解决方案 »
- 表已删除,sysobjects中还能找到记录
- 简单,求问这样的一个SQL语句怎样写?二表查询................
- 高分请教SQL Server 2005 如何处理附件?
- |zyciis| 急求:用UNION连接两个表的时候提示:不能为DISTINCT 方式选择 text ntext 或 image 数据类型
- Update的简单问题(解决马上给分)
- [sos]在sqlserver2k中写了个function,怎么输出调试信息阿?
- 75天<=当前日期 -(这里是个减号) 字段日期〈=90天
- 请教Sybase ASE 11.9导出SQL SERVEr2000中乱码问题
- 谁有 对image字段内容实现base64编码的sql语句
- 关于c/s的问题
- 还是这句SQL 我又来了
- 统计的sql语句怎么写
go
create table tb
(
brokerid varchar(10),
zc decimal(19,2),
date datetime
)
insert into tb select '001',1000.00,'20100401'
insert into tb select '001',1000.00,'20100402'
insert into tb select '002',2000.00,'20100403'
insert into tb select '002',2000.00,'20100404'
goselect * from tb b
where not exists(select * from tb where brokerid=b.brokerid and date<b.date)
brokerid zc date
---------- --------------------------------------- -----------------------
001 1000.00 2010-04-01 00:00:00.000
002 2000.00 2010-04-03 00:00:00.000(2 行受影响)
select * from tb t where
not exists
(
select 1 from tb where brokerid=t.brokerid and date<t.date
)
--2
select * from tb t where date=
(
select min(date) from tb where brokerid=t.brokerid
)
go
create table tb
(
brokerid varchar(10),
zc decimal(19,2),
date datetime
)
insert into tb select '001',1000.00,'20100401'
insert into tb select '001',1000.00,'20100402'
insert into tb select '002',2000.00,'20100403'
insert into tb select '002',2000.00,'20100404'
goSELECT DISTINCT brokerid ,zc ,max(date)OVER(PARTITION BY brokerid )[mdate]
FROM tb t brokerid zc mdate
---------- --------------------------------------- -----------------------
001 1000.00 2010-04-02 00:00:00.000
002 2000.00 2010-04-04 00:00:00.000(2 row(s) affected)
not exists (select 1 from t where a.brokerid=brokerid and date<a.date)
--inner join
select a.* from t a inner join (select brokerid,zc,min(date) as mindate) b on a.brokerid=b.brokerid and a.date=b.mindate and a.zc=b.zc
where not exists(select * from tb where brokerid=b.brokerid and date<b.date)
select * from tb
where date%2=1
SELECT * FROM dbo.TB
WHERE CAST(date AS int)%2=1
--或者
SELECT * FROM dbo.TB
WHERE CAST(date AS int)%2=0