SELECT * From Table1
WHERE
(Column1 = SELECT MAX(Colunm1) FROM Table1) OR (Column1 IS NULL)
如果这样写会返回最大值和一个空值(2行结果)。如果我想返回NULL或者最大值怎么办呢?(只要一行结果)
WHERE
(Column1 = SELECT MAX(Colunm1) FROM Table1) OR (Column1 IS NULL)
如果这样写会返回最大值和一个空值(2行结果)。如果我想返回NULL或者最大值怎么办呢?(只要一行结果)
解决方案 »
- 再问列行互转
- 中文匹配问题
- 怎样在存储过程中实现按序号遍历记录集
- 求解,SQL2000查询问题,比较纳闷!
- 数据复制问题
- 我的数据库里面某个字段要替换里面的部分内容。怎么才是最优化的执行方法。
- Identity编号问题
- "The datebase you are attempting to restore was bacled up under a different sot order ID (52) then the one currently running on
- log文件 log 文件滿full,怎么样清除部分log 文件,让log 文件可用空间增大
- delete语句:删除前面10行
- 一个表内统计查询
- 修改系统的注册用户名由name变为name_xin,如果想要使之前用户操作时留下的记录名字也随之改变,有没有办法啊
From Table1
WHERE
(Column1 = SELECT MAX(Colunm1) FROM Table1) OR (Column1 IS NULL)
--或set rowcount 1SELECT * From Table1
WHERE
(Column1 = SELECT MAX(Colunm1) FROM Table1) OR (Column1 IS NULL)
第2各方案好像不能在View里使用。
From Table1
order by Column1 desc
有错误
(
id int ,
varstr varchar(10)
)insert into tablenull
select '5', 'cc' union all select
'1','dd' union all select
'', 'dd'
select max (case when id =null then null else id end ) from tablenull
(
id varchar(10),
varstr varchar(10)
)
insert into tablenull
select '5', 'cc' union all select
'1','dd' union all select
'null', 'dd'select max (case when id =null then null else id end ) from tablenull
WHERE
(isnull(Column1,'') = SELECT MAX(isnull(Colunm1,'')) FROM Table1)isnull 转换成 '' 可以嘛?这样就可以用=号进行比较了
FROM table1 LEFT OUTER JOIN table2 ON table1.id = table2.id
WHERE table2.id = (SELECT MAX(mydate) AS Expr1 FROM table2 AS table2_1 WHERE id = table1.id)问题:如果在table2里面没有table1的id,那么结果就什么都不能返回!我想要的结果是:返回table1的所有id,和table2里面的mydate最大值,如果table2里没有这个id,则返回空值NULL,但是这行的id 还是要的,只不过mydate列null!
select id,isnull(select max(mydate) from table2 where id=a.ID,null) as mydate
from table1 a
SELECT isnull((SELECT MAX(Colunm1) FROM Table1 where table1.column1=t.Colunm1),null) as value From Table1 as t
WHERE
SELECT isnull((SELECT MAX(Colunm1) FROM Table1 where table1.column1=t.Colunm1),null) as value From Table1 as t
1 2004 A
1 2005 B
1 2007 C
1 2006 D
如果按照上面的写法,那么将返回:
id, mydate, name
1 2007 A
1 2007 B
1 2007 C
1 2007 D我只想要一行结果,即mydate最大值的那行结果:
1 2007 C
SELECT table1.id, mydate=(select max(mydate) from table2 where id=table1.id)
FROM table1 LEFT OUTER JOIN table2 ON table1.id = table2.id
FROM table1 LEFT OUTER JOIN
(select id,MAX(mydate) as mydate from table2 group by id ) table2_1
ON table1.id = table2_1.id 这样试一下
如果要返回最大mydate 对应 的school :
SELECT table1.id, table2_1.mydate,table2_2.school
FROM table1 LEFT OUTER JOIN
(select id,MAX(mydate) as mydate from table2 group by id ) table2_1
ON table1.id = table2_1.id
inner join table2 table2_2 ON table2_1.id = table2_2.id and table2_1.mydate = table2_2.mydate