关于SQL语句

1.select a.rwmc,a.fpr,a.rwgq,b.jhkssj,b.jhjssj from YG_RW_JHRW a left join 

(select rwid,min(jhrwsj) jhkssj,max(jhrwsj) jhjssj from YG_RW_JHRWMX group by rwid) b 

on a.rwid=b.rwid where a.xmid='090837043_115716625_115817953_104354109' order by b.jhkssj,b.jhjssj

 

2.group by 与 distinct

GROUP BY 语句 用于结合聚合函数，根据一个或多个列对结果集进行分组。

语法

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name

http://www.w3school.com.cn/sql/sql_groupby.asp

DISTINCT 语句

http://www.w3school.com.cn/sql/sql_distinct.asp

 

3.select top 20 id,title from hs_xy_bbsmessage 

where (boardid=@boardid and parentid=@parentid)

and (id not in 

(select top 120 id from hs_xy_bbsmessage where boardid=@boardid and parentid=@parentid order by id desc)

) order by id desc

 

4.select b.goodsname 品名,b.ggxinghao 规格型号,b.shengchancj 生产厂家,b.caizhi 材质,b.danwei 计量单位,case a.isdingchi when 1 then '是' when 0 then '否' end 是否定尺,a.lizhong 理重,a.chengzhong 称重,a.jianshu 件数,a.rushu 入数 from yew_rkplandandetail a left join base_goods b on a.goodsid=b.id

 

5.select * into tableB from tableA

   insert into tableB(fld1, fld2) select fld1, fld2 from tableA

以上两句都是将 tableA 的数据插入到 tableB，但两句又有区别的：

第一句（select into from）要求目标表（tableB）不存在，因为在插入时会自动创建。

第二句（insert into select from）要求目标表（trableB）存在。

 

6. create table DYXY_TBDATA_HG as select * from dongying1.DYXY_TBDATA_HG

创建一个表DYXY_TBDATA_HG，其表结构与dongying1.DYXY_TBDATA_HG(dongying1是表空间)一样

 

7.select bb.StatisticsCount,aa.* from spb_Collocation aa  left join (select * from tn_Counts_100304 where CountType='CommentCount') bb on aa.CollocationId=bb.ObjectId order by bb.StatisticsCount desc

 

8.select DateDiff(day,birthday,getdate()) from user    

   sqlserver数据库两时间相减求时间差 差几天day  差几年year等  http://bbs.csdn.net/topics/100147236