Case ..when..End 首先让我们看一下 CASE 的语法。在一般的 SELECT 中,其语法如下: SELECT<myColumnSpec>= CASE WHEN<A>THEN<somethingA> WHEN<B>THEN<somethingB> ELSE<somethingE> END 在上面的代码中需要用具体的参数代替尖括号中的内容。下面是一个简单的例子: USE pubs GO SELECT Title, 'Price Range'= CASE WHEN price ISNULLTHEN'Unpriced' WHEN price <10THEN'Bargain' WHEN price BETWEEN10and20THEN'Average' ELSE'Gift to impress relatives' END FROM titles ORDERBY price GO 这是 CASE 的典型用法,但是使用 CASE 其实可以做更多的事情。比方说下面的 GROUPBY 子句中的 CASE: SELECT'Number of Titles', Count(*) FROM titles GROUPBY CASE WHEN price ISNULLTHEN'Unpriced' WHEN price <10THEN'Bargain' WHEN price BETWEEN10and20THEN'Average' ELSE'Gift to impress relatives' END GO 你甚至还可以组合这些选项,添加一个 ORDERBY 子句,如下所示: USE pubs GO SELECT CASE WHEN price ISNULLTHEN'Unpriced' WHEN price <10THEN'Bargain' WHEN price BETWEEN10and20THEN'Average' ELSE'Gift to impress relatives' ENDAS Range, Title FROM titles GROUPBY CASE WHEN price ISNULLTHEN'Unpriced' WHEN price <10THEN'Bargain' WHEN price BETWEEN10and20THEN'Average' ELSE'Gift to impress relatives' END, Title ORDERBY CASE WHEN price ISNULLTHEN'Unpriced' WHEN price <10THEN'Bargain' WHEN price BETWEEN10and20THEN'Average' ELSE'Gift to impress relatives' END, Title GO 注意,为了在 GROUPBY 块中使用 CASE,查询语句需要在 GROUPBY 块中重复 SELECT 块中的 CASE 块。
把某一字段相同的数据去掉 select id=identity(int,1,1) ,attid=cast(attachmentid asint),contentid into #t3 from attachment where contentid =402 select contentid,attid from #t3 where id in (selectmin(id) from #t3 groupby contentid) 其中,attachmentid为自增字段,把contentid字段相同的数据,只取出一条
写SQL字符串 通过写SQL语句来执行存储过程 declare@Sqlnvarchar(1000) set@sql=@sql+' and C.Author like ''%'+cast(@Authorasvarchar)+'%''' set@sql=@sql+' and (datediff(s,cast('''+cast(@startTimeasvarchar)+''' as datetime) ,CommitDate)>0) ' exec(@sql) like 匹配查询时,第一个%前面要加俩个'',第二个%后面要加俩个'' 字符串连接时间类型时,首先把datetime类型参数转换成字符串类型,注意,也要加俩个''.