select * from(
select * from
(
select * from (select * from Orders) a
full join (select * from PatientCome) b
on a.GUID = b.ComeGUID
) c
where (c.ProjectID=16 or RealProjectID=16)
and
(
(convert(varchar(10),Time,120) >= '2010-01-01' and convert(varchar(10),Time,120) <='2015-02-02')
or (convert(varchar(10),TimeC,120) >= '2010-01-01' and convert(varchar(10),TimeC,120) <='2015-02-02')
)
) d通过这条sql语句,形成了一个“虚拟表d”,经观察数据发现,还应添加一个自动增长的ID列,请问应该怎么改写这条语句?
解决方案 »
- 有没有办法永久改变@@DATEFIRST的值?
- sqlserver2005设为标识字段的问题
- 一个判断插入问题
- 游标可以嵌套吗?@@FETCH_STATUS是一个全局变量,如果两个游标嵌套的话,这个变量指示的是谁的状态呀?会不会乱呀?
- 执行该存储过程时出现如下错误,请大侠指点,谢谢!
- 怎么理解sql server中的确定性函数和不确定性函数,比如dateadd函数
- sql2000的用户和密码忘了怎么恢复
- 关于XML XSD的一个问题,请高手解答
- 问个SQL Server连接上的问题
- 我的表有三个字段..有一个列是有默认值的..我在插入的时候想..那个 列的值等于默认值..怎么写这个存储过程
- SQL 碰到一个问题
- sqlserver中的decimal的用法
select * from(
select * from
(
select * from (select * from Orders) a
full join (select * from PatientCome) b
on a.GUID = b.ComeGUID
) c
where (c.ProjectID=16 or RealProjectID=16)
and
(
(convert(varchar(10),Time,120) >= '2010-01-01' and convert(varchar(10),Time,120) <='2015-02-02')
or (convert(varchar(10),TimeC,120) >= '2010-01-01' and convert(varchar(10),TimeC,120) <='2015-02-02')
)
) d
)
SELECT * FROM CTE A
WHERE EXISTS (
SELECT 1 FROM CTE WHERE Name = A.Name AND ID <> A.ID) order by Name
注意倒数第二行代码,我就是想用那个ID来区分查重中的不同
select *
,ID = ROW_NUMBER() OVER (ORDER BY NAME,NEWID())
from(
select * from
(
select * from (select * from Orders) a
full join (select * from PatientCome) b
on a.GUID = b.ComeGUID
) c
where (c.ProjectID=16 or RealProjectID=16)
and
(
(convert(varchar(10),Time,120) >= '2010-01-01' and convert(varchar(10),Time,120) <='2015-02-02')
or (convert(varchar(10),TimeC,120) >= '2010-01-01' and convert(varchar(10),TimeC,120) <='2015-02-02')
)
) d
)
SELECT * FROM CTE A
WHERE EXISTS (
SELECT 1 FROM CTE WHERE Name = A.Name AND ID <> A.ID) order by Name